I am currently working on a project that we have multiple items in multiple locations.

Assume that the items are labeled A, B, C, D and have multiple QTYs associated with them.

We then get a table that looks like



The problem is that we need to add an additional field for the item's location.

Currently I am doing SELECT QTYs FROM table Where Item = "A"; and I get the exact data I need.

Item A however could be in multiple places at the same time, which creates a table like

This would cause me to get multiple rows returned for the same query as above.

The combination of the value in Item A and WAREHOUSE# would always be unique, however the item could possibly be listed multiple times.

Is there a way to create a table where the combination of Item Letter and Warehouse # would let me directly find the information I am wanting and ensure that there are no duplicated combinations of Item Letter and Warehouse Number?

Is there a better way to design? It will be accessed constantly.
We will sometimes be wanting the QTYs that are available across all warehouses and sometimes only the QTYs in one house. The number of warehouses and the number of items will be variable so separate table for each warehouse is not possible.

So you want the sum of all item A? So the result would be:
A 12 6 11?

Select Item, sum(QTY1), sum(QTY2), sum(QTY3) from TABLE where Item='A' group by Item

I implemented this very quickly and seems to accomplish the task. And it is a viable design, I am more concerned of this being optimal / the correct way to do it.

It seems that each item would have multiple locations and this table could potentially grow at a very very fast rate.

Without knowing how the rest of the db is structured, I don't really see a better way to do it. Unless each warehouse number or item has other fields associated with it, like i dunno, address, category, item type, etc. The goal of db normalization is to minimize redundancy. But since each combination of Item,Warehouse is unique, the table is not redundant.

My instinct says that the underlying design should be checked. I have to admit I don't really understand the abstract table.

Item A is made of 5xQTY1, 3xQTY2 and 8xQTY3? And then there can be another Item A with different quantities and in a different location?

So basically we'd want Item A to be an instance of some class A that can have what I'd call "variations"?

So if Item A would for example be "Some School", QTY1 could be "# of teachers", QTY2 could be "# of students" and QTY3 could be "# of classrooms" or something like that?
And then "warehouse" could be something like "campus"
Just trying to make up some example that doesn't expose your business logic because I don't like thinking too abstractly.

Each item has a ton of fields as does each warehouse that is associated with it.

An item can have description / prices etc but I believe those are all constant throughout each warehouse. Example

Items Table
Warehouse Table
Quantity Table
I believe I can still do something like
Select Quantity.Q1, Warehouse.Address LEFT JOIN Warehouse Table ON Quantity.WareNum = Warehouse.WarehouseNum Where Item = A which would solve all my problems I believe.

I believe thats the same logic.
Lets say we have feet of tape. We need 100 feet. We have 76 in warehouse A and 46 in warehouse B. We need to know how to get that 100 feet out of the warehouses.

I believe splahpots design / SQL sum() is sufficient. I have never taken a database design class so I am some what clueless. We have had enough issues cause by the rest of our database being designed somewhat haphazardly (not by me) that I wanted more input than my own.

That looks right then. A table for Items, Warehouses, and Quantities. Why are you using LEFT JOIN instead of INNER JOIN? Left Join will return all rows in the left table even if there are no matches in the right table.

I didn't think it through too thoroughly I suppose. I believe you are correct. Although I believe in my implementation they are the same.

Is there a way to force the combination of inventory number and warehouse number to be unique to avoid having the same thing listed twice?

Create a multi-field constraint?

I think you can do something like this when you create the table. (I work in Microsoft SQL Server)

create table Quantities(
Item varchar(500),
QTY1 int,
QTY2 int,
QTY3 int,
Warehouse int,
unique(Item, Warehouse)
)

Not 100% sure about this.

You are in fact correct it seems. Thanks for your help I am working on some coding of everything and more technical details.

Can you give a real world example of what the difference is between Q1, Q2, Q3 in the same warehouse for 'Item A'?

Also, for the code above, you should absolutely be using an integer ID for any fields that are relationships between tables.



I'm guessing the real design should probably look something like:

Items:
ItemID (int)
Price (currency)
Description (varchar)

SubItems:
SubItemID (int)
Whatever fields describe the subtypes, color?
fk_ItemID (int)
(currency could easily move to this table if you want to be able to price by subitem)

Warehouses:
WarehouseID (int)
Address (varchar)
Contact (varchar)

Inventory:
Quantity (int/float/whatever)
fk_SubItemID (int)
fk_WarehouseID (int)
Unique constraint/PK on fk_SubItemID, fk_WarehouseID

If there's no other way to break down why you have 3 qtys, then you can't do any better than slapping an item+warehouse primary key on what you have as others have said.

I disagree.

I don't know what Q1, Q2, and Q3 represent, but I think most typically the data would be structured as.

item #, warehouse #, qty # (key)
QTY.

Instead of this:
Item QTY1 QTY2 QTY3 WAREHOUSE#
A 5 3 8 1

This:
Item, Warehouse, Qty#, Qty
A 1 1 5
A 1 2 3
A 1 3 8

That's a good way to do it if there's potential for other qty dependent fields. For example, if he wants a "last time modified" field for a qty, he would have to have 3 "last time modified" fields for each of the 3 qtys, which would be bad.