Subject | Re: [firebird-support] Re: a question of design |
---|---|
Author | Doug Chamberlin |
Post date | 2007-01-07T13:50:43Z |
Consider these points:
1) What about future changes? If you need to add a new music type you will
need to add a new table for extra info for that type rather than just
adding a new legal value to the MusicType field.
2) Using the extra tables, you need a different actual query to retrieve
from each music type. Using the MusicType field you can retrieve each type
using the same query and just changing the MusicType value (unless you
actually need the classical extra info).
3) Using the extra tables you are permanently committing to extra
processing each time you perform a query. Using the MusicType field you are
making all future queries more efficient by avoiding the additional
complexity unless it is actually needed in the case of the classical items.
4) I would actually make the MusicType field a string with characters
included that matched the type of the entry. 'D' for DVD, 'M' for music,
'C' for classical. Define it as varchar(10) to leave room for expansion.
Right now each record would have only 'D', 'M', or 'C' in each. But you
could allow for 'CM' for classical music if one item qualifies for both
categories. Sometime in the future you might have such an item. Your design
should allow for such developments.
5) A hybrid design would create a classification table that lists only the
primary key for the item and any appropriate MusicType value. Doesn't
'contaminate' the primary table with a MusicType value but still uses the
MusicType field to indicate the classification.
1) What about future changes? If you need to add a new music type you will
need to add a new table for extra info for that type rather than just
adding a new legal value to the MusicType field.
2) Using the extra tables, you need a different actual query to retrieve
from each music type. Using the MusicType field you can retrieve each type
using the same query and just changing the MusicType value (unless you
actually need the classical extra info).
3) Using the extra tables you are permanently committing to extra
processing each time you perform a query. Using the MusicType field you are
making all future queries more efficient by avoiding the additional
complexity unless it is actually needed in the case of the classical items.
4) I would actually make the MusicType field a string with characters
included that matched the type of the entry. 'D' for DVD, 'M' for music,
'C' for classical. Define it as varchar(10) to leave room for expansion.
Right now each record would have only 'D', 'M', or 'C' in each. But you
could allow for 'CM' for classical music if one item qualifies for both
categories. Sometime in the future you might have such an item. Your design
should allow for such developments.
5) A hybrid design would create a classification table that lists only the
primary key for the item and any appropriate MusicType value. Doesn't
'contaminate' the primary table with a MusicType value but still uses the
MusicType field to indicate the classification.