Database schema for products, which can have multiple locations and different quantities-CodePudding

I want to make a database relationship. That should have a table Product which can be in multiple locations with different quantities. I created something like this, but I'm not sure if this is a very good or even a solution. So for example I do have multiple chairs and in total I have 20 of them. Now those chairs are splitted up in 2 different locations each 10 pieces.

For any who wants a representation:

Table Type {
  t_key int [pk]
  name varchar
 }

Table Product{
  p_key int [pk]
  description varchar
  size int
  total_stock int
  storage_stock int
  price_per_unit int
  total_price int
  shop varchar
  type_key int [ref: > Type.t_key]
  quantity_key int [ref: > Quantity.q_key]
}

Table Quantity{
  q_key int [pk]
  quantity int
}

Table Location{
  l_key int [pk]
  name varchar
  quantity_key int [ref: > Quantity.q_key]
}

CodePudding user response：

You need a m-to-n relation and must therefore include a product Id and a location Id in the quantity table which acts as a junction table.

Table Quantity{
  p_key int [pk]  -- Product
  l_key int [pk]  -- Location
  quantity int
}

 Table Product                                                 Table Location
┌──────────────────┐ 1                                      1 ┌──────────────────┐
│ PK p_key         │◄────┐        Table Quantity        ┌────►│ PK l_key         │
├──────────────────┤     │       ┌────────────────┐     │     ├──────────────────┤
│ description      │     └───────┤ PK p_key       │     │     │    name          │
│ ...              │           n │ PK l_key       ├─────┘     └──────────────────┘
│                  │             ├────────────────┤ n
│                  │             │    quantity    │
│                  │             └────────────────┘
└──────────────────┘

This allows you to have different quantities of the same product in distinct locations.

The primary key (p_key, l_key) in the Quantity table ensures the uniqueness of the quantity per product and location.

A different way to look at it is to focus on the 1-to-n relationship between Product and Quantity. Here you consider Product and Quantity to be fact tables, whereas Location is a merely a lookup table. The latter table is only used to look up the l_key making the m-to-n relationship an accidental side effect.

In your design, quantity_key has no place in the Location table, because this would require having a new location for every quantity. This table must store only locations. Nothing else.