normalization/ table mysql worries

elskan

I have three tables, TBLproduct, TBLmaufacturer, TBLcategory,

TBLproduct holds prod info
TBLmanuf holds manufacturing info
TBLcategory hold category info

How do I actually display the category and manufacturing fields when I want to display the contents of TBLproducts. I want to link the three together, I do not wnat to keep putting in the the same manufacturer just reference it in the tblproduct the same goes for the category.

I am doing this right. I was told that if you are repeating data you should put it in another table and index key it Is this right?

Sorry for what is probably a very staright forward question!

Thanks for your help in advance, kind people.

elskan

superwormy

Not sure I understand... you just mean you want to link the tables together yes...?

Product_Info
ID, Manufactuer_ID, Category_ID, Name, Descrip

Manufacturer_Info
ID, Name

Category_Info
ID, Name

SELECT * FROM Product_Info WHERE ID = 2

We'll assume that returns:
2, 15, 23, Cow Flashlight, A Flashlight That Looks Like A Cow

SELECT * FROM Manufactuer_Info WHERE ID = 15

SELECT * FROM Category_Info WHERE ID = 23

adamgeorge

I think what you are trying to do (or what you asking for) is how to link tables together using relationships.

What you really need to do is go get yourself a book on Database design and normalization. There's squillions for sale on Amazon.com and it will be money very well spent.

There's also heaps of web resources on Database design that you can look at too:

Database design and normalisation techniques

But just to get you started, what I think you need to do is create relationships between your tables using Primary and Foreign keys. This way you can keep data in your tables normalised. As you said, a normalisation technique is to remove repeating data into a seperate table and link it back to the parent table by storing the parent's primary key as a foreign key in the new table.

This will remove redundant data, and allow you to still link together the tables if you need to join the data back together in a query.

If you get a good book you can learn about it in depth, and heaps of other database design techniques and will be a good resource for the future too.

k, hope that helps
-Adam 🙂