MS SQL Table Relationship


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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kyle AbrahamsSenior .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

select * from Product

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EscanabaAuthor Commented:
Really appreciate the detailed explanation (and patience).  This was very helpful.  Thank you!!
Kyle AbrahamsSenior .Net DeveloperCommented:
Good luck!  Feel free to post questions as you run across them.  We're here to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.