I have a table called inventory_movements , and I'm planing to save the products movements in and out the warehouse , it has fields like
1- movement_id(PK)
2- product_id(FK)
3- quantity int
4- unit_price decimal
5- movement ENUM('in','out')
6- date datetime
7- ????????? (reference )(e.g. sell(out)- purchase(in)- fire loss(out)
- sales return (in) - purchase return (out)
my problem is that I want to store the reference of the movement (the cause of the movement) whither it is the order id , or purchase id , purchase return id, .... etc
but I also want to make a constrain on this field to make sure that no invalid data (e.g. not exist purchase) will be stored in the database, of curse I can't make one foreign key references many tables (sales, purchases, purchase returns , ...etc)
a very bad solution is to add column for every reference type (sell id, purchase id, sales return id,etc.. ) and fill the right one in each movement and let the others null , but this is of curse against normalization and I can't add any more reference later.
what can I do in this situation ?
please consider that I'm very newbie, thanks
You have a few approaches. One is to have one foreign key per table type with a constraint that ensures that exactly one is not null. I agree that is clunky but some people prefer it (David Fetter, for example, has blogged about the benefits of this approach).
Another approach is to factor out the common parts of the referenced tables into a single, easily referenced table. If you cannot do this, you can have a trigger-maintained table instead. That would mean something like:
If that cannot be done then you have another table which just stores the ids, relevant tables, and an id for reference purpose, and that is maintained with a trigger, then you have a referring constraint there.
Either way, long-run you are probably going to end up with the second solution (a master transaction journal, and then other tables that extend it).
(Original design question answer below.
Depending on how you want to address this I can see one of two ways of doing it.
The first is to use a basic convention of positive numbers coming in and negative numbers going out. This works for global movements (purchases and sales) but it breaks down for local movements (moving between warehouses).
One option here is to have a separate "states" table which represents both global and local states. For example, purchases, sales, different warehouses, etc. Then you represent the transfer as a graph link between the state. You can also have a documents table which can represent purchases and sales, with appropriate classifictions etc. This allows three-way relationship between an in-state, an out-state, and a document. For example a sale could have an in-state as inventory (or a particular warehouse), an out-state of sale, and a document of the sales invoice.
Of course you can do both, storing global inventory in one way and warehouse movements in the other.