2

Lets say I have a table with information about all the products I am selling and a table with information for my seven stores, each store has its own id. Now I want to crate a table which tells me how much of a certain product I have in stock in total and how much of it do I have in each store. I am thinking of having every store be a column in the new table. What is a clever way to do that? Is there a better way to deal with it? And can I have a table with no primary key?

enter image description here

Wilson Vargas
  • 2,841
  • 1
  • 19
  • 28
waterdrinker
  • 21
  • 3
  • 2
    **YIKES** if ever you see [name]1, [name]2, ... [name]n run for the hills! This IS NOT normalized and will be a pain in the ... ever after. What happens when stores 5 & 6 merge so that you can open 3 new stores? Keep normalizing the model. Also, do prices change over time? or could the price differ between stores? – Paul Maxwell Oct 14 '17 at 01:31
  • I have no idea how to do the normalization in this case. That is what I am asking. How can I reduce number of columns without having multiple entrees for a single product? – waterdrinker Oct 14 '17 at 01:49
  • There are many references to normalization to research, and there already is a relevant answer below. You have many products and have several stores, each store has many of the products. So at some point there has to be *multiple references* to a product (but you only need one master record to define a product). I was stressing a data model symptom that clearly indicates a need for more normalization. – Paul Maxwell Oct 14 '17 at 01:53
  • @Used_By_Already & waterdrinker Such multiple columnity is typically bad design, but it has nothing to do with ["normalization"](https://stackoverflow.com/a/40640962/3404097) either in the sense of higher NFs or the (confused & fuzzy) sense of "atomic"-only values. The notion that there is something wrong with "multiple entries" is also [misconceived](https://stackoverflow.com/a/44539858/3404097). – philipxy Oct 14 '17 at 05:39

1 Answers1

4

Stock table, where you should have:

  • ProductId (FK)

  • StoreId (FK)

  • Stock

If you want to have total stock for stores - create a view with GROUP BY. For PK you can use the complex as ProductId + StoreId or create an artificial one. Primary Key is almost always highly recommended.

Kamil Nowinski
  • 486
  • 3
  • 9