Table design advice - products in location

Databases
1

I have a table containing 88 locations - 88 rows and 2 columns = ID & location name
I have a table containing 11 products - 11 rows and 3 columns = ID, product code & product full name

Both these tables are used for other queries and as I am only starting on the site I could add more columns if required.

What I want now is to know how many products are in each location and am not sure of the best way to go about it.

Not all locations have all the products and I could create a table with a location ID column, products ID column and qty column. This table will still be quite large and I keep thinking there must be a better/more efficent way to do it.

Any suggestions would be gratefuly recived.

2

If a product can be in multiple locations, and a location can have multiple products, then the table you described is exactly the right way to store that information. There is no more compact way – there’s no duplication of data there, so nothing you could eliminate with another design.

3

that actually is the best way to do it

at most you are looking at a few hundred rows – a trivially small table

4

Thanks for the coments; I will carry on with the method I outlined.