Data model inventory Typeopearation movements
I have a doubt in my data model in the table movements, in that table I have an affected field, which is going to relate to sales, purchases, promotions, prize, donation, move stores, etc.
In the Tipooperation table is where these operations I just mentioned will go.
How can I control the referential integrity of AfectadoId that is in motion?, would there be another modeling solution for this case?
Could be another option this modeling.
In the table Operaciones register the ID afectado in which I indicate the type of operation, using TipoOpearacionId and would not be highlighted with Compras, Ventas, Promocion, etc. referential integrity could be controlled by a Triggers.
1 answers
Don't follow that path because it's a bad idea
In the moves table define two fields VentaId and CompraId (this of course will allow null)
Then by inserting the moving record you assign VentaId in case of being a sale or to CompraId in case of being a purchase, leaving the one that does not correspond in null
Cannot relate a single field to two different tables