webguy262 wrote:I have a list of ~300 items. They are each a row in one table with part #, description, etc. columns.
I also have a list of locations that stock the item.
Each location has this same array of items but the inventory level is unique to that location.
How do I configure the database/tables so that I can get the inventory level of each item by each location?
Do I need a table with ~300 columns and rows for each location?
That would be precisely the wrong way to do it.
Make a table for locations that stores.
Make a table for items.
Make an intermediate join table to join locations to items.
Like this, sorta:
create table location (locid int primary key, storename text);
create table item (itemid int primary key, itemdesc text);
create table loc_to_items (locid int references location(locid), itemid int references item(itemid), qty int);
insert into location values (1,'store a');
insert into location values (2,'store b');
insert into item values (1,'bic lighter');
insert into item values (2,'grade a widgets');
insert into loc_to_items values (1,1,15);
insert into loc_to_items values (1,2,10);
insert into loc_to_items values (2,2,20);
insert into loc_to_items values (2,1,2);
select l.locid, l.storename, i.itemid, i.itemdesc from location l join loc_to_items li on (l.locid=li.locid) join item i on (li.itemid=i.itemid);
