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.