-1
I want to create a database that has several customers and many orders per each customer and each order has several items in it. I was thinking of creating a table for each order and populate each order from an "items" table, but this solution seems cumbersome after you reach thousands of orders and will be pretty hard to query. Any suggestions? Your help is greatly appreciated.
edferda
Posted 2013-07-17T22:00:58.397
Reputation: 1
0
You can have table which keeps your customers. You can have table which keeps orders. You can have table which keeps items per order. Just link them correctly with foreign keys and it should work for you.
Table one has filed ID and field name.
Table two has field ID and field customer (which refers to ID of first table)
Table three has field ID, field ORDER (which refers to ID of second table) and field items (which can refer to ID of another table - ITEMS).
Not necessary it should be exactly like that - this is just general concept for you.
VL-80
Posted 2013-07-17T22:00:58.397
Reputation: 3 867
What is your database BTW? – VL-80 – 2013-07-17T22:19:35.177
@Nikolay I am planning to use sqlite, but since I just picked up database design(I am currently learning to program) I am playing around with Microsoft Access to get to know this stuff a little bit better. – edferda – 2013-07-17T22:27:40.727
Cool. MS Access should have wonderful graphic designer which will allow you to link your tables in graphical mode. You will see lines between tables showing how exactly your tables related to each other. In this video ... approximately on minute 3 and so on ... they show how to link tables together. I did not watch it, but I think it can give you good start.
– VL-80 – 2013-07-17T22:31:48.517Maybe you can have a table with an order number, customer id, items, quantity, and some identifier, and simply select all values with an order number? Sounds like one of those cases where cracking open a textbook would help. – Journeyman Geek – 2013-07-27T13:00:54.960