SQL Server, Like Part Numbers in Different Warehouses

[Papa_Don]

Group,

I'm discovering that the SQL Server tables don't like identical (in this case) Part Numbers, even though one of the columns will have different information in it (in this instance, it is the "Location"). FYI.... I'm using SQL Server 2008 Express for my testing.

With this said, I'm trying to develope a inventory database table to hold the part number, description and other pertinent information for a company that has multiple warehouse locations. For obvious reasons, every location will have the same items available, which means duplicate part numbers.

Here are my questions:

1) Does this mean I need to create a different table for every location (this doesn't seem wise as I don't know how many location will exist as the company grows in the future)?

2) Can I create a data table to "define" the warehouses, the ID numbers, etc.?

3) if so, where do I store the dynamic (changing) data for each part number/location for things like selling price, number of units sold, total value sold, etc.?

4) Must I break apart the existing INVENTORY data table and create "sub-tables" the would house the dynamic (potentially changing) data?

5) or is the simple "fix" to this setting the Primary Key on the "Location" column of the INVENTORY data table?

I apologize for asking what is probably a very simple question. I'm new to programming and I'm even newer to the world of datatables. Therefore I appreciate your insight, knowledge and opinions.

Don

[gusano79]

Are you able to post your current schema definition? Having that kind of specific information will help.

Meanwhile...

1) Does this mean I need to create a different table for every location (this doesn't seem wise as I don't know how many location will exist as the company grows in the future)?

Your instinct is correct; you don't want multiple tables for locations. How would you write a query in a database like that without having to revise and rebuild your application every time you wanted to add or remove a location?

2) Can I create a data table to "define" the warehouses, the ID numbers, etc.?

Yes, that's better. A table that represents the various locations parts could be stored. Then your inventory can just refer to that table to indicate where a part is.

3) if so, where do I store the dynamic (changing) data for each part number/location for things like selling price, number of units sold, total value sold, etc.?

In general, keep information about an object with the object it's about. Examples:

• Selling price is probably about the part itself, for a simple application.
• Number of units sold is about a specific order.
• Total value sold is about an order. You could store this with the order, but there's no need to actually save this value anywhere--you can always calculate it for any order by adding up the prices.

4) Must I break apart the existing INVENTORY data table and create "sub-tables" the would house the dynamic (potentially changing) data?

From what you've told us, you probably should. Show us the table definition and we'll help you identify what should split out.

5) or is the simple "fix" to this setting the Primary Key on the "Location" column of the INVENTORY data table?

You could play games with keys, but that's about the same thing as sweeping the issue under the rug and pretending it's not there--it will cause you problems down the road, and certainly wouldn't be appropriate for any real production application.

In my experience, it's very important to get your data model right, especially for data-driven business applications. If it's not, the rest of your application will suffer no matter how good you are.

[adam_k]

In a typical WMS things are like this:
Order info goes to order tables, header and details. In details you probably need 2 qties, 1 ordered and 1 remaining.
Price info goes to item master, together with part number, description, product category (Finished goods, raw material, packaging supply), lot and expiration date requirements, possibly owner if dealing with a 3PL warehouse, inventory unit (the unit that the quantities are expressed in) and possibly the rules for storing and picking it and hazardous classification.
Item master is usually accompanied by a couple other tables, that don't have part number as single identity field, as they are required to repeat it a couple times. These would be the UOMs (units of measure) and you would store there qty per box, ctn, pallet or other containers like drums. With that you probably store the dimensions for each UOM and sometimes the UPC assosiated with it.
The other table would be the UPCs table. You might not find it everywhere, especially if the system has the UPC associated with a particular level.

You'd also have a locations table, where you store info about your storage locations and their status, capacity and how you want them to react to the pick and putaway rules (picking path, machinery requyired,etc). If your warehouse is zoned to certain uoms per area, then you need to add this info here.

Finally you've got the stored items table, where you store location, part number, quantity, UOM (for zoned warehouses), owner, status and sometimes order_number the goods belong to - after picking or "Free" before picking. Almost forgot Lot number, expiration date and fifo date.
Usually the key for this table would be location, part number and lot number and sometimes order number.

If you really ment the different warehouses - instead of different locations in the same warehouse, you need to separate that in locations table and probably in the stored items one - so you don't have to join to locations all the time.

Hope this helps.

[Papa_Don]

To clarify some things, by "Location" I did mean "Warehouse Location" which is expressed as a number. I did play a bit more and learned I could duplicate part numbers if I assigned an ID_Key that could also equate to the warehouse location. As part of the item record (or part number), "Bin-Location"(s) are also included such that the item can be assigned to a specific spot within the warehouse.

The database Schema is as follows:

Name Policy Health State
INVENTORY_KEY (PK, nchar(32), not null)
INV-PART-NUMBER (nchar(32), not null)
INV-LOCATION (numeric(3,0), not null)
INV-DIVISION (numeric(3,0), not null)
INV-VENDOR-PART-NUMBER (nchar(32), not null)
INV-DESCRIPTION (nvarchar(100), not null)
INV-VENDOR-NUMBER (numeric(10,0), null)
INV-ALT-VENDOR-NUMBER (numeric(10,0), null)
INV-CATEGORY-CLASS (nchar(4), not null)
INV-PRICE-CAT-CLASS (nchar(4), not null)
INV-SALES-CAT-CLASS (nchar(4), not null)
INV-MISC-CAT-CLASS (nchar(4), null)
INV-CATEGORY (nchar(2), not null)
INV-CLASS (nchar(2), not null)
INV-CLASSIFICATION (nchar(1), not null)
INV-DEPOSIT-REQUIREMENT-FLAG (nchar(1), not null)
INV-DEPOSIT-PCNT-REQUIREMENT (numeric(4,1), null)
INV-COMMISSION-PCNT (numeric(4,1), null)
INV-UNIT-MEASURE (nchar(4), not null)
INV-UM-BREAK-1-NAME (nchar(4), null)
INV-UM-BREAK-1-QTY (numeric(14,4), null)
INV-UM-BREAK-2-NAME (nchar(4), null)
INV-UM-BREAK-2-QTY (numeric(14,4), null)
INV-UM-BREAK-3-NAME (nchar(4), null)
INV-UM-BREAK-3-QTY (numeric(14,4), null)
INV-UM-BREAK-4-NAME (nchar(4), null)
INV-UM-BREAK-4-QTY (numeric(14,4), null)
INV-UM-BREAK-5-NAME (nchar(4), null)
INV-UM-BREAK-5-QTY (numeric(14,4), null)
INV-WEIGHT (numeric(14,4), null)
INV-WEIGHT-NAME (nchar(4), null)
INV-CURRENT-SELL-PRICE-DATE (numeric(8,0), null)
INV-SELL-PRICE-1 (numeric(12,2), null)
INV-SELL-PRICE-2 (numeric(12,2), null)
INV-SELL-PRICE-3 (numeric(12,2), null)
INV-SELL-PRICE-4 (numeric(12,2), null)
INV-SELL-PRICE-5 (numeric(12,2), null)
INV-DATE-SETUP (numeric(8,0), null)
INV-DELETE-DATE (numeric(8,0), null)
INV-USE-AVERAGE-COST (nchar(1), null)
INV-COST (numeric(14,4), null)
INV-COST-DATE (numeric(8,0), null)
INV-FREIGHT-COST (numeric(14,4), null)
INV-MISC-COST-1 (numeric(14,4), null)
INV-MISC-COST-2 (numeric(14,4), null)
INV-NEW-COST (numeric(14,4), null)
INV-NEW-COST-DATE (numeric(8,0), null)
INV-NEW-SELL-PRICE-1 (numeric(12,2), null)
INV-NEW-SELL-PRICE-2 (numeric(12,2), null)
INV-NEW-SELL-PRICE-3 (numeric(12,2), null)
INV-NEW-SELL-PRICE-4 (numeric(12,2), null)
INV-NEW-SELL-PRICE-5 (numeric(12,2), null)
INV-NEW-SELL-PRICE-DATE (numeric(8,0), null)
INV-MINIMUM-STOCK-LEVEL (numeric(10,0), null)
INV-MAXIMUM-STOCK-LEVEL (numeric(10,0), null)
INV-MIN-MAX-FREEZE-DATE (numeric(8,0), null)
INV-SAFETY-STOCK (numeric(10,0), null)
INV-PRODUCT-CODE (numeric(2,0), null)
INV-PURCHASING-FLAG (numeric(1,0), null)
INV-BIN-LOCATION (nchar(15), null)
INV-ALT-BIN-LOC-1 (nchar(15), null)
INV-ALT-BIN-LOC-2 (nchar(15), null)
INV-ALT-BIN-LOC-3 (nchar(15), null)
INV-ALT-BIN-LOC-4 (nchar(15), null)
INV-COMPANY (nchar(3), null)
INV-LEAD-TIME-DAYS (numeric(3,0), null)
INV-EXT-DESCRIPTION (text, null)
INV-EXT-DESC-PRINT-ON-ORDER (nchar(1), null)
INV-EXT-DESCRIPTION-VENDOR (text, null)
INV-EXT-DESC-PRINT-ON-PO (nchar(1), null)
INV-HAZARDOUS-MATERIAL (nchar(1), null)
INV-KEYWORD (nchar(15), null)
INV-LOT-SHADE-INBOUND (nchar(1), null)
INV-LOT-SHADE-OUTBOUND (nchar(1), null)
INV-TAXABLE-ITEM (nchar(1), null)
INV-UPC-NUMBER (nchar(15), null)
INV-UPC-EXPANDED (numeric(1,0), null)
INV-MISC-FIELDS-1 (nvarchar(32), null)
INV-MISC-FIELDS-2 (nvarchar(32), null)
INV-MISC-FIELDS-3 (nvarchar(32), null)
INV-MISC-FIELDS-4 (nvarchar(32), null)
INV-MISC-FIELDS-5 (nvarchar(32), null)
INV-MISC-FIELDS-6 (nvarchar(32), null)
INV-MISC-FIELDS-7 (nvarchar(32), null)
INV-MISC-FIELDS-8 (nvarchar(32), null)
INV-MISC-FIELDS-9 (nvarchar(32), null)
INV-MISC-FIELDS-10 (nvarchar(32), null)
INV-MISC-FIELDS-11 (nvarchar(32), null)
INV-MISC-FIELDS-12 (nvarchar(32), null)
INV-MISC-FIELDS-13 (nvarchar(32), null)
INV-MISC-FIELDS-14 (nvarchar(32), null)
INV-MISC-FIELDS-15 (nvarchar(32), null)
INV-MISC-FIELDS-16 (nvarchar(32), null)
INV-MISC-FIELDS-17 (nvarchar(32), null)
INV-MISC-FIELDS-18 (nvarchar(32), null)
INV-MISC-FIELDS-19 (nvarchar(32), null)
INV-MISC-FIELDS-20 (nvarchar(32), null)
INV-MISC-FIELDS-21 (nvarchar(32), null)
INV-MISC-FIELDS-22 (nvarchar(32), null)
INV-MISC-FIELDS-23 (nvarchar(32), null)
INV-MISC-FIELDS-24 (nvarchar(32), null)
INV-MISC-FIELDS-25 (nvarchar(32), null)
INV-MISC-FIELDS-26 (nvarchar(32), null)
INV-MISC-FIELDS-27 (nvarchar(32), null)
INV-MISC-FIELDS-28 (nvarchar(32), null)
INV-MISC-FIELDS-29 (nvarchar(32), null)
INV-MISC-FIELDS-30 (nvarchar(32), null)
INV-AVERAGE-COST (numeric(14,4), null)
INV-BKO-DEMAND (numeric(14,4), null)
INV-DATE-LAST-SOLD (numeric(8,0), null)
INV-INTRANSIT (numeric(14,4), null)
INV-LAST-YEAR-SERVICE-PCNT (numeric(5,2), null)
INV-LAST-YEAR-UNITS-SOLD (numeric(14,4), null)
INV-LAST-CLASSIFICATION (nchar(1), null)
INV-LAST-COUNT-AMOUNT (numeric(14,4), null)
INV-LAST-COUNT-BY (nchar(32), null)
INV-LAST-COUNT-DATE (numeric(8,0), null)
INV-LAST-COUNT-ACCURACY-PCNT (numeric(5,2), null)
INV-LAST-COUNT-ADJUST-VALUE (numeric(16,2), null)
INV-LAST-COUNT-ADJUST-UNITS (numeric(14,4), null)
INV-LOST-SALES-UNITS (numeric(14,4), null)
INV-JAN-UNITS-SOLD (numeric(14,4), null)
INV-FEB-UNITS-SOLD (numeric(14,4), null)
INV-MAR-UNITS-SOLD (numeric(14,4), null)
INV-APR-UNITS-SOLD (numeric(14,4), null)
INV-MAY-UNITS-SOLD (numeric(14,4), null)
INV-JUN-UNITS-SOLD (numeric(14,4), null)
INV-JUL-UNITS-SOLD (numeric(14,4), null)
INV-AUG-UNITS-SOLD (numeric(14,4), null)
INV-SEP-UNITS-SOLD (numeric(14,4), null)
INV-OCT-UNITS-SOLD (numeric(14,4), null)
INV-NOV-UNITS-SOLD (numeric(14,4), null)
INV-DEC-UNITS-SOLD (numeric(14,4), null)
INV-MTD-COST (numeric(16,4), null)
INV-MTD-SALES (numeric(16,2), null)
INV-MTD-UNITS (numeric(14,4), null)
INV-MTD-SERVICE-PCNT (numeric(5,2), null)
INV-MTD-SERVICE-TIMES (numeric(10,0), null)
INV-ON-HAND (numeric(14,4), null)
INV-ON-ORDER (numeric(14,4), null)
INV-OPEN-ORDER-DMND (numeric(14,4), null)
INV-QTY-INTRANSIT (numeric(14,4), null)
INV-XFER-DEMAND (numeric(14,4), null)
INV-YTD-ADJUST-QTY (numeric(14,4), null)
INV-YTD-ADJUST-VALUE (numeric(12,2), null)
INV-YTD-ACCURACY-PCNT (numeric(5,2), null)
INV-YTD-TIMES-COUNTED (numeric(3,0), null)
INV-YTD-COST (numeric(16,4), null)
INV-YTD-SALES (numeric(16,2), null)
INV-YTD-UNITS (numeric(16,4), null)
INV-YTD-SERVICE-PCNT (numeric(5,2), null)
INV-YTD-SERVICE-TIMES (numeric(10,0), null)
INV-ZERO-MTD (numeric(2,0), null)

I have the ability to break this inventory table into sub-tables. However I am concern of too many. One of the challenges I need to plan for is a large number of part number records. The business I'm planning this for has 74,000 part numbers in each of their 10 different warehouse locations.

Thanks to each of you for you comments, thoughts and ideas. These are incredibly valuable.

Don

[adam_k]

Don,
In my opinion you need to break it down to smaller tables, not per warehouse but per info usage.
You don't want to search 30 misc fields and the qties sold all year when you are looking for a bin location.
This setup is too expensive (in resources and time) to have for 74000 part numbers in 10 locations (740,000 SKUs). Separate the item info, from the inventory info, from the sales info, from the sales statistics.

Also keep in mind that if you have to store an item in more than 4 bin locations, then you'll need to change both the table and your app. Use rows instead of columns for this kind of info.

[Papa_Don]

Adam,

For what it's worth, those 30 MISC fields are meant to be user defined and are HIGHLY unlikely to be used as a search involving quantities sold. It is meant to store data such as "color", "size", "Series Name", etc. The company uses these fields to aid in a online price lookup for their customers. As it is used today, the customer can seach by an individual part number (if they know it). If they don't, they have a series of dropdown boxes that starts with "Series Name". After they choose a series name, another dropdown box lists sizes and another allows a choice of color. After making their choices, the product number, the description, packaging info, pricing, weight, etc., comes back in the form of a tables.

If I understand, you're suggesting that I break this one table into three. The first table would include "static" info like part number, description, warehouse location, pricing, weight, etc. A second table would include the part number and/or the Item ID (as a link to the first table) and those fields that would change as the daily, monthly and yearly updates are done such as number of units sold, the value of those units sold, etc.

You mention the third being for sales statistics. Something I haven't mention that I've been pondering has to do with Lot/Serial/Shade. I come from a background in the ceramic tile and stone industry. One of the continuing problems in that realm has to do with keeping inventory quantities by the shade and/or mfg. run number. Those are critical as these alpha/numeric characters determine if one box of tile will match the next for shade color, sizing and texture. With this said, I would love to be able to find an exceptional way of recording that Lot/Serial/Shade "number" as it comes in and keep up with the quantity on-hand. As well, I'd like to record that same info as it's sold and adjust the inventory count as required. But in a years time, you could litteraly have 100 different shade/run numbers for one item. So I'm thinking the third table could be used to record and maintain inventory levels by shade.

Thoughts?

[adam_k]

I was considering this INV-JAN-UNITS-SOLD as sales statistics.

How do you store multiple shades in only 4 bin-locations?
Anyway, yes in my mind a separate table is used to hold inventory by item number, by lot number (batch/run/whatever number), by location.

Can I also say that giving access to your db on the internet, is a huge risk? You have described what seems a lot of work to populate with data. It would be terrible if somebody could wipe it all with a single statement. Of course you could restore a backup, but the on hand quantities per lot wouldn't be correct.
I would use a second db for the internet and push info in it. This way if somebody SQL injects into my db, it wouldn't matter as my data would be safe.

[Papa_Don]

Adam, I haven't got that far in my thought process. However, the company has always used a secure private network to handle the data communications. Thus the reason I haven't given a lot of thought to it. With that said, there will be logins and passwords that will confirm access to the database by the program.

With respect to bin-locations, you are correct. Given the amount of total inventory the company has, they could have more than 4 bin-locations. Again, it's making more sense to me to put bin-location as a column on a table so that it won't be limited. It could sit along side the lot/shade/serial numbers.

Keep your thoughts and ideas flowing. I really want this to help the company.

Don