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-12-19 : 19:06:37
|
| Lets say I have a table of widgets and each widget can have one or more attributes assigned to it. Here's how I would do it...TABLE WidgetWidgetID int pkTABLE AttributesAttributeID int pkTABLE Widget_AttributeWidgetID int fkAttributeID fkSo 'Widget_Attribute' is a simple join table. Now, here's how I've seen it done...TABLE WidgetWidgetID int pkAttribute1 bitAttribute2 bitAttribute3 bitPlease cold anyone explain in terms of relational theory why this latter design is wrong?I'd have said that the attributes form a repeating list however the fact remains that every widget has a value for every attribute even if its just '0'! Would these '0's really just be disguised nulls?Cheers, XFactor |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-12-19 : 20:34:00
|
| Well, the short answer is "it breaks normalization" or "it's not normalized", and for the very reason you mention: it's a repeating list. I confess I can never remember the normal form definitions but they're easy to Google.A more practical perspective is: how do you find which attributes a widget has? How do you add new attributes? How do you guarantee that the correct attribute applies to the widget, and conversely, prevent the wrong attribute from being applied? Even your original design doesn't necessarily enforce that, but it depends on what you're trying to store.You could say the zero's represent nulls but again it's really dependent on what's being stored. For instance, I've seen this type of design in several commercial incident tracking products. They allow the user to customize field names and data types. Generally they provide a characterdata1..characterdata10, date1..date10, bit1..bit10 set of columns. If you were to design such a system in house you'd likely name your bit columns for the precise attribute, like "IsClosed" or "IsBug" or "IsPEBKAC". It's a compromise between functionality, flexibility, and relational purity, and data integrity is either not important or relegated to the application logic. |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-12-20 : 12:26:23
|
quote:
How do you guarantee that the correct attribute applies to the widget, and conversely, prevent the wrong attribute from being applied? Even your original design doesn't necessarily enforce that, but it depends on what you're trying to store.
What design would enforce these things? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-12-20 : 13:20:54
|
If you're storing all kinds of widgets in the Widgets table (cars, computers, bicycles, clothing) and all kinds of attributes in a single table (engine, CPU, frame material, dress size) then that structure can't prevent a dress size or CPU from being assigned to a car or bike. You'd need to subtype the Widgets and Attributes tables (Cars & CarAttributes, Bikes & BikeAttributes, etc.) to properly enforce that relationship. However that may make a Widgets table redundant.On the other hand you could keep a single Widgets table and single Attributes table but add a Type column to each, so that each Widget is a Type (Car, Bike, etc.) and the Attributes are also similarly typed: CREATE TABLE Widget(WidgetID int not null primary key,WidgetTypeID int not null references Types(TypeID))CREATE TABLE Attributes(AttributeID int not null primary key,AttributeTypeID int not null references Types(TypeID))CREATE TABLE Widget_Attribute(WidgetID int not null,WidgetTypeID int not null,AttributeID int not null,AttributeTypeID int not null,CONSTRAINT FK_Widgets FOREIGN KEY(WidgetID,WidgetTypeID) REFERENCES Widgets(WidgetID,WidgetTypeID),CONSTRAINT FK_Attributes FOREIGN KEY(AttributeID,AttributeTypeID) REFERENCES Attributes(Attribute,AttributeTypeID)) You'd also need a check constraint for WidgetType=AttributeType. It's kinda messy and not ideal for data storage or flexible programming, but if you have a lot of types and attributes it may be better than having many similar or identical tables.As to which structure is better, it really depends on the details of your data and applications. Jeff's got a good article on table inheritance that might be a better solution for you:http://www.sqlteam.com/article/implementing-table-interfaces |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-12-20 : 17:48:42
|
| OK cool, thanks. |
|
|
|
|
|
|
|
|
What's wrong with this design?