jeudi 4 décembre 2014

Field types database design best practice


I am creating a cms system where the user can create fields to use in the templates of the website. I want to create different types like 'string', 'html', 'integer' etc. The user must also have the possibility to add these fields in different values, so I will be needing at least two tables.


My question is this: Should I create a table for each field value type and catch this in the code or should I store all field value types in one table?


Option 1: Table for each type



+---------------+ +-----------------+ +--------------------+
| Fields | | field_int_value | | field_string_value | etc...
+---------------+ +-----------------+ +--------------------+
| id | | field_id | | field_id |
| field_type_id | | language_id | | language_id |
+---------------+ | value (int) | | value (varchar) |
+-----------------+ +--------------------+


Option 2: Store all values in one table. keep the unused values NULL.



+---------------+ +-----------------+
| Fields | | field_value |
+---------------+ +-----------------+
| id | | field_id |
| field_type_id | | language_id |
+---------------+ | value_int |
| value_string |
| etc... |
+-----------------+


Some examples:


A user could for example create a string with his or her phone number. In the code the template would call this field to display the phone number. If the user would ever change this, they can just edit the value in the admin without having to worry about editing the template.


Another example is a boolean to allow the user to show a certain 'call to action' on a page. This boolean would be used to hide something in the template without having to update the template.


My Field model would be the one handling all this. In here I would create functions to get the field of a specific type.




I have been breaking my head over what would be the best option, but I can't seem to find the better one. Does anyone have any ideas?





Aucun commentaire:

Enregistrer un commentaire