February 27, 2009 at 2:23 pm
I have a question of best practices for this scenario:
tblOrderLineItem, columns: RowID, OrderID, ItemTypeID, ItemID, Qty
tblItemType, columns: ItemTypeID, Name; Name row values: 'Product', 'Gizmo', 'Widget'
tblProduct, columns: ProductID, Name, etc
(2 other tables for items not qualifying as Products)
tblGizmo, columns: GizmoID, Name, etc
tblWidget, columns: WidgetID, Name, etc
So,
tblOrderLineItem.ItemID -> tblProduct.ProductID OR
tblOrderLineItem.ItemID -> tblGizmo.GizmoID OR
tblOrderLineItem.ItemID -> tblWidget.WidgetID
Based on tblOrderLineItem.ItemTypeID
Short of trying to consolidate Product, Gizmo, and Widget in to a single master Product table, I am wondering what is the best practice for this situation? An intermediate cross-reference table, etc?
Thank you,
JG
March 1, 2009 at 6:31 pm
Your only problem is the foreign key so the options are:
1. Support the integrity programatically - maybe by a trigger.
2. A single referenced table with possibly a lot of attributes that are only applicable to a single type.
3. A table with type, id to support the foreign key. Notice you have the same problem when linking to the attribute tables - maybe set up like 7 the id in one column for the FK to line item, columns for the individual IDs for FK to the attribute tables .
4. A single referenced table with the columns having different meanings depending on the type. Meanings held in another table.
5. A table to hold all common attributes between the types then attribute tables for the rest (see 3 for id columns).
6. Use an xml column to support the attributes.
7. Put the id's in different columns depending on type - the others being null.
3. is useful if you need to allocate the ID's.
v2008 has support for sparsly populated columns which might help with 2.
4. Is often used for systems which need to be user configurable (crm systems?)
1. is probably the simplest solution.
Note you can use a combination of the above. I would often go for 5. which may in your instance be the same as 3. It means a structure change if another attribute table is added though.
Wouln't go for 6. probably but have often thought of it.
Depends a lot on the system - the data profile and how the system is going to be used.
Cursors never.
DTS - only when needed and never to control.
March 2, 2009 at 10:48 am
Your solution may be affected by whether there are external reasons to maintain separate series of ID values for Product, Gizmo and Widget, but it seems that you may want to normalize this around a "tblItem" entity/table. This would be much as Nigel suggests for his option 3 or 5, but it seems to me that the three attribute tables for Product, Gizmo and Widget would carry a foreign key up to the parent tblItem rather than the other way round.
So, the tables would be defined as
tblOrderLineItem, columns: RowID, OrderID, ItemID, Qty (drop itemTypeID)
tblItem, columns: ItemID, ItemTypeID, ItemName... (and other attributes common to any item regardless of type)
tblItemType, columns: ItemTypeID, TypeName (Product, Gizmo or Widget) and other attributes about the use of this type. You may have just three rows here.
tblProduct, columns: ItemID, ProductID... (and other attributes specific to Product)
tblGizmo, columns: ItemID, GizmoID... (and other attributes specific to Gizmo)
tblWidget, columns: ItemID, WidgetID... (and other attributes specific to Widget)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply