How to design great database structure for POS system

Question

I am designing mysql database for my POS system. I will explain it as my best. Please kindly help me to design it more successfully!

INVENTORY

Id
bar-code(Primary key)
product-name
company-id
category-id

(This table for store product information. This is not useful for day to day selling process. That for store product information.if product add to database, first store in that table. after that STOCK table getting information from this table)

COMPANY

company-id(primary-key)
company-name
phone

(this is for store product company information.)

CATEGORY

category-id(primary-key)
category-name

(this is for store product category)

STOCK

id
bar-code(primary-key)
get-price
sell-price-bulk
sell-price-retail
quantity

(that table is for day to day use. use for calculate current stock and all of process. mainly getting information from INVENTORY table.)

LAST_STOCK_DATE

bar-code(primary-key)
date/time
quantity

(that table handle every stock recode's. That use full for seller to view how many times stock products and dates. As well as can calculate full quantity per year etc...)

BILL

bill-no(primary-key)
date/time
customer-id
normal-price ($100 etc...)
discount-price ($99 etc...)

(that is for store bill information. my main problem is here. I can not think creatively in that point. So i want your help to design that BILL table and SOLD_ITEM table. in that time i am thinking about another table "SOLD_ITEM" for reduce the data repeats.)

SOLD_ITEM

bill-no(primary-key)
bar-code
quantity (2 etc...)
per-item-price ($10 etc...)
per-item-discount-price ($2 etc...)
total-discount ($4 etc...)
credit-or-debit

(if anything wrong with BILL and BILL_ITEM please comment. i want to fix that part. this two tables look like this!)

---

BILL
bill-no  date/time  customer-id  normal-price  discount-price
B1       15/11/01   C1                 $30                 $22
B2       15/11/01   C2                 $30                 $18

---

BILL_ITEM
bill-no  bar-code  quantity  per-item-price  per-item-discount-price  total-discount  credit-or-debit
B1       1111          2            $10                   $2                                    $4                   debit
B1       2222         4            $20                   $1                                    $4                   debit
B2       3333         5            $10                   $2                                    $10                 debit
B2       4444         2            $20                   $1                                    $2                   debit

basically i want to reduce the repeat of my bill table. Please look it and tell me is my way correct or need to change. if it need to change, please comment.

BILL_ITEM table always filling sell by sell.....

Thank you.

Dogger

Answer 1

There are very few reasons to ever have multiple tables with the same primary key - last_stock_date and stock can be merged or include date/time in the key.

What you do about this depends on your business rules - as shown, the BILL_ITEM data matches the sold_item table definition: you don't need 2 tables for this. A data analyst would ask questions like:
1) Do you (or will you ever) want to support customer credit accounts ?
If so, bill_no probably wouldn't be known at the time of a sale so you'd need it to allow nulls on the sold_item table. (You'd need a generated - column as an artificial key instead.
If not, you probably need a line_number column (standard master-detail construct) - you otherwise are missing a unique identifier.

Cecilius