Table design methodology...

[clamfuddle]

Hi, everyone.

I'm creating a utility database for my Reserve (Volunteers) Fire Department,
keeping
personal information, gear issued, training attended (attendance) and such.

I thought I had this all lined out, but now I'm questioning a little about
table or DB design.

I'm keeping personal information (FN, LN, Addr, City, State, Zip, Phone,
work phone, etc)
as well as drivers license information (DL#, endorsements, state, expiry,
etc)
as well as emergency contact information (next of kin, work phone, home
phone, etc)
as well as size information (Tee-shirt size, belt size, dress coat size,
etc.)
(You get the point, I hope)

Now the design question - Should I put all these items in one table (lots of
fields so I feel it will be unwieldy)
or create "themed" tables that will be a one-to-one relationship with the
main table.

What I mean by "themed" tables is, create a personal info table with that
subset of fields, and a drivers license table with
those fields, a sizes table with those fields, an emergency contact table
with those fields, etc. and connect
them with a common key. That gives me a lot of linked tables with a
reasonable number of fields in each.
I'm thinking that entry form design will be easier and such as well.

I don't know which way is more acceptable, or even if it matters or not.
Does the "themed" table approach adhere
to commonly accepted practices?

Thanks for helping me clear away the cobwebs,

Bryan

 

[PC Datasheet]

Actually, the best way is everything in one table. Everything you list has a
one-to-one relationship with the person so one table is appropriate. What you
can do is create a form with a tab control. Put the person's name on the form
above the tab control and make each tab one of your themes. Then on each page
of the tab control, only put the fields related to that theme. This will give
you the effect of a file cabinet with multiple file folders for each person.
Each file folder being one of the "themes". Try it - you'll like what you
create!!

 

[clamfuddle]

Thank you for the idea - I've been torn between both ideas for
quite a while and am trying to break my own mental "deadlock" over
these two methodologies. I'll give it a go!

Thanks again,

Bryan

 

[tina]

well, some things probably should go in separate tables. for instance, phone
numbers. you have one-to-many relationship between personnel and phone
numbers, as
one person can have many phone numbers (work, home, cell, pager).

you have a one to-many-relationship between personnel and clothing, as
one person can have many clothing sizes (for tee-shirts, pants, dress coats,
etc).

you may have a one-to-many relationship between personnel and emergency
contact (might be appropriate to list more than one contact at times - i'd
certainly want that capability), and also a one-to-many relationship between
contacts and phone numbers (again, i'd want the capability to list more than
one phone number for an emergency contact).

you probably have a one-to-many relationship between personnel and gear
issued. name of a specific piece of gear, perhaps date issued, perhaps final
disposition (lost, damaged, etc), and perhaps date of final disposition.

you probably have a one-to-many relationship between personnel and training.
course name, date attended, perhaps grade, etc.

actually, out of everything you mentioned, it seems like the only one-to-one
relationship is between personnel and address info and perhaps dl info.

hth

 

[Chris Nebinger]

I respectfully disagree.

Tina has a, in my opinion, better approach.

I know that for now it seems like sizes on clothing won't
change, but it's possible that it will. There very well
could be a new piece of clothing required that needs it's
own size measurement. Adding fields to accomodate this
would be innefficient.

Driver's Licenses are a 1-1 relationship, but what about
CDL's? Motorcycle's? Are there different endorsements to
the standard license that you will need to track? How are
you handling them all?

Training Classes? That is a M-M relationship as well.

Post back if you have any more questions/comments.


Chris Nebinger