March 5, 2009 at 12:05 pm
I'm trying to capture similar data in one table rather than create a separate table for each type of data I'm capturing.
For example, I have 3 tables...
Products
ProductID
ProductName
Hardware
HardwareID
HardwareName
Components
ComponentID
ComponentType
ProdHwID
The ProdHwID field in the Components table can be either the ProductID from the Products table or the HardwareID from the Hardware table. The ComponentType field would identify whether the component was Product or Hardware.
How do I connect those so that I would be able to return the ProductName or HardwareName from the appropriate table?
Any recommendations?
March 5, 2009 at 12:14 pm
You won't be able to apply a direct foreign key relationship to that column.
You could accomplish much the same by having two columns, one for hardware ID and one for product ID, and have FK constraints on those. You could even have a constraint that would force one or the other to be null, so you wouldn't end up with values in both columns. And you could have a calculated column that used IsNull to hold whichever value was filled in.
Or, just don't plan on having a foreign key constraint on it, and you can join tables in whatever manner you like. You could still enforce referential integrity through triggers in that case.
Either solution works.
Possibly a better solution, however, would be to combine the hardware and products tables into a single table, with a column to indicate which type it is. That's the direction I'd be inclined to go at first.
One possibility you might want to think about is a standard "bill of materials" table. You'd have an item, and a "ParentItemID", a type and whatever other fields you need. Query the one table, using recursive hierarchy techniques, and you can find out what anything is made of, and what it can be combined into. That's pretty standard.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2009 at 4:12 pm
GSquared (3/5/2009)
You could still enforce referential integrity through triggers in that case.
And I have been told that triggers are almost as evil as cursors :hehe:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply