I want to allow a person to insert custom fields and was wondering what’s considered best practice:
A: Create a new table “custom_user_fields” to store the fields the admin wants,
and Create another table “custom_user_data” to store the data related to the fields.
Haha, I can imagine that getting sticky pretty bad.
I had read some Wikipedia and another site, it
Im reading a few sites its pretty funny, one guy says:
I was brought in to a project with a database in EAV model style,
the database is already widely deployed to customers and there is no chance
of changing the schema because “the database design is so good that they
haven had to change the schema for 10 years” [sic]
I would choose B but factor out the dynamic data into another table with foreign key back to the users table. You already have a users table right? That table can pretty much stay static. Than you can add another table a users_id as the primary key only. When an admin adds a column just alter this “dynamic” table. You will just need to make sure you application data layer can easily expand with this table. So rather than hard coding select columns for the dynamic data table you should use a array of all columns or something. That way when someone adds to the table the system adapts to the change without breaking or requiring additional modification. You also maintain the 1:1 relationship between users and dynamic data, which will make selecting data, filtering, sorting, etc much more efficient than an EAV. It seems like only specific people have the rights to add columns so its not a huge task to alter the dynamic table every now and than. Much better with that than an EAV which can be a nightmare to manage.