Table layout for multiple product types

[stonward]

Hi NeoPa,

Yep, I think i get that, but how do i link my sales/purchasing tables - thru the productType table, or direct to the separate product tables? If to the 'main' product table, then what field can I use?

Thanx for your help - this prob is really giving me jip now!

StonwardR

[nico5038]

The ProductType can be a field in the "main" producttable.
This ProductType can be used to determine which fields are available of the linked tables.
Linking main with sub is done with the unique ID of the main table. Each sub will hold the main ID and it's own ID.

Clearer?

Nic;o)

[stonward]

Hi Nic;O,

Yep i get, and have done that - but I have problems linking the Purchase/Sale Details table(s)...If I use the ProductTypeID, that doesn't tell me WHAT product is being sold/bought....See my prob?

StonwardR

[NeoPa]

Hi NeoPa,

Yep, I think i get that, but how do i link my sales/purchasing tables - thru the productType table, or direct to the separate product tables? If to the 'main' product table, then what field can I use?

Thanx for your help - this prob is really giving me jip now!

StonwardR

The main point that I was trying to get across is that there are NO separate product tables. There is no need to separate the items into different tables. Simply include them all in the same table and include an extra field to flag each record as one of the three different types.

As far as linking into (or from) the sales and purchasing tables is concerned, I expect they would want to link to the Product code in the main (single) Product table.

[stonward]

I see,...got 2 remember all what I have tried now. I originally used that very idea, using ONE products table, but with a Categories (type) field. The problem I have is when I have more than one type of product in one sale (on one invoice)...but I'm gonna go back and try it again now.

Thanx for your help.

R

[NeoPa]

If you have multiple products on an order (sales or purchase) then it is necessary to have an Order Header table as well as a linked Order Details table. Each product would go on an Order Detail record. If you try to bundle them all together by having multiple products per Order record you will certainly have problems as the structure of your concept does NOT match the structure of your situation.
Have a look at Normalisation and Table Structures.
This may be a lot to take on for you now - but with it you will avoid many future issue. Without it I'm afraid the reverse is true. Expect various problems to occur unless you have this basic understanding of how to organise your data.

[stonward]

Of course...

I've had that down for some time now (although I don't yet grasp SELF joins!)...and I have Sales/Purchase DETAILS tables because of the many
:many join that would otherwise occur.

This problem arose when the guy who needs this database suddenly required the ability to 'sell' more than one type of product at one time, in addition to adding some services to the product range...

I've got a couple of new ideas to try,...I'll get the details straight in my mind, then come back to you with an accurate explanation of the problem.

Thanks, Guys!

R

(I promise it's nothing so very simple tho...!)

[NeoPa]

That's fine R.
We have very little clue as to how advanced a question (or questionner) is on here. We get the full range of course. I wasn't trying to imply you were less advanced than you clearly are :)