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.
B: Add columns onto the existing user table.
Hmm ok, well.
I would rather do option B, since it will apply to all users. And I think it keeps the DB Layout cleaner looking.
Maybe I should prefix them with custom_ in the user table.
Ill readup on the EAV thing, thanks doods.
If these fields may vary in the future, or they all won't be present for each user, then option A is appropriate.
do a search for EAV (entity-attribute-value)
not recommended unless you have no other options
option B is an option
indeed it does
but that's where the simplicity ends
getting meaningful data out of an EAV table is more painful than a root canal
don't take my word for it, or all the anti-EAV articles you will find when you search -- try it yourself and see
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.