mysql - how can I constrain a foreign key relationship where it may refer to multiple other tables? -

i have table called inventory_movements , , i'm planing save products movements in , out warehouse , 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 want store reference of movement (the cause of movement) whither order id , or purchase id , purchase return id, .... etc

but want make constrain on field make sure no invalid data (e.g. not exist purchase) stored in database, of curse can't make 1 foreign key references many tables (sales, purchases, purchase returns , ...etc)

a bad solution add column every reference type (sell id, purchase id, sales return id,etc.. ) , fill right 1 in each movement , let others null , of curse against normalization , can't add more reference later.

what can in situation ?

please consider i'm newbie, thanks

you have few approaches. 1 have 1 foreign key per table type constraint ensures 1 not null. agree clunky people prefer (david fetter, example, has blogged benefits of approach).

another approach factor out common parts of referenced tables single, referenced table. if cannot this, can have trigger-maintained table instead. mean like:

1. a transaction documents table
2. a table sales/purchase data (or maybe different tables this).

if cannot done have table stores ids, relevant tables, , id reference purpose, , maintained trigger, have referring constraint there.

either way, long-run going end second solution (a master transaction journal, , other tables extend it).

(original design question answer below.

depending on how want address can see 1 of 2 ways of doing it.

the first use basic convention of positive numbers coming in , negative numbers going out. works global movements (purchases , sales) breaks down local movements (moving between warehouses).

one option here have separate "states" table represents both global , local states. example, purchases, sales, different warehouses, etc. represent transfer graph link between state. can have documents table can represent purchases , sales, appropriate classifictions etc. allows three-way relationship between in-state, out-state, , document. example sale have in-state inventory (or particular warehouse), out-state of sale, , document of sales invoice.

of course can both, storing global inventory in 1 way , warehouse movements in other.