473,324 Members | 2,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Multiple product classes table design

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
2 2057
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Billy Jacobs | last post by:
I am using a datagrid to display some data. I need to create 2 header rows for this grid with columns of varying spans. In html it would be the following. <Table> <tr> <td colspan=8>Official...
4
by: anonymous | last post by:
When I use the schema collection to apply many schemas to one XML instance document, I get an error if I do not qualify every element with the appropriate namespace. Both the W3C site and this...
3
by: Robin S. | last post by:
I tried to ask this question several days ago, but I didn't explain my application correctly. Basically I want to have one record from table "A" and I want to display, say, 5 records from table...
22
by: Matthew Louden | last post by:
I want to know why C# doesnt support multiple inheritance? But why we can inherit multiple interfaces instead? I know this is the rule, but I dont understand why. Can anyone give me some concrete...
29
by: MAHESH MANDHARE | last post by:
Hi , Can Anyone explain me exactly why multiple inheritance not used in java and c# thanks, Mahesh -- Have A Good Day, Mahesh, Maheshmandhare@yahoo.co.in
3
by: M.L. Abram | last post by:
Hello all, I do not know if this question is regarding table design, queries, or programming. Below, I have given a table design using Access 2003. Fields 'Product' and 'Color' are primary keys...
4
by: beatdream | last post by:
I am designing a database to handle different kinds of products ... and these products can have different properties...for example, a trouser can be specified by the width, length, color, and other...
5
by: MadDiver | last post by:
Guys, I need to create an application that handles several product types/ categories. Each product type can have totally different fields to describe it. For instance a car would have Year,...
482
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.