updatable entities in the design of databases I have a question on Entity Relationship design.

It is a shop where products are kept in stock first and then go to the store. When the store has less than a certain amount of product, products that are in stock go to the store. How would you design these entities?

I thought two entities products_store and products_stock but I don't know if there is another, more practical way.

Thanks.
You can have two entities products_store and products_stock as depicted in your question. 

Other Options


  Incase you just need a track of which product is moved


I think of having single Products table with a column name it as In_stock (can be a bit field) where you change it to false, when the product is moved to store.

In_stock value options

1- InStock

0- InStore


  Incase you need a track of DateTime when the product is moved to store.


I think of having single Products table with one column called Move_DateTime (default value NULL) to update the column when the product is moved to store from stock.

Move_DateTime value options

NULL - InStock

DateTimevalue -InStore

In one possible scenario you can go for choosing both the columns to be added in the table.