database - SQL complex contraints -

i'm working on little database project , have problem organizing complex constraints data schema.

here use bodyparttag describe accepted layertags in instance of bodypart. problem if i'm going build bodypart entity have no direct constraints associated bodyparttag. can create bodypart called 'red eyes' bodyparttag 'eyes' can attach bodypart layer if 'eyes' bodyparttag accepts 'left eye', 'right eye' layertags.

how reorganize tables add these constraints? @ moment use 'programatic' solution requires coding seems not correct kind of tasks.

details: white dot 1 , black dot many.

you can composite keys rather surrogate keys. it's not necessary great way it, if working schema provide data constraint specified.

firstly, add 'tag' keys primary keys of layer , bodypart tables, primary key composed of 2 values, i.e.

layer table:    pk_layer (id, layertagid) bodypart table: pk_bodypart (id, bodyparttagid) 

then create bodypartlayer table using combined keys (2 values each), rather current key (single value). bodypartlayer table becomes 4 value table columns

-- 2 'bodypart' values have foreign key `bodypart` table,  bodypartid    bodyparttagid  -- , 2 'layer' values have foreign key `layer` table. layerid       layertagid    

finally, create foreign key constraint bodypartlayer table bodypartschema table. key like

bodypartlayer table: fk_bodypartlayer_bodypartschema (bodyparttagid, layertagid) 

this ensure bodypart/layer combinations can linked when respective tags linked. not particularly pretty, achieve requirement.