One to One Relationships...

[MeisoT]

I have a table that contains approximately 80 fields - alot of which are Yes/No and Date fields. When each record is updated by the user, there will be groups of fields that will remain blank.

My question: Should I combine the fields into logical groups and move them to separate tables, creating one-to-one relationships where I would have 1 main table and 7 related tables (one-to-one) or is it ok to keep one large table that would contain alot of blank fields?

I would also like to know what effect the one large table scenario would have on running queries and reports. Will performance degrade with this type of setup? Will queries and reports run slower?

I would prefer to keep everything in one table, as it would be easier to maintain and easier for the users to navigate on the data entry form. Does anyone know what the drawbacks are to doing it this way?

Any ideas or suggestions would be much appreciated.

 

[dhookom]

Whenever I hear about multiple yes/no and/or date fields, I worry that the table structure is not normalized. This is especially true when you have 80 fields.

I try not to suggest workaround to a potentially poor table structure when it might need some normalization.

I could be very wrong.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BNPMike]

With Access it is a good approach to build the database in a canonical way ie map the tables to your logical view. Only worry about efficiency actions (de-normalising, collapsing, partitioning etc) when you hit an actual problem. Don't anticipate problems.

One of the guiding principles of the relational model (and other database models at the time) was you should access the data via an external schema This layer then allows you to change the underlying table structure without changing your programs. So start of with one big table but always access it via a query (the external schema mechanism for Access). If you then decide you need to split the table you merely need to change that one query and all your forms and reports will run as before.

 

[MeisoT]

Thanks for your replies. Two totally opposite opinions, yet both seem to make alot of sense. I now have something to chew on while I come up with an adequate design for my table(s).

 

[RickSpr]

Here's some more to chew on. They're not really opposite opinions, they're just based on different assumptions.

Duane is assuming, or at least questioning, that you haven't normalized enough yet. BNPMike is assuming that you've already normalized to at least 3NF (Third Normal Form). That's why BNPMike's advice mentioned not worrying about whether to denormalize.

You really should feel sure that you've normalized enough before you follow Mike's advice.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PHV]

You may take a look here:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244