How to structure this table

[JaeBrett]

I need to create an Access table that stores invoice information. The easy fields to setup are as follows:

invoiceid
customerid
date
total
discount

But I struggle with the field(s) to use for items purchased. Associated with the items are price and sku code. I have a table for items but that changes as often as the user wants so I can't really tie them together, as an item may not be there tomorrow.

Any help is appreciated.

<-- JaeTech Solutions -->

 

[Golom]

In most such systems, (e.g. Point-of-Sale, Order Entry, etc.) you capture the complete information about the item in the detail records for the sale or order. The reason for this is, as you have discovered, information in the main stock table can change but sale or order information is historical and reflects the situation at the time that the sale or order was processed. As a short list (depending on your application) you will need

[li]OrderID[/li] (PK)
[li]Order Line Number[/li] (PK)
[li]Stock Code[/li]
[li]UPC Code[/li]
[li]Description[/li]
[li]Quantity[/li]
[li]Price[/li]
[li]Taxes[/li] (May be an entry in a separate table for multiple taxes)

There are several other possible inclusions if you support multiple unit pricing, kit or combo sales, etc., but these are the basics.

 

[sritzel]

In the items table, I would not delete the items, I would create a Yes/No field called Deleted. When the users wants to look at all the items, I would just use a query that showes all active items. When a user wants to delete an item, just mark the Deleted field.

 

[JaeBrett]

Golom,
Thanks ... I do support multiple unit pricing, so how could I structure that?

sritzel, that would work too. Thanks

<-- JaeTech Solutions -->

 

[Golom]

JaeBrett
Re: I do support multiple unit pricing, so how could I structure that?

There's no fixed answer. In one of my systems I support 6 different kinds of multiple unit pricing so my structure is extremely elaborate. Here are a few pointers however.

If your pricing structure results in a price / unit that is just different than the regular price then I would include two price fields called Price and RegularPrice. The Amount for the line is just Quantity * Price and the price reduction is Quantity * (RegularPrice - Price). If you are being more exotic about the pricing thing (for example, "3 for $10", or "1st one for 2.99 and Regular Price after that") then your solution is going to require another table or tables to properly represent the pricing structure for the item.

 

[Golom]

sritzel

Deleting is only part of the problem. Suppose for example you had
[tt]
StockCode Description Price
1234 Blue Jacket 25.99
[/tt]
and the user changed that to
[tt]
StockCode Description Price
1234 Pink Bicycle 327.44
[/tt]
He has re-used the stock code for a completely different item and, if you link your historical sale line to this record, you have suddenly (and incorrectly) changed what was sold and for how much.

 

[OhioSteve]

Here are my thoughts on this thread...

1) There are many great "run a store" db packages available. The first question you should ask is "Do I really want to reinvent the wheel?" Maybe you have a good answer to that question, but you should ask it.

2) A db textbook would tell you that the value "total" should not be in a table. It is a calculated field. I'm not saying that you must follow textbook normalization rules, but you should understand that a "total" field breaks them. The textbook solution would be to have a tables such as sales, inventory, and sales details. The interactions among those tables would produce calculated values like "total".

3) If you want alot of info about how to do discounts, we need alot of info about your discounts.