Database design of a record keeping system

Question

I am in the process of designing the database of a record keeping system. The system has 4 types of record / forms.

Here is my design.

First, the 4 forms has common input fields such as name, age and address. I separate this common input fields in a table and add a foreign key.

for example:

common_fields
 ID
 Name
 Age
 Address
 Form_fk_id (foreign key to what form)
 Form_type_number(this will define what kind of form the user used)

form1
 ID
 Birthdate
 Occupation
 etc..

form 2
 ID
 Mother_name
 Father_name
 etc..

form 3
 ID
 School_address
 School_name
 etc..

Basically, it will look like that.

Form_fk_id - foreign key that will connect the common input fields table and form table

Form_type_number - this will be useful for knowing what form to retrieve.

I am unsure of this design. I dont know if this is the best way to design in.

Answer 1

You don't want the FK reference in the common table. The common table will have the key and type indicator which the detail tables will reference with their FKs.

common_fields
 ID
 Form_type (defines what 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 to (ID, Form_Type) possible.

form1
 ID
 Form_fk_id (with ID, FK to common) must contain 1
 Birthdate
 Occupation
 etc..

form2
 ID
 Form_fk_id (with ID, FK to common) must contain 2
 Mother_name
 Father_name
 etc..

form3
 ID
 Form_fk_id (with ID, FK to common) must contain 3
 School_address
 School_name
 etc..

Assume the Form_Type fields are small integer and are constrained in the common table to only the values (1, 2, 3). In the Form1 table, that field is constrained to only the value 1, in Form2 only 2 and in Form3 only 3.

When a new form is created, the common data is inserted into Common_Fields table, generating a unique value for ID. The Form_Type field contains the value 1, 2 or 3 depending on the type of form.

Say the value is 1, thus a matching record can only be inserted into Form1 table.

An entry with the same ID value cannot be written to either of the other form tables. The value of Form_Fk_Id of 1 would not be allowed as each table has a check constraint making sure the field must contain the proper value. A value other than 1 will not find a matching set of values in the common table.

This makes it so a form defined as a particular form type in the common table can only have an associated entry in the correct form table.

When a user wants to see the details of form 123, locate 123 in the common table and join to it the details from the correct table according to the form type.

Actually, I would have three views, each view showing the complete details of each form. Triggers on the views would allow the app code to manipulate each type of form as if the form data was in one complete row instead of broken up across two tables. This hides the structure from the app and makes sure that structure is correctly maintained.