hi friends, my problem is this, I would like to know what is the best option for my next problem?
Currently in my database, I have a table that refers to the products I have in a warehouse, but what I want is to have many wineries and I do not know which option would be better:
oh some better option that comes to mind.
Which option do you suggest?
you only need a table of bodegas
example:
------------------------
BODEGAS
------------------------
codigo_bodega
nombre de bodega
etc...
------------------------
PRODUCTOS
------------------------
codigo
codigo_bodega
nombre_producto
etc...
make the relation in products with the winery in codigo_bodega cascade the modifications.
Although it seems that the "1 table per hold" model is more scalable, it actually entails a deeper problem.
With the model "1 table for many wineries" you only need an additional field "bodega_id", and a winery table with its ID. Between them, a foreign key ensures that there are no records without a warehouse, and that eliminating a warehouse eliminates its contents.
With the model of many tables, you have to store in which table goes each hold. This means that a field represents a table . That relationship of entities does not exist. It would be like doing an FK to the information_schema of the BBDD. There are no database engines where you can make this reference , which means that your database can not have referential integrity between your entities.
You will have to write the code, in your business layer, to create and drop tables according to the insertions and deletions in the winery table, and that is never as good as using the built-in behavior of a relational engine.
PS: In the PostgreSQL engine you can make the field that contains the name of the table of each warehouse change if you rename the table, using the data type RegClass but that is like all the help that is going to you give the engine to do the schema you're thinking.