Products database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi! I'm attempting to build a database for my company to easily track orders.
We have some product codes that are a combination of two or more other
products. for example, we sell a bundle of a PC and a monitor, so that
bundle has its own product code. But the PC and monitor are also sold
separately, and each have their own product codes. I'm wondering how I can
tie these together so that I can track inventory levels. For example, if the
bundle gets sold, the individual inventory levels of both the PC and the
monitor should go down.

Is this possible in Access?
 
tblInventoryItems
InvID (pk)

tblMerchandiseItems
MerchID (pk)

tblMerchandiseInventory
MerchInvID (pk)
MerchID one-to-many to tblMerchandiseItems
InvID one-to-many to tblInventoryItems
InvQuantity
- Combination of MerchID & InvID can probably have a unique index.
- Single-item Merchandise will have one record in this table (InvID &
MerchIDs might even be the same)
- Merchandise with 3 Inventory components will have 3 records.
- Structure allows a "2-for-one special" MerchID, if desired.

Merchandise sales go through tblMerchandiseInventory to determine what
inventory items have been sold.
 
OK, I tried this, but I have a few questions:

1. In tblMerchandiseInventory, do I need to have a separate pk? Can't my pk
be MerchID + InvID? Or is there a reason to have a separate pk?

2. Shouldn't InvQuantity be in tblInventoryItems? If one of my inventory
items is part of several merchandise items, and I enter InvQuantity in
tblMerchandiseInventory, then I'll be duplicating information, right?

Thank you!
 
1) Pureists might tar and feather me but: Flip a coin. As long as you have
a unique index on the combination, I don't see why you can't use that as the
PK if you want. Then again, an independent PK never hurt anyone. At worst
you find that you end up never using it as a Foreign key anywhere.

2) Yes, the actual warehouse quantity goes in tblInvItems (which I didn't
show). The quantity field in tblMerchInv is different. It allows you to say
"this Merchanise has 2 of these Items and 1 of these Items". i.e., it
controls how much you decrease tblInvItems inventory level when a sale is
made, or can tell you how many more you can sell based on current inventory.

HTH,
 
Back
Top