db table with multiple foreign keys

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

db table with multiple foreign keys

Author

Topic

joseph17
Starting Member

5 Posts

Posted - 2012-07-06 : 11:43:58

Hi everyone,
I hope I write my problem out in a way that it makes sense and I hope I posted it on the right forum. I am creating an inventory DB for a product. Each product has 10 serialized components where I can pick one of each and build this product. I have 10 db tables, one for each serialized component. Each component has the same column names:

CREATE TABLE component1 <------ all other tables are named component2, component3, and so on
(
_id PK int NOT NULL Identity,
_sn varchar(3) NOT NULL,
_partNum varchar(4) default'1232' <----_partNum are different for each table
)

Ok now since one of all these components create a product I have a db table for the product:

CREATE TABLE product
(
_productID int NOT NULL IDENTITY,
component1 int FOREIGN KEY REFERENCES component1(_id)
component2 int FOREIGN KEY REFERENCES component2(_id)
component3 int FOREIGN KEY REFERENCES component3(_id)
//and so on.
)

Now for my question. I am creating a stored procedure that looks up the serial number and part number for any component on a product and from there will give me the product id and serial numbers for all conponents that make up that product. So far I find the table name that match the _partNum like so:

//I am looking up component1

select TABLE_NAME from localDB.information_schema.columns where column_name like '%_partNum%' and COLUMN_DEFAULT like '%1232%'

I return the TABLE_NAME, and run another store procedure to find the _id that matches the serial number. Once I have this _id, I run another query on the product table and return all values where this _id matches the TABLE_NAME I previously returned. This work well but I am trying to find a way to return all the _sn for each component table rather than the Foreign keys. I hope this makes sense and here's what I am hoping to have my output look like:

Currently:

product table
_productID component1 component2 component3
1 2 1 7
2 5 2 19

Desired Output:

product table
_productID component1 component2 component3
1 333 933 953
2 987 324 344

Thanks in advance,
Joseph

Joseph

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 12:04:07

Why do you need separate tables for components? isnt it just a matter of keeping them on same table as _partNum identifies each component details?

Anyways in current way, you could do something like


SELECT p.*,
c1.[_sn] AS Component1,
c2.[_sn] AS Component2,
c3.[_sn] AS Component3
FROM product p
LEFT JOIN component1 c1
ON c1.[_id] = p.component1
LEFT JOIN component1 c2
ON c2.[_id] = p.component2
LEFT JOIN component1 c3
ON c3.[_id] = p.component3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

joseph17
Starting Member

5 Posts

Posted - 2012-07-06 : 12:38:22

Thanks for the help visakh16. Your response makes sense. I was thinking about multiple subqueries but it seemed off. Regarding keeping all the _sn on the product table, I was thinking that originally but I thought it would be a better db design if all components are on separate tables that way the product table wouldn't contain the partnum, price, and any other information I might include for each component. I just wanted to keep the Product table simple.

Joseph

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-07-06 : 13:25:32

If you still have time, change your design. You will LOVE yourself down the road: http://en.wikipedia.org/wiki/First_normal_form

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 15:23:42

quote:
Originally posted by joseph17

Thanks for the help visakh16. Your response makes sense. I was thinking about multiple subqueries but it seemed off. Regarding keeping all the _sn on the product table, I was thinking that originally but I thought it would be a better db design if all components are on separate tables that way the product table wouldn't contain the partnum, price, and any other information I might include for each component. I just wanted to keep the Product table simple.

Joseph

Welcome
I dont agree to your point though
things would be much simple if you keep them together in same table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

joseph17
Starting Member

5 Posts

Posted - 2012-07-06 : 15:49:54

I am trying to understand the point of putting all my components _sn in the product table. I agree, it would be easier to select/search for information but if I wanted to include additional columns for each component such as part numbers, cost of the component, and lets say manufacture. Wouldn't that be to much information for my product table?
My product table would look like this:

CREATE TABLE product
(
_id int NOT NULL IDENTITY,
comp1_sn VARCHAR(3) NOT NULL,
comp1_partNum VARCHAR(4) DEFAULT '1234',
comp1_cost SMALLMONEY NULL,
comp1_manuf VARCHAR(25) NULL,
comp2_sn VARCHAR(3) NOT NULL,
comp2_partNum VARCHAR(4) DEFAULT '1234',
comp2_cost SMALLMONEY NULL,
comp2_manuf VARCHAR(25) NULL,
comp3_sn VARCHAR(3) NOT NULL,
comp3_partNum VARCHAR(4) DEFAULT '1234',
comp3_cost SMALLMONEY NULL,
comp3_manuf VARCHAR(25) NULL,
//and so on...
)

I would see all my component _sn being on the product table versus the FK if all components were identical but each component is different. Thanks again for all the advice, I just want to understand you POV a little better so I can take this into account for my database.

Joseph

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-06 : 15:54:11

quote:
Originally posted by joseph17

Wouldn't that be too much information for my product table?

No. Why do you think it's too much?

If all of the tables have the same columns, then you only need one table. And even if a few columns are a bit different, you could use nullable columns when that particular attribute doesn't apply to the component. But it doesn't sound like you have that situation. If there's a bunch of nullable columns, I'd properly stick that stuff in a separate table, maybe an EAV design for those.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 16:53:41

quote:
Originally posted by joseph17

I am trying to understand the point of putting all my components _sn in the product table. I agree, it would be easier to select/search for information but if I wanted to include additional columns for each component such as part numbers, cost of the component, and lets say manufacture. Wouldn't that be to much information for my product table?
My product table would look like this:

CREATE TABLE product
(
_id int NOT NULL IDENTITY,
comp1_sn VARCHAR(3) NOT NULL,
comp1_partNum VARCHAR(4) DEFAULT '1234',
comp1_cost SMALLMONEY NULL,
comp1_manuf VARCHAR(25) NULL,
comp2_sn VARCHAR(3) NOT NULL,
comp2_partNum VARCHAR(4) DEFAULT '1234',
comp2_cost SMALLMONEY NULL,
comp2_manuf VARCHAR(25) NULL,
comp3_sn VARCHAR(3) NOT NULL,
comp3_partNum VARCHAR(4) DEFAULT '1234',
comp3_cost SMALLMONEY NULL,
comp3_manuf VARCHAR(25) NULL,
//and so on...
)

I would see all my component _sn being on the product table versus the FK if all components were identical but each component is different. Thanks again for all the advice, I just want to understand you POV a little better so I can take this into account for my database.

Joseph

I would have done it by means of two table
1 for product and other for components

and components i'll store all components for product and link by means of product key. I'll be adding all attributes for components as columns in component table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

joseph17
Starting Member

5 Posts

Posted - 2012-07-06 : 16:55:42

Would one table be best if I had excess amounts of components (or spares) that do not make up a product.

Say I have
qty: 50 of comp1,
qty: 55 of comp2,
qty: 57 of comp3.

That's why I am using the approach of separate tables for every component. I think that by having separate tables for my components I can simply pull one from each component table and ensure my extra components will be in that table. Sorry to drag this topic on, I just want to design so its efficient.

Joseph

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 21:18:25

quote:
Originally posted by joseph17

Would one table be best if I had excess amounts of components (or spares) that do not make up a product.

Say I have
qty: 50 of comp1,
qty: 55 of comp2,
qty: 57 of comp3.

That's why I am using the approach of separate tables for every component. I think that by having separate tables for my components I can simply pull one from each component table and ensure my extra components will be in that table. Sorry to drag this topic on, I just want to design so its efficient.

Joseph

if you have components without products then you should be maintaining components as a separate single table and link it to product via an intermediate table productcomponents with ProductId and ComponentId as foreign keys

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources