Combining data from multiple tables

[asmith3006]

Hi,

 

I'm making a stock control system and want some advice with the best way to do things.

 

Each of our products that we sell is actually made up of other components that we buy. I want to store the details for the components and for the piece that we sell, but there is no guarantee of how many pieces make a product.

 

I was thinking of storing them:

Sellable:

=============

Product ID

Description....

blah

blah

=============

 

Components

=============

Comonent ID

Sellable ID

Blah

Blah

=============

 

Which will work obviously, but I'm not sure what the best way to output the combined data would be. Would I need to use a JOIN? A "References"? (what does that do??)

 

Any and all help would be great. Thanks.

 

Andrew.

[waterssaz]

If I'm understanding what you what to do corrcetly then a few pointers that I would do are:

1: For your database structure->In the components table I would have a field called something like relating_product_id and this as its name suggests would hold the product ID that relates (foreign key) to that component. This will combat your one product to many components problem.

 

2: Then if you do the above you can select all components where there relating_product_id is equal to the product id you want to output in php script using a simple where = "" query.

 

:-)

[asmith3006]

Ah I see, so something like

SELECT * FROM Sellable s, Components c WHERE s.ProductID = 'Value' AND s.ProductID = c.SellableID

 

Do I understand correctly?

[waterssaz]

Yes, although obviously the join syntax would depend on the type of database you are going to use :-)

[asmith3006]

Thanks for confirming.

 

What do you mean by type of database?