schema - Database design of a record keeping system -

i in process of designing database of record keeping system. system has 4 types of record / forms.

here design.

first, 4 forms has common input fields such name, age , address. separate common input fields in table , add foreign key.

for example:

common_fields  id  name  age  address  form_fk_id (foreign key form)  form_type_number(this define kind of form user used)  form1  id  birthdate  occupation  etc..  form 2  id  mother_name  father_name  etc..  form 3  id  school_address  school_name  etc.. 

basically, that.

form_fk_id - foreign key connect common input fields table , form table

form_type_number - useful knowing form retrieve.

i unsure of design. dont know if best way design in.

you don't want fk reference in common table. common table have key , type indicator detail tables reference their fks.

common_fields  id  form_type (defines kind of form) must contain 1, 2 or 3  name  age  address  constraint uq_common_fields_id_type unique( id, form_type ) 

the unique constraint makes fk reference (id, form_type) possible.

form1  id  form_fk_id (with id, fk common) must contain 1  birthdate  occupation  etc..  form2  id  form_fk_id (with id, fk common) must contain 2  mother_name  father_name  etc..  form3  id  form_fk_id (with id, fk common) must contain 3  school_address  school_name  etc.. 

assume form_type fields small integer , constrained in common table values (1, 2, 3). in form1 table, field constrained value 1, in form2 2 , in form3 3.

when new form created, common data inserted common_fields table, generating unique value id. form_type field contains value 1, 2 or 3 depending on type of form.

say value 1, matching record can inserted form1 table.

an entry same id value cannot written either of other form tables. value of form_fk_id of 1 not allowed each table has check constraint making sure field must contain proper value. value other 1 not find matching set of values in common table.

this makes form defined particular form type in common table can have associated entry in correct form table.

when user wants see details of form 123, locate 123 in common table , join details correct table according form type.

actually, have 3 views, each view showing complete details of each form. triggers on views allow app code manipulate each type of form if form data in 1 complete row instead of broken across 2 tables. hides structure app , makes sure structure correctly maintained.