Database design

[Jim McGivney]

I want to use a Microsoft Access 2003 database to hold dental and
anthropological information about individuals.

It seems to me that I only need one table with many columns.

The fields would include: ID#, LastName, FirstName, Height, Weight,
Tooth#1, Tooth#2, . Tooth#32, BloodType, DOB, etc. There could be well over
100 fields (columns).

The relationship seems to be a one-to-one type.

Am I missing something or is use of one table the best design choice.

Thanks in advance for your help.

Jim

 

[John Vinson]

I want to use a Microsoft Access 2003 database to hold dental and
anthropological information about individuals.

It seems to me that I only need one table with many columns.

The fields would include: ID#, LastName, FirstName, Height, Weight,
Tooth#1, Tooth#2, . Tooth#32, BloodType, DOB, etc. There could be well over
100 fields (columns).

The relationship seems to be a one-to-one type.

Am I missing something or is use of one table the best design choice.

Thanks in advance for your help.

Jim

It depends somewhat on what is in etc.

I'd be pretty strongly inclined to pull at least some of the fields
(the teeth in particular) out into a related table. What information
are you storing about tooth#5 (for me nothing, Yes, We Have No
Bicuspids)? An alternative would be a table Teeth with fields ID#,
ToothNo (as a joint two-field primary key), and any fields of
information about the tooth. Do you have any other fields with 1, 2, 3
in the fieldname? If so - consider them for a related table.

If you only have one table then there ARE no relationships -
relationships exist between pairs of tables.

John W. Vinson[MVP]

 

[Allen Browne]

One person can have many teeth. There is a one-to-many relation between
Client and Tooth.

Over time, a client will have work done on their teeth multiple times. This
implies multiple visits over for one client: a one-to-many relation between
clients and visits.

One visit can cover work on multiple teeth. This means you have multiple
line items to describe the visit, i.e. a one-to-many relation between the
visit and the visit details (like the Order and OrderDetails table in
Northwind.)

I imagine you have a special name (or number) for each tooth, so the
structure is something like this:

Tooth table (32 records):
ToothID Unique number or name for each tooth. Primary key

Dentist table:
DentistID AutoNumber primary key
LastName Text
FirstName Text
...

Client table:
ClientID AutoNumber primary key
LastName Text
FirstName Text
BloodTypeID Text relates to a table of valid blood types.
DOB Date/Time
...

Visit table:
VisitID AutoNumber primary key
ClientID Number relates to Client.ClientID
VisitDate Date/Time when this visit was
DentistID Number relates to Dentist.DentistID

VisitDetail table:
VisitDetailID AutoNumber primary key
VisitID Number which visit this relates to.
ToothID which tooth was worked on.
WorkTypeID Number what kind of work was done.
Fee Currency charge for this work.

WorkType table:
WorkTypeID primary key

BloodType table:
BloodTypeID Text primary key

The WorkType table will contain one entry for each kind of work you do, e.g
"root canal", "2-surface amalgam", "check up". For a check up, the ToothID
field in the VisitDetail table might be left blank (i.e. it was not work on
a specific tooth.)

There will probably be other lookup tables such like BloodType as well.

After creating these table, you will have lots of one-to-many relations to
create (Tools | Relationships.) You will probably find that your tables have
between 1 and 40 fields.