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. 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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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,...
|
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.
...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |