If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Store similar types of records all in one table or separate tables?
Suppose you want to have four different types of records.
Each of these records have numerous fields in common, and a few fields that are unique to each type of record. Most of the fields are related to other tables, but a few are simply text fields or Booleans. Which is better?: Keeping track of all 3 types of records in a single table. Or Creating separate tables for each type of record. Is one solution clearly better or is it just a matter of opinion? Thanks in advance, Tom |
#2
|
|||
|
|||
Store similar types of records all in one table or separate tables?
On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit wrote:
Suppose you want to have four different types of records. Each of these records have numerous fields in common, and a few fields that are unique to each type of record. Most of the fields are related to other tables, but a few are simply text fields or Booleans. Which is better?: Keeping track of all 3 types of records in a single table. Or Creating separate tables for each type of record. Is one solution clearly better or is it just a matter of opinion? Thanks in advance, Tom You can get into some fine old quasi-religious arguments over this issue. The "purists" would say that this is a case of Subclassing, one of the few instances where one-to-one relationships are appropriate. Let's say you have a table of ComputerComponents; Monitors have width, height, resolution, etc., disk drives have diameter, capacity, speed, cabletype, etc. - but these attributes only apply to the particular category. Using subclassing you would have one master table of ComputerParts, with the common fields; it would be related one-to-one to a table of Monitors (with the monitor-specific fields), and to a table of Diskdrives (with those fields), etc. You would need some programmatic (not referential integrity) constraint to ensure that you don't put a record in the Monitors table when the part in question is a disk drive! Alternatively you can use a wider table and leave the irrelevant fields NULL. Both methods do work, and do have their place; which is better depends both on the application (how MANY subclasses? how many fields each? how will the data be used?) and on the biases and preferences of the developer. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Store similar types of records all in one table or separatetables?
On Nov 6, 10:55*am, John W. Vinson
wrote: On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit wrote: Suppose you want to have four different types of records. Each of these records have numerous fields in common, and a few fields that are unique to each type of record. Most of the fields are related to other tables, but a few are simply text fields or Booleans. Which is better?: * * Keeping track of all 3 types of records in a single table. Or * * Creating separate tables for each type of record. Is one solution clearly better or is it just a matter of opinion? Thanks in advance, Tom You can get into some fine old quasi-religious arguments over this issue. The "purists" would say that this is a case of Subclassing, one of the few instances where one-to-one relationships are appropriate. Let's say you have a table of ComputerComponents; Monitors have width, height, resolution, etc.., disk drives have diameter, capacity, speed, cabletype, etc. - but these attributes only apply to the particular category. Using subclassing you would have one master table of ComputerParts, with the common fields; it would be related one-to-one to a table of Monitors (with the monitor-specific fields), and to a table of Diskdrives (with those fields), etc. You would need some programmatic (not referential integrity) constraint to ensure that you don't put a record in the Monitors table when the part in question is a disk drive! Alternatively you can use a wider table and leave the irrelevant fields NULL. Both methods do work, and do have their place; which is better depends both on the application (how MANY subclasses? how many fields each? how will the data be used?) and on the biases and preferences of the developer. -- * * * * * * *John W. Vinson [MVP] Thank you, John. You've given me food for thought. Best, Tom |
Thread Tools | |
Display Modes | |
|
|