MS SQL Table Relationship

Hello,

I'm trying to work out a SQL problem and found myself stuck so hoping someone can assist.  Based on the attached document, I have created three tables and inserted vendors into the vendor table.  But I am not understanding how to get the data in part 3 into the vendor product table.

Would greatly appreciate any assistance as I am just learning SQL.

Thanks!
EE-SQL-Sample.docx
LVL 1
EscanabaAsked:
Who is Participating?
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
The foreign Key just states that it must exist in the parent table while it exists in the child table.  (EG:  You could not create a child record without it existing in the parent and the parent cannot be deleted while it exists in the child).

For example:

you have a list of vendors:
1 - Microsoft
2 - Apple
3 - Google

you have a list of products:

1 - Operating systems
2 - Phones
3 - Games
4 - Consoles

Then you tie them together in the list of VendorProducts.  In that table would live:
Vendor ID / Product ID
1 / 1
2 / 1
3 / 1  
(EG: all 3 vendors make Operating systems)

but you would only have
1 / 4
(EG: XBOX type console made by microsoft).

Before that record existed you could have deleted 4 as an  product, but you couldn't have deleted 1 as it still had operating systems.
As soon as that record is inserted, then you can no longer delete 4.  Similarly, you couldn't put in a 4th vendorID without it first existing in the Vendor Table.

That's what the foreign key does.  Without it, I could put a value of -1 / 203948 . . . but it wouldn't mean anything as it doesn't represent anything.


Database design can be complex at times but like anything else there are patterns used as people have found the best way of representing different real world scenarios.  I would google "database normalization" if you're interested in the theory behind why tables are constructed the way they are.

But to get back to your issue, you just need to put the vendor IDs and the productIDs that are supported into the vendorProducts table.  You could then get to all information using joins.  One of the other major benefits of this layout is say that you had a vendor change name.  If you're using an integer representation of said Vendor all over the place that refers back to the vendor table you could then just update the vendor table with the new name and that would "flow" to all of the other places  (as opposed to going to the 2 (and most likely more in a real world scenario) tables and running an update on each).

You have created your vendors, but haven't setup any products yet, so that's the next step.

Insert into Product (productName) values('Red Shoes')

Open in new window


Then from there you can put in who does redShoes.
insert into VendorProduct(Cost, ProductID, VendorID)
values (50, 1, 1)

insert into VendorProduct(Cost, ProductID, VendorID)
values (55, 1, 2)

Open in new window



you can run this query to verify your IDs:
select * from Vendor
go

select * from Product
go

Open in new window


And you can check your work using the following query:

select v.VendorName, P.ProductName, VP.Cost
from VendorProduct VP
join Vendor V on  VP.VendorID = V.VendorID
join Product P on VP.ProductID = P.ProductID

Open in new window


Lastly just on a side note QtyOnHand most likely belongs in the vendorProduct table as different vendors would have different quantities.

Hope that helps.  Kudos for trying to learn and don't worry about being confused.  It's part of the learning process.  Feel free to ask more questions if you have them.
1
 
slightwv (䄆 Netminder) Commented:
Your vendorproduct table doesn't have a vendorname or productname column.  Therefore, you cannot insert values into them.

Since the vendorproduct table has foreign key references to the vendor and product table, you need to insert the correct rows in there first.  Once you've done that you need to insert the correct productid and vendorid values into the vendorproduct table.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Just to restate what Netminder set in a different way:

Ensure you have the product and the vendor inserted first.  When you insert them they will get their ids because they are set to identity.

You then insert the ids of the vendor / product into the vendor table product so you know what vendors provide which products.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
EscanabaAuthor Commented:
Kyle - Is that not what I've done by referencing the product and vendor IDs as foreign keys when I created the vendor product table?  Sorry - just really confused.
0
 
EscanabaAuthor Commented:
Really appreciate the detailed explanation (and patience).  This was very helpful.  Thank you!!
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Good luck!  Feel free to post questions as you run across them.  We're here to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.