Storing government forms

I want to store a large number of filled-out government forms, like the Application for Federal Assistance. The forms are varied and change yearly. Field types vary, and can be: boolean, string, date, int, among others.

Is the best wa开发者_Go百科y to store these forms to completely normalize data?

À la:

form
+-----------------+-----------+------+-----+---------+----------------+
| Field           | Type      | Null | Key | Default | Extra          |
+-----------------+-----------+------+-----+---------+----------------+
| id              | int(11)   | NO   | PRI | NULL    | auto_increment |
| govt_identifier | char(40)  | YES  |     | NULL    |                |
| description     | char(100) | YES  |     | NULL    |                |
+-----------------+-----------+------+-----+---------+----------------+

filled_form (a form a person has actually filled out)
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| form_id   | int(11) | NO   |     | NULL    |                |
| person_id | int(11) | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

text_field (a class of input; belongs to a form)
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| name    | char(40) | YES  |     | NULL    |                |
| form_id | int(11)  | NO   |     | NULL    |                |
+---------+----------+------+-----+---------+----------------+

text_value (a particular input record; belongs to a class and filled_form)
+----------------+---------+------+-----+---------+----------------+
| Field          | Type    | Null | Key | Default | Extra          |
+----------------+---------+------+-----+---------+----------------+
| id             | int(11) | NO   | PRI | NULL    | auto_increment |
| value          | text    | YES  |     | NULL    |                |
| text_field_id  | int(11) | NO   |     | NULL    |                |
| filled_form_id | int(11) | NO   |     | NULL    |                |
+----------------+---------+------+-----+---------+----------------+

... continue for all input types

---

While this would work, your SQL will be slightly awkward and quite non-intuitive. Have you considered actually creating data models for each form individually and then using those to populate your forms. It may seem more work up front, but the development of your data capture will potentially be simpler.

---

I would have a look at single table inheritance. Model each field as a base class Field with subclasses IntField, BoolField, etc.

The Field class will have a member Name (string), IntField will have IntValue (int), BoolField will have BoolValue (bit), etc.

This requires you to have one column for each possible type in your Field-table, that is a bit space overhead, but on the other hand it gives you type safety. If you model as single table inheritance you can probably hook up your favorite ER-mapper without problem.