0

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.

Lorin
  • 343
  • 4
  • 15

2 Answers2

3

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
...
Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
0

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

Sapna Prajapati
  • 61
  • 6