"Elementary, my dear Watson!"
I sell apples and organic apple juices over my site and deliver them to my customers. I would like to track down everything. And the problem is, I am not quite sure in what table should I put some fields. Data modeling isn't my stronger side.
This is how I put things, so bare with me and if you can, help me!
I need these information from users:
Name, Last Name, Username, Address, City, ZIP, email, cell phone, telephone, date of registration
This is pretty obvious, so I made a table USERS with these fields + UserID which is primary key for relationships. Right?
Next thing is my own products. This is a little bit tricky. I sell apples in boxes of 10kg. Currently I have three apple sorts I offer. Beside that, I have apple juices bag in box 5L two types of juices. Here, I don't know how should I form a table.
- I can have more apple sorts. 3 is just beginning
- I could have other packaging except 10kg ones. 5kg, 3kg, 20kg,
- Apple juice currently comes in 0,2 Liters and 5 liters. Currently in two flavors, but more to come soon.
- and price is not constant
And for orders, I need the following:
OrderID, Order number, Which user ordered, order date, delivery date, which product
In the end, I want to take the following info out:
- how many kilos of apple is sold
- how many boxes of apple is sold
- which apple sort is most sold
- how many bag in box 5 liters are sold
- how many 0,2 liters juices are sold
- summary - how many liters is sold
- summary - how much have I earned
- and this list goes on...
Every information is important to me, so I would like someone to help me with constructing tables or at least, helping me understand the relationships.
Please forgive me if something isn't clear because english is not my primary language.
Thank you in advance!