Advice needed to structure database


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.


Might have a look at this post

Could also consider another data type of order items

1 Like

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:

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.

1 Like

Thank you. I’ll definitely check out the post and reference articles. Appreciate the help.

Thank you @adamhholmes This helps clarify things.

You could also have Type - Product with a field Qty in it.

That would be more for an idea of inventory tracking rather than order tracking.

I see. Thanks dude.