Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2008-08-07 : 19:48:49
|
| Hi,Lets say I have a table of widgets and each widget has a type. So a widget row has a foreign key value which references a table of widget types which contains a description of each type.Now, what if one type of widget needs a little bit of extra data for each widget instance - an extra column or two? I can see a few solutions and am interested to know how experts would deal with this.1. Have a seperate table for the extra data required for each instance of the special type and have each row in this table have a 1-1 reference to a row in the widgets table. Couple of issues here. a) Is an extra table for *one* extra column excessive? b) The widgets type as specified with the foreign key column might not be in synch with the existance or not of a row in the extra table. So the type might suggest that the widget should have this extra row but it might not be there.2. Have a completely seperate table for these special widget. The trouble here is that all the columns from the original widgets table have to be re-created and then kept in synch with any changes. At least this design is normalized though.3. Stick with one table of widgets and just have the extra column and just leave its value to null when its not appropriate to the widget type. I think this is the most practical.Any ideas?Cheers, XF. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-08 : 14:58:26
|
| i think you need to add the data to type table if its actually an extra information for the type. this information can then be retrieved using the existing foreign key relationship. |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-08-09 : 07:20:07
|
| No its extra information for each instance of a specific type. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 07:33:11
|
| Will this extra information be there for majority of instances? |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-08-09 : 20:30:58
|
| It'll be there for all instances of two types but will not be there at all for two other types. I'm not quite sure what the counts will be for the instances of the different types. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-10 : 05:48:44
|
quote:
Originally posted by X-Factor
It'll be there for all instances of two types but will not be there at all for two other types. I'm not quite sure what the counts will be for the instances of the different types.
if its really sparse information which exists only for few type instances and the number of fields involved are really large then you can even have attribute type table which holds the information as rows. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-08-12 : 06:35:18
|
| You mean EAV? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-13 : 13:27:35
|
quote:
Originally posted by X-Factor
You mean EAV?
Yup.Exactly |
|
|
|
|
|
|
Table where eah row references a type