Database Layout V2

[wfernley]

Hi everyone :)

Well I made a post earlier about this problem and I thought from the posts I had a good idea of what to do, but my client wants it a bit different now. Because of this, I'm really stuck on how to do this. :S

Basically what I have to do is this:

Every product in the catalog will have a section called "Where to Buy". In this section it will list where the distributors of the product can be found. Basically all its going to have is an image of the distributor, a link to A) the main page of the distributor or B) directly to the product on their site. Each product will have anywhere from 5 to 15 distributors.

The way I had it before was a MySQL table called manufacturers and in the table I had the manufacturer_id, manufacturer_name, manufacturer_image, manufacturer_url. Then I had a seperate table that had a products_manufacturers where you had the product_id and the manufacturer_id to link the product to the correct distributor.

My problem now is this....Each product will have more than 1 distributer (which was not the way I designed it before -it only worked with 1) Also each product may have a different link if I am linking to products where neccessary. (which means the manufacturer_url is no good because I would need more than 1 link)

I hope I explained my problem good enough for everyone to understand :)

Can anyone help me out? How should I design the database for all this to work?

Thanks in advance for all your help :)

Wes

[Sanenet]

HI wfernley,

If I understand you correctly, I think something like this would do the trick (ID is always a unique identifier):

PRODUCTS
ID, Name, Description...

Manufacturers
ID, product_id, name...

URLS
ID, product_id, name, url...

[other associated tables]

The idea is that PRODUCTS is the main table with your list of products. Then you can have various entries in the other tables, associating product_id to PRODUCTS.ID, and do a lookup as so:

SELECT * FROM urls WHERE product_id = 'PRODUCTS.id'

Get the idea? The easiest way (for me) is to write down what information you want stored, get a big sheet of paper, and plan it out.

[Raymond]

If your products URL is unique:

Products(*ProductID, WhateverFieldsYouWant)

Distributors(*DistributorID, URL, WhateverFieldsYouWant)

P_D_Link(*ProductID,*DistributorID)

If not

Products(*ProductID, WhateverFieldsYouWant)

Distributors(*DistributorID, WhateverFieldsYouWant)

DistributorsURLs(*DistributorID, *URLID, URL)

P_D_Link(*ProductID,*DistributorID, *URLID)

[wfernley]

Great! thanks for the posts. :)

That does help, but how would I get it so each product displays more than 1 distributor?

Wes

[Robino]

PRODUCTS.Distributor1

PRODUCTS.Distributor2

PRODUCTS.Distributor3

[Raymond]

Great! thanks for the posts. :)
That does help, but how would I get it so each product displays more than 1 distributor?

This table
P_D_Link(*ProductID,*DistributorID)

You can add as many URL as you want because BOTH fields form one unique key.

This table
P_D_Link(*ProductID,*DistributorID, *URLID)

You can even add multiple URLs from the SAME distributors along with other distributors for the same product because the Distributor and DistributorsURLs has a 1 to many relationship.

[wfernley]

Sorry, I'm sure this is going to be such a noob question. I am still starting out with PHP and MySQL. I have seen that expression a few times now. What is a 1-to-many relationship?

Also, what does this mean? PRODUCTS.Distributor1?

Thanks again for your help :)

Wes

[danieljean]

This was the best article I found back when I was starting out with db design:
[phpbuilder.com...]

My shopping cart now contains over 60 tables, and I would not have been able to understand it without that article. Good luck! :)

[wfernley]

Thanks DanielJean. I read over that article and I am completely confused. I did get the basics of it though and I'm sure I can get it once I go over it a few more times.

Thanks again :)

Wes

[danieljean]

heh. It took me a few readings, and I kept referring to it even 2 years later... it is well worth it though!

good luck ;)