Basic question about Table design (2 tables versus many)

[NoviceNana]

Hi All,

I have moderate knowledge of Microsoft Access. I have been building
small databases for years and just sort of learning and and picking up
things as I go. At this point I'm pretty proficient at writing basic
code, running complex queries and creating really pretty forms.

Because I'm pretty much self taught I lack some of the fundamental
knowledge of database design, especially table design. I'm curently
building a database to house information on clients in a health
program. The database has 2 tables

Table 1:- All client level information (id number, date of birth etc.)
This table houses all information that will essentially only be
entered once.

Table 2: All encounter level information. Each client will have
meetings weekly, chart reviews as needed and review meetings that
occur quarterly. These 3 "encounters" all have different forms
(feeding into the encounter table) and each set of data elements have
an associated date.

After working on this database for 2 months my boss is now questioning
my table structure. He seems to think that all data elements that
have the same periodicity should be entered on the same table... this
would result in each form having at least 3 tables associated with
it. Originally the database had a table for each separate form and
all the tables had a one to many relationship with the main table.

Which way is the best way to build this database? I'm sure there is
information on line so if you just want to point me in the right
direction I would be MUCH obliged!!
Thanks!

 

[Carl Rapson]

Hi All,

I have moderate knowledge of Microsoft Access. I have been building
small databases for years and just sort of learning and and picking up
things as I go. At this point I'm pretty proficient at writing basic
code, running complex queries and creating really pretty forms.

Because I'm pretty much self taught I lack some of the fundamental
knowledge of database design, especially table design. I'm curently
building a database to house information on clients in a health
program. The database has 2 tables

Table 1:- All client level information (id number, date of birth etc.)
This table houses all information that will essentially only be
entered once.

Table 2: All encounter level information. Each client will have
meetings weekly, chart reviews as needed and review meetings that
occur quarterly. These 3 "encounters" all have different forms
(feeding into the encounter table) and each set of data elements have
an associated date.

After working on this database for 2 months my boss is now questioning
my table structure. He seems to think that all data elements that
have the same periodicity should be entered on the same table... this
would result in each form having at least 3 tables associated with
it. Originally the database had a table for each separate form and
all the tables had a one to many relationship with the main table.

Which way is the best way to build this database? I'm sure there is
information on line so if you just want to point me in the right
direction I would be MUCH obliged!!
Thanks!

Assuming the encounters are all using basically the same fields, there's no
need to separate the information into multiple tables, since you can store a
date with each record that indicates the periodicity. I assume there's also
a field in the table indicating the type of encounter. Your queries can be
structured so that you can't really tell that the encounters are all stored
together.

Now, if each encounter has different types of information and they don't all
use the same fields in the table, then you may want to consider separate
tables. If, for example, encounter A uses only fields 1 and 2 in the table,
while encounter B uses only fields 3 and 4 in the table, then it may make
sense to separate them. Otherwise, you will be wasting roughly 2/3 of your
record space for each encounter entry. So it really depends on what data
items make up the 3 encounters.

Carl Rapson

 

[Chris2]

Hi All,

I have moderate knowledge of Microsoft Access. I have been building
small databases for years and just sort of learning and and picking up
things as I go. At this point I'm pretty proficient at writing basic
code, running complex queries and creating really pretty forms.

Because I'm pretty much self taught I lack some of the fundamental
knowledge of database design, especially table design. I'm curently
building a database to house information on clients in a health
program. The database has 2 tables

Table 1:- All client level information (id number, date of birth etc.)
This table houses all information that will essentially only be
entered once.

Table 2: All encounter level information. Each client will have
meetings weekly, chart reviews as needed and review meetings that
occur quarterly. These 3 "encounters" all have different forms
(feeding into the encounter table) and each set of data elements have
an associated date.

After working on this database for 2 months my boss is now questioning
my table structure. He seems to think that all data elements that
have the same periodicity should be entered on the same table... this
would result in each form having at least 3 tables associated with
it. Originally the database had a table for each separate form and
all the tables had a one to many relationship with the main table.

Which way is the best way to build this database? I'm sure there is
information on line so if you just want to point me in the right
direction I would be MUCH obliged!!
Thanks!

NoviceNana,

The answer to your question largely depends on the nature of your
data.

In database design, every table represents an "entity" in the real
world.

Your Table1 represents your clients (patients).

Your Table2 represents encounters (client/health-provider meetings).

Now, you need to ask yourself this:

Are all encounters pretty much like all other encounters, differing
only in what their periodicity is (and perhaps what name of the type
of encounter is, "heart test", "stress test", "x-ray")?

If they are all alike, its one table (one entity).

If they are all disimilar, they are multiple tables.

How do you decide?

Here's the good part.

It's a judgment call based on following the rules of database design.

Once you know these rules and understand them, you will find that this
type of question just tends to "fall into place".

The rules of database design go by the high-flown name of
"normalization".

Good Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm

Tips to Get You Going
http://home.att.net/~california.db/tips.html#aTip1


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I quite like this whole site, since it has a handy menu on the right
describing many important aspects of the database world:
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html


Sincerely,

Chris O.

 

[Amy Blankenship]

Assuming the encounters are all using basically the same fields, there's
no need to separate the information into multiple tables, since you can
store a date with each record that indicates the periodicity. I assume
there's also a field in the table indicating the type of encounter. Your
queries can be structured so that you can't really tell that the
encounters are all stored together.

Now, if each encounter has different types of information and they don't
all use the same fields in the table, then you may want to consider
separate tables. If, for example, encounter A uses only fields 1 and 2 in
the table, while encounter B uses only fields 3 and 4 in the table, then
it may make sense to separate them. Otherwise, you will be wasting roughly
2/3 of your record space for each encounter entry. So it really depends on
what data items make up the 3 encounters.

You might also want to create an encounter type table that includes
information about the different types of encounters, including periodicity.

HTH;

Amy