Multiple ID's

[dan1dyoung]

Just a quick one im sure.

If i have 3 tables like below

tblPart
PartID
PartNumber
Price
e.t.c

tblKit
KitID
KitPartNumber
Price
e.t.c

tblProduct
ProductID
ProductCode
Price
e.t.c

tblOrderDetail

Do i have:

ProductID
KitID
PriceID

in the table, and the same in a stock table, or do i have 1 ItemID and link that to all three PK fields in the other tables???

Hard to explain

Thanks

Dan

 

[FlyerMike]

Is it possible for a PartID to be the same as a KitID or ProductID? I'm reffering to the ID value.

If they are all unique, then my OrderDetail would look like

ID (which would be PartID, KitID, or ProductID)
Quantity
....

I'd also consider putting the Parts, Kits, and Products into one Inventory table, with a field that differentiates the inventory as one of the those types.

 

[dan1dyoung]

Thanks Mike

Thanks Mike,

The productID, KitID, and PartID are all going to be randon numbers as i want to use synchronisation later on, but what if in the order and inventry table i have ItemID and i create a relationship between that and the productID, KitID, and PartID (one-Many), what happens if the ProductID happens to be say 25 and so does the PartID??? Will the inventry table see these as unique as they are from different tables?? If not how else can i do it as the Product, Kit, And Part tables will have very different fields, and the kits will be made up of multiple parts??

 

[FlyerMike]

Dan:

I would not put the parts, kits and products in separate tables. Without knowing more about your business rules, I'd put them all in one table.

Table: Kitchen Sink
ItemID (Autonumber)
ItemDescription (Text)
Price (Currency)
ItemCategory (Number or Text: Part, Kit, or Product)
...

I'd link the Kitchen Sink table to the Order table through the Order Details table noted earlier.

 

[dan1dyoung]

Mike,

What about when multiple parts make up a kit???

 

[FlyerMike]

I'd add another Table:

Table: Kits
Kit ID: Long (lookup from Kitchen Sink where type = "kit")
Part ID: Long (lookup from Kitch Sink where type = "part")

I'd use this table to declare the parts that comprise a kit. I'd populate it with a continous subform linked to the record from Kitchen Sink.

Q1) If a part is an ingredient of a kit, should a customer be able to order the part separately?

If I order the kit, I have indirectly ordered all the parts within the kit.

 

[dan1dyoung]

Mike,

sounds logical, but im not quite sure on how to fully implement that, not done much on continues forms, e.t.c

do you have an example i can look at??

everytime i think im getting somewhere, i hit another wall!!!!


Oohhh and yes they can buy the parts seperatly as well as in a kit

 

[FlyerMike]

Dan:

See the attached example.