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!
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-id(primary-key)
company-name
phone
(this is for store product company information.)
category-id(primary-key)
category-name
(this is for store product category)
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.)
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-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.)
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_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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With