Enforcing implicit relationships in SQL -

Enforcing implicit relationships in SQL -

i'm having hard time figuring out if there way enforce implicit relationsships in sql when creating database structure. on abstract level, want model following:

a person can belong 1 or many workfield's, workfield can have 1 or many sub-workfield's person can belong to. if person belongs 1 or more sub-workfield's, must belong parent workfield's.

is there way enforce relationship in sql or need in application layer?

you can enforce in database. 1 method have 4 tables: person, workfield, subworkfield, , personworks.

the first 3 pretty self-explanatory. subworkfield table have column workfieldid have foreign key relationship workfield. column subworkfield.subworkfieldid primary key, , column pair subworkfieldid, workfieldid declared unique. latter declaration redundant helpful enforcing 1 of conditions.

the personworks table have 3 (relevant) columns:

personid references person(personid) workfieldid references workfield(workfieldid) subworkfieldid references subworkfield(subworkfieldid)

each have foreign references suggested. when reference workfield only, subworkfieldid null.

in addition, there more 1 more foreign key reference:

foreign key (workfieldid, subworkfieldid) referenes subworkfieldid(workfieldid, subworkfieldid)

and, completeness, might add:

check (workfieldid not null)

this construction takes advantage of how null values handled foreign key references. if key or part of composite key null, foreign key reference not checked. hence, can have reference subworkfield table, , used when info populated.

sql