A
Adam
I am struggling with an issue of table design, and would appreciate any
advice.
I need to create a table to record the dates that various events happen to a
number of objects. Each object will have the same events that happen to it
at different times. For each event that happens, I need to record the date.
So, I have a choice: either I can have one record for each object, and store
the various dates in different fields, with the first field for the date of
the first event that happens, the second fiield for the date of the second
event, etc (wide form). The events that happen are predictable and will
always happen for all objects, so there is no problem knowing how many
fields to have. The alternative is to have just one field for date and
another field to record which event is happening, so each object will have
several records (long form).
My understanding of good database design (which I will admit is far from
perfect) is that similar things should generally go in the same field, so
the long form would be better design. This would also have the merit that no
space is taken up for dates of things that have not yet happened. On the
other hand, I suspect the wide form will make it easier to do the sort of
queries I will need, such as determining whether a particular event has
happened yet for a given object.
I suspect that none of this really matters greatly, as this table is
unlikely ever to have to store data for more than a few hundred objects
anyway, but I would like to know that I am doing things the right way.
Many thanks
advice.
I need to create a table to record the dates that various events happen to a
number of objects. Each object will have the same events that happen to it
at different times. For each event that happens, I need to record the date.
So, I have a choice: either I can have one record for each object, and store
the various dates in different fields, with the first field for the date of
the first event that happens, the second fiield for the date of the second
event, etc (wide form). The events that happen are predictable and will
always happen for all objects, so there is no problem knowing how many
fields to have. The alternative is to have just one field for date and
another field to record which event is happening, so each object will have
several records (long form).
My understanding of good database design (which I will admit is far from
perfect) is that similar things should generally go in the same field, so
the long form would be better design. This would also have the merit that no
space is taken up for dates of things that have not yet happened. On the
other hand, I suspect the wide form will make it easier to do the sort of
queries I will need, such as determining whether a particular event has
happened yet for a given object.
I suspect that none of this really matters greatly, as this table is
unlikely ever to have to store data for more than a few hundred objects
anyway, but I would like to know that I am doing things the right way.
Many thanks