Complicated Relationship

[Bert666]

Hi there people,

I am designing a database for a Home Textile Exporting firm, and i am stuck. My problem is that when a buyer places an order he/she can order one or more than one product - which means a one to many relationship between the PO table and the Products table - This is fine. Now i have created a Products Base table where i will hold all the products (Here is the problem) Each product can be an inividual product eg. Bedsheet, pillow etc. or a set of products eg. The product Sheet Set holds a Sheet, a fitted and two Pillow cases, now the problem gets more complex because they also have a product by the name of BNB (Bed in a Bag) - this product Contains Individual products as well as products which are themselves sets eg. Sheet Set. The way i have designed the tables are as follows:

Main Products Table
Product ----------
| Sub Products Table
-------------- ITEM
| Sub ITEM TABLE
---------------------- ITEM

Explaination:

One product can have multiple products within - each product within the product can have more products within it.

The main problem i am having is with the report (Stock Report) - they would like to see the stock in terms of sets as well as indiviual products.

Secondly if i go forward with this design - and in the future they decide to sub product even after the third level eg.

One product can have multiple products within - each product within the product can have more products within it & each product here can have more products within.

Please HELPPPPPPPPPPPPPP


Bert.

 

[AncientOne]

Bert

you may be confusing yourself by thinking about these things as products,sub-products and sub-sub-products.

Most stock control can be done by simple calculations:

say 500 individual sheets + 50 sheet sets each containing 2 sheets + 10 Beds in Bags each having 2 sheet sets= 640 sheets. You could even devise a coding scheme for each product defining the sub-products within it.

So, unless I am being dense (entirely possible), I don't see that you need all these product tables in the first place...Just one product table.

 

[Bert666]

Coplicated Relationships

Thanks for your suggestion, Will rethink the design.

Thanks again,
Bert

 

[lynsey2]

if you have a productID for your products would that not help?
e.g.
single sheet on its own has a code of 1
a pack with a single sheet and pillow case has a code of 2