Multiple product classes table design

[yackel@intergate.ca]

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) - CustomComponent s - 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.

[Larry Linson]

Re: Multiple product classes table design

A really good source for inventory applications is MVP Allen Browne's site,

http://allenbrowne.com/

Larry Linson
Microsoft Access MVP

<yackel@interga te.ca> wrote in message
news:1122486883 .337992.251310@ g43g2000cwa.goo glegroups.com.. .[color=blue]
> 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) - CustomComponent s - 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.
>[/color]

[lesperancer@natpro.com]

Re: Multiple product classes table design

simplified tables (* is PK)

1) productClass
productClassId (*)
productClass (In-House, Retail, Component, Manufactured)


2) product (*)
productId
item
description
productClassId
qtyOnHand


3) productComponen ts (required for manufactured items)
productId (*)
componentProduc tId (*)
componentQty


4) productTransact ion (tracks comings / goings of inventory)
productTransact ionId (*) (using qtyOnHand as a base)
productId
transactionDate
transactionQty
reference


5) customer
customerId (*)
name
phone
fax
...


5a) customerContact
customerId (*)
contactId (*)
name
phone
fax
...

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

7) salesOrderDetai l
salesOrderId (*)
salesOrderLine (*)
productId
salesQty
unitPrice
discount

8) shipment
shipmentId (*)
salesOrderId
salesOrderLine
shipmentLine
shipQty
boQty
invoiceId


9) invoiceHeader
invoiceId (*)
customerId
invoiceDate
invoiceAddress
..

10)invoiceDetai l
invoiceId (*)
invoiceLine (*)
productId
shipQty
boQty
unitPrice
discount

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

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



yackel@intergat e.ca wrote:[color=blue]
> 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) - CustomComponent s - 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.[/color]