table organization?

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

I am designing a database which tracks Clients,
ClientEvents, and ClientEventDetails. Each related to the
subsequent in 1:many relationships respectively. My
delemma is that certain ClientEvents have
ClientEventDetails that are completely distinct. Is it
more efficient to have one large ClientEventDetails table
in which only 1/2 of the fields will be used for any
given event or is it preferrable to make a
ClientEventDetails(1) table and a ClientEventDetails(2)
table and create 2 separate 1:many joins with the
ClientEventTable? Does the answer depend on how many
fields would be left unfilled by having a single large
ClientEventDetails table. If so, at what point does it
become more efficient to break the tables into 2 separate
ones. Thanks.
 
I am designing a database which tracks Clients,
ClientEvents, and ClientEventDetails. Each related to the
subsequent in 1:many relationships respectively. My
delemma is that certain ClientEvents have
ClientEventDetails that are completely distinct. Is it
more efficient to have one large ClientEventDetails table
in which only 1/2 of the fields will be used for any
given event or is it preferrable to make a
ClientEventDetails(1) table and a ClientEventDetails(2)
table and create 2 separate 1:many joins with the
ClientEventTable? Does the answer depend on how many
fields would be left unfilled by having a single large
ClientEventDetails table. If so, at what point does it
become more efficient to break the tables into 2 separate
ones. Thanks.
Click to expand...

If it's just two categories, and especially if they sometimes overlap,
I'd just leave it as one table. Access doesn't store blanks in "empty"
fields, so leaving fields blank will not waste space nor will it
adversely affect indexing on the database.

This is a design decision that is as much art as science; some
developers will use one table, some will use two, and there might be
some cases where it would make sense to use *three* - a
ClientEventDetails table with the fields common to all details events,
with two additional tables related one-to-one to ClientEventDetails
with the specific events.
 
Back
Top