By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Multiple product classes table design

P: n/a
Hi there, I'm just in the paper-designing stage of an inventory / sales
database but am looking for some help and suggestions as it seems a
little out of the norm due to the different classes of products
available.

Basically for inventory, I have 3 different classes - products used
In-house and not sold, products sold as Retail, and then component
products which are then assembled into either Custom or House-Brand
retail products. My first thought was to separate these out into
separate tables with 1-to-1 relationships such as:

/ In-House (Product ID)
Inventory (Product ID) - Retail (Product ID)
\ Components (Product ID)
I am not sure about how to further handle the Components table, I
thought about further splitting it up to a Custom products table and a
House-Brand table with 1-to-many such as:
Components (Product ID) - CustomComponents - Custom (Invoice ID)
\ House-BrandComponents - House-Brand (Invoice
ID)

However, I see a couple of problems in that the House-Brand products
are always in stock so don't need an invoice to be made up, whereas the
Custom ones are only made to order. Further, the Custom ones are
sometimes based on a modicifation of a House-Brand with additions from
the Components table, but are also sometimes made entirely from scatch
based off the Components table so not sure if there is some way to base
the Custom off of the House-Brand.

My final problem is how to handle the customer order / invoice.
Normally if it just pulls from one inventory table it seems
straightforward, but with these different products, a customer can
place an order for a Retail product, a House-Brand product, and a
Custom product all on the same order. They would all have a Product ID
to refer to but not sure how the relationships would be set up.

Any suggestions would be greatly appreciated. Thanks very much for your
help.

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
A really good source for inventory applications is MVP Allen Browne's site,
http://allenbrowne.com/

Larry Linson
Microsoft Access MVP

<ya****@intergate.ca> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Hi there, I'm just in the paper-designing stage of an inventory / sales
database but am looking for some help and suggestions as it seems a
little out of the norm due to the different classes of products
available.

Basically for inventory, I have 3 different classes - products used
In-house and not sold, products sold as Retail, and then component
products which are then assembled into either Custom or House-Brand
retail products. My first thought was to separate these out into
separate tables with 1-to-1 relationships such as:

/ In-House (Product ID)
Inventory (Product ID) - Retail (Product ID)
\ Components (Product ID)
I am not sure about how to further handle the Components table, I
thought about further splitting it up to a Custom products table and a
House-Brand table with 1-to-many such as:
Components (Product ID) - CustomComponents - Custom (Invoice ID)
\ House-BrandComponents - House-Brand (Invoice
ID)

However, I see a couple of problems in that the House-Brand products
are always in stock so don't need an invoice to be made up, whereas the
Custom ones are only made to order. Further, the Custom ones are
sometimes based on a modicifation of a House-Brand with additions from
the Components table, but are also sometimes made entirely from scatch
based off the Components table so not sure if there is some way to base
the Custom off of the House-Brand.

My final problem is how to handle the customer order / invoice.
Normally if it just pulls from one inventory table it seems
straightforward, but with these different products, a customer can
place an order for a Retail product, a House-Brand product, and a
Custom product all on the same order. They would all have a Product ID
to refer to but not sure how the relationships would be set up.

Any suggestions would be greatly appreciated. Thanks very much for your
help.

Nov 13 '05 #2

P: n/a
simplified tables (* is PK)

1) productClass
productClassId (*)
productClass (In-House, Retail, Component, Manufactured)
2) product (*)
productId
item
description
productClassId
qtyOnHand
3) productComponents (required for manufactured items)
productId (*)
componentProductId (*)
componentQty
4) productTransaction (tracks comings / goings of inventory)
productTransactionId (*) (using qtyOnHand as a base)
productId
transactionDate
transactionQty
reference
5) customer
customerId (*)
name
phone
fax
...
5a) customerContact
customerId (*)
contactId (*)
name
phone
fax
...

6) salesOrderHeader
salesOrderId (*)
customerId
shipAddress (street, city, state/province, postal code/zip)
invoiceAddress
contactId
orderDate
shipDate
discount

7) salesOrderDetail
salesOrderId (*)
salesOrderLine (*)
productId
salesQty
unitPrice
discount

8) shipment
shipmentId (*)
salesOrderId
salesOrderLine
shipmentLine
shipQty
boQty
invoiceId
9) invoiceHeader
invoiceId (*)
customerId
invoiceDate
invoiceAddress
..

10)invoiceDetail
invoiceId (*)
invoiceLine (*)
productId
shipQty
boQty
unitPrice
discount

11) invoiceMisc
invoiceId (*)
miscCodeId (*)
miscAmt

12) miscCode
miscCodeId (*)
miscCode (GST, PST, VAT, Freight, Restocking, etc)

ya****@intergate.ca wrote:
Hi there, I'm just in the paper-designing stage of an inventory / sales
database but am looking for some help and suggestions as it seems a
little out of the norm due to the different classes of products
available.

Basically for inventory, I have 3 different classes - products used
In-house and not sold, products sold as Retail, and then component
products which are then assembled into either Custom or House-Brand
retail products. My first thought was to separate these out into
separate tables with 1-to-1 relationships such as:

/ In-House (Product ID)
Inventory (Product ID) - Retail (Product ID)
\ Components (Product ID)
I am not sure about how to further handle the Components table, I
thought about further splitting it up to a Custom products table and a
House-Brand table with 1-to-many such as:
Components (Product ID) - CustomComponents - Custom (Invoice ID)
\ House-BrandComponents - House-Brand (Invoice
ID)

However, I see a couple of problems in that the House-Brand products
are always in stock so don't need an invoice to be made up, whereas the
Custom ones are only made to order. Further, the Custom ones are
sometimes based on a modicifation of a House-Brand with additions from
the Components table, but are also sometimes made entirely from scatch
based off the Components table so not sure if there is some way to base
the Custom off of the House-Brand.

My final problem is how to handle the customer order / invoice.
Normally if it just pulls from one inventory table it seems
straightforward, but with these different products, a customer can
place an order for a Retail product, a House-Brand product, and a
Custom product all on the same order. They would all have a Product ID
to refer to but not sure how the relationships would be set up.

Any suggestions would be greatly appreciated. Thanks very much for your
help.


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.