relational tables help

[glennnall]

ok, i'll ask it this way:

 

i have an inventory of about 700 products, each with an individual part_number, of course, and a list of only, say, three manufacturers: "SKB", "UK" and "ZERO"

 

i'm attempting to learn good normalization technique and i can't decide how to create a manufacturers table to be relational to my part_numbers table.

 

can someone show me, and an example of a query that will return the associated mfr from a list of part_numbers?

 

i'm very grateful

 

gn

[Andy17]

I'm not 100% sure I understand what you want. But, if you have a product which has a single manufacturer associated with the product, you can simply have a foreign key in your product table that references a column in a manufacturer table. So, a query could look like this:

 

SELECT * FROM Manufacturer WHERE ManufacturerId = (SELECT ManufacturerId FROM Product WHERE someUniqueColumn = 'someUniqueValue')

 

That would give you the manufacturer details for a given product. As I was reading your post again, it seems like I misunderstood what you wrote, but I hope the above is of any help anyways - otherwise I will have to try again.

[glennnall]

thanks, Andy -

 

yep, i wasn't very clear.

 

what i'm looking for is a proper database schema for data like this. i'll worry about the queries once i have a set of relational tables...

 

i'm going to attach an example of the overall database i have that i need to normalize...

 

i'm trying to get help building a set of tables like this properly.

 

thanks much

gn

 

[attachment deleted by admin]

[glennnall]

anybody...?

[ignace]

SELECT * FROM Manufacturer WHERE ManufacturerId = (SELECT ManufacturerId FROM Product WHERE someUniqueColumn = 'someUniqueValue')

 

Euh... Why need the sub-query?

 

SELECT * FROM manufacturer
JOIN product ON manufacturer.id = product.manufacturer_id
WHERE product.some_unique_value = 'some_unique_value';