CODEWITHSUNDEEP
databasesqlite
Lorin
Lorin

Reputation: 341

Database: Design customer orders

I am currently creating an Android application which should use SQLite database. In my case, I need to strore "product type", "bill" and "list of all purchased products"

Bill:

id
customer
data -> link to list of all purchased products
price

Product types:

id
name
price

My question is: One customer could have more products connected to one bill. How to design it? Is using a Use a one row for every bills:

id
bill_id
product

or rather insert more products into one row and then parse it in my application? I suppose, there is a better solution that these mine. Thank you for help.

Upvotes: 0

Views: 1536

Answers (2)

Sapna Prajapati
Sapna Prajapati

Reputation: 61

To design customer order with bill we can design it as below:

This schema having basics attributes we can add more as per requirements.

Customers Table

id PK

full_name

address

phone

MenuItems Table

id PK

item_name

item_description

item_price

Order Table

id PK

customer_id FK

order_date

OrderDetails Table

id PK

order_id FK

menu_item_id fk

quantity

Bill Table

id PK

order_id FK

amount

bill_date

Upvotes: 0

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51445

You said in your question, "One customer could have more products connected to one bill."

Another way of saying this is, one customer can have 0 or more bills, and one bill can have 1 or more products.

After rephrasing your statement, the database normalization becomes more obvious.

Customer
--------
Customer ID
Customer Name
...

Bill
----
Bill ID
Customer ID
Bill Time Stamp

Bill-Product
------------
Bill ID
Product ID

Product
-------
Product ID
Product Name
Product Price
...

Upvotes: 3

Related Questions