Table Design
-
Hi,
I'm a newbie in database design, hope you guys can help me with the following scenario.
Let's say, I have 5 Item Types with some common fields and unique fields among them. Example:
Item Type 1 : A, B, C, D, E, F, G
Item Type 2 : A, B, C, H, I, J, K
Item Type 3 : A, B, C, E, J, K, L, M
Item Type 4 : A, B, C, F, H, K, N, O
Item Type 5 : A, B, C, G, H, E, O, P, Q
Now, my current table design is, I have 1 master table called Item with columns A, B, C and then have 5 different Item SubType tables to store the rest of the unique columns of the respective Item Type
However, the above design would requires me to join table, hence it would affect the query performance.
So, I were to create just 1 table to store all Item Types with columns A, B, C, D, E, F, G, H, I, J, K , L, M, N, O, P, Q would that be a good idea?
Yes, some of the columns will be NULL for different type of items, but in term of query performance, it would be easier and faster to retrieve the data. Would it?
Note: In the actual scenario, there more ItemType (not just 5) and many more columns.
Thank you in advance!
-
As you mentioned that in real scenario you can have more item type with many other attributes.So it is better to have separate tables for every item type instead of single table i.e. your first design would be better.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂 -
First, few questions:
1. Will you ever have to list more than one type in the same result set?
2. Will you be adding new types?
3. Is there a finite set of attributes or will new attributes be added when and if a new type is added?
4. Are the common attributes justification enough for a separate table?
😎
-
Eirikur Eiriksson (5/8/2014)
First, few questions:1. Will you ever have to list more than one type in the same result set?
2. Will you be adding new types?
3. Is there a finite set of attributes or will new attributes be added when and if a new type is added?
4. Are the common attributes justification enough for a separate table?
😎
1. Yes (most of the time the result set will only display common attributes, but in certain cases the result set also display non-common attributes)
2. Potentially, yes
3. New attribute will be added if the new type has diff attribute than the existing ones
4. Currently 60-70% are common attributes...
Thank you.
-
azmiman (5/8/2014)
Eirikur Eiriksson (5/8/2014)
First, few questions:1. Will you ever have to list more than one type in the same result set?
2. Will you be adding new types?
3. Is there a finite set of attributes or will new attributes be added when and if a new type is added?
4. Are the common attributes justification enough for a separate table?
😎
1. Yes (most of the time the result set will only display common attributes, but in certain cases the result set also display non-common attributes)
This could potentially lead to a fairly long winded left outer join code with the super/sub-type design.
2. Potentially, yes
3. New attribute will be added if the new type has diff attribute than the existing ones
4. Currently 60-70% are common attributes...
Based on this, I would suggest a single table design, possibly storing some/all of the non-common attributes in a structured form, i.e. XML.
If adding a new type means either adding a new table or adding a column to an existing table, the latter is far simpler. Even simpler would be the addition of an attribute within an XML.
😎
-
If you go for a single table, the coding of queries will be easier; but it may be slower rather than faster, depending on what the queries do and how much data there is. Obviously if a query has to return some items of each of the 5 different types it's going to be a 6-way join in the 6-table version, which is a lot more complex than the single table query in the one table version, and that complexity may make it slower. But if most of your queries don't access more than one type then most of teh joins are only two-way, and if most of them return only common attributes then most don't need a join, so perhaps only a small number of queries may be slower and a lot of queries may be faster. But if splitting out the common table into a separate table doesn't save enough space to to reduce the amount of IO, and the queries don't have to do scans but are can use index seeks, there isn't any performance gain from the split. Of course you can go to a two table model, one table for the common attributes and another for all the non-common attributes, which doesn't get the scan-saving of the six table model (if there is any such saving) but does get the space saving (if there is any such saving), and the the difference in complexity between that and the single table model is just a single inner join. As well, you can maybe (in either the two table or the six table model) mark the non-common attributes SPARSE, which might save enough space to be useful and doesn't do the kind of denormalisation that using an encoding for those columns into some serial form (lke XML) does (and thus avoids a lot of complexity and performance cost of using such an encoding). Personally I think I would go for a two-table solution, based on your answer's to Eirikur's four questions, because it looks as if most queries only look at the common attributes, but I have to admit that those four questions and their answers don't really tell me enough to make an informed choice.
Tom
-
You give some good examples of table design here, i am impressed with your programming and designing skills. In future any kind of issue related designing tables in website i definitely contact you through PM
-
One of the problems with bundling different entity types into one table is that you may have to make some compromises with integrity constraints - or incur a certain amount of additional complexity to enforce those constraints.
For example, in your original model the Type 1 items have attributes D,E,F,G. If you combine all types into one table and make D,E,F,G nullable columns then you could have Type 1 attributes with missing values for one or more of those attributes. That may not be what you intended. You could solve this by adding additional CHECK constraints to ensure only permitted combinations of attribute values get into your table. That implies some overhead when you insert and update rows.
A possibly harder problem is what to do if FOREIGN KEY constraints needed to reference those individual type tables, or if UNIQUE or FOREIGN KEY constraints applied to an attribute for one item type but not to the same attribute for another item type. If you need constraints of that kind then I suggest you keep the different types separated into individual tables.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply