Need an Advice Regarding Table Creation.

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

Need an Advice Regarding Table Creation.

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-23 : 09:49:06

Hello Good Evening.

Could you please give me an advice on the proper creation of tables with foreign keys?

I am about to create a 2 Tables that has a relationship to each other.

This is to make my tables efficient and to have a data query that is much faster.

Lets say I have this Table Item and Vendor.

Please check which of the 2 idea is better.

Item Table will have ID and Name Column while Vendor table will have Vendor ID, Vendor Name, Item Name and Price.

Is it ok if i will use Item_name column as the foreign key between Item and Vendor Table or should I include the Item_id column in the vendor table to make it as the foreign key?

Please give me the standard or ideal way on how should I do it.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 11:27:10

the ideal way i will implement this would be using three tables

1. Vendor (VendorID,VendorName)
2. Item (ID,Name)
3. VendorItems ( VendorItemID, VendorID (fk to Vendor (VendorID)),ItemID(fk to Item(ID)))

This enable you to maintain each unique Item by same unique id even if they're supplied by multiple vendor. Each Item supplied by Vendor will have a record in VendorItems table with Vendor and Item details denoted by referring ids from master tables.

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

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-24 : 07:10:39

quote:
Originally posted by visakh16

the ideal way i will implement this would be using three tables

1. Vendor (VendorID,VendorName)
2. Item (ID,Name)
3. VendorItems ( VendorItemID, VendorID (fk to Vendor (VendorID)),ItemID(fk to Item(ID)))

This enable you to maintain each unique Item by same unique id even if they're supplied by multiple vendor. Each Item supplied by Vendor will have a record in VendorItems table with Vendor and Item details denoted by referring ids from master tables.

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

Yea. This is the best Normalized form you could use.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-24 : 08:09:53

Noted Sir Thank you.

But Can`t I just use the vendor name of Item Name as the foreign key?

If these two columns will be unique can would it be possible?

I believe that using vendorID and ItemID would require me to use joins upon querying. If my database has gone bigger if could take some time in querying cause it still need to look for the other table.

If I will use the Vendor Name and Item Name I can query without using joint the only time that i would use joint is when I need to query some details from vendor or Item Table.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-24 : 08:40:40

Since you have two uniquely identifiable entity groups in VENDOR and ITEM, the best DB Design would be to have two different master tables - one for VENDOR and one for ITEM.
Then you can have another table for linking all the ITEMS to the various VENDORS.
This is the best and the most Normalized DB Design.
You can accomplish any no. of complex Business Requirements in the most optimal possible way.
I did not understand what exactly you are trying to do with the JOINS. But whatever it is, it can be accomplished using this design.
Please elaborate a little more on what you are trying to do and what problem is the DB design Visakh gave causing.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-24 : 12:53:37

Thank you sir for your response.

Well with these design ( VendorItemID, VendorID (fk to Vendor (VendorID)),ItemID(fk to Item(ID)))

The query that i should do is like these

Select a.VendorItemID,
b.vendorname as Vendor,
c.itemname as ITem
from VendorItems a
inner join Vendor b on a.vendorid = b.vendorid
inner join ITem c on a.itemid = c.itemid.

I just worried that if the my table gets big it might take some time to process the querying. Would querying time will not be affected by these design?

I just wondered if I can use these one
( VendorItemID, Vendorname (fk to Vendor (Vendorname)),Itemname(fk to Item(name)))

If using these on the only table that i will need to access it the
vendoritem table.

Is it a bad idea if I use a varchar data type as foreign key?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-24 : 13:11:53

quote:
Is it a bad idea if I use a varchar data type as foreign key?

Not necessarily, but it's not particularly useful either. One problem is having multiple vendors with the same name (it happens), you can't differentiate them. There's also the case where a vendor may change their name. In that case, you'd have historical items under the old vendor name that would get updated with the new name, which is not likely what you want. It's better to manage the foreign keys with an ID that is related but independent of the vendor name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources