Database Structure involving dynamic fields
Im working on a project. Its mostly for learning purposes, i find actually
trying a complicated project is the best way to learn a language after
grasping the basics. Database design is not a strong point, i started
reading up on it but its early days and im still learning.
Here is my alpha schema, im really at the point where im just trying to
jot down everything i can think of and seeing if any issues jump out.
http://diagrams.seaquail.net/Diagram.aspx?ID=10094#
Some of my concerns i would like feedback on:
Notice for the core attributes like area for example, lets say for
simplicity the areas are kitchen,bedroom,garden,bathroom and living room.
For another customer that might be homepage,contact page,about_us,splash
screen. It could be 2 areas and it could be 100, there isn't a need to
limit it.
I created separate tables for the defaults and each is linked to a bug.
Later i came to the problem of custom fields, if someone wants for example
to mark which theme the bug applies to we dont have that, there is
probably a 100 other things so i wanted to stick to a core set of
attributes and the custom fields give people flexibility.
However when i got to the custom fields i knew i had an issue, i cant be
creating a table for every custom field so i instead used 2 tables. custom
fields and custom_field_values. The idea is every field including defaults
would be stored in this table and each would be linked to the values table
which would just have something like this
custom_fields table
id project_id name
01 1 area(default)
12 2 rooms(custom)
13 4 website(custom)
custom_field_values table
id area project_id sort_number
667 area1 1 1
668 area2 1 2
669 area3 1 3
670 area4 1 4
671 bedroom 2 1
672 bathroom 2 2
673 garden 2 3
674 livingroom 2 4
675 homepage 4 1
676 about_us 4 2
677 contact 4 3
678 splash page 4 4
Does this look like an efficient way to handle dynamic fields like this or
is there other alternatives?
The defaults would be hard coded so you can either use them or replace
with your own or i could create a another table to allow users to edit the
name of the defaults which would be linked to their project. Any feedback
is welcome and if there something very obvious with issues in the scheme
please feel free to critique.
No comments:
Post a Comment