I need some advice to structure a database. I have one type called “Orders” and another type called “Products”. What I’d like to have is for each thing in Orders, I’d like it to reference a Product thing but also a qty for that product. In essence, I’d like for each Order thing to hold a product and a qty for that product. But the same Order could hold more than one products (items). So one order record/thing could have Product A, 100gms; Product B, 50gms, etc.
How would I structure my database for this? Because I thought that the Orders type would have a field called Products, which would be of list type, but then I’d even need to store the qty, so kind of a like a 2-D array within the field.
Also, perhaps I’m not thinking of this the right way to begin with? i.e. each order thing to hold more than one products so any advice/guidance is much appreciated.
You’ll need a third datatype, to connect the product to the order, along with quantity (and any other data).
So you’ll want 3 datatypes:
Product
Order
Ordered-Product (or Order Item)
The order will contain ‘Ordered Products’.
The Ordered Product will contain a Product and a Quantity.
Definitely check out the post @boston85719 linked to above for some great resources for more info on database design and structure (as setting your database up correctly from the start will save you a lot of hassle in the long run). I’ve just looked at it and it’s got some great info and examples for DB setup.
