I'm in the process of making an application that among other stuff, also has a user info management section. There are 10 basic fields that the users fill to register (first name, last name, address etc) but the admin can also define custom fields that that will be included in the registration form. At the moment I implemented it with EAV. I have a table "users" which contains all the 10 basic fields as columns, users_custom_fields (field_id, field_name, field_type) which contains all the custom added fields and a table "users_custom_data" (user_id, field_id, field_value), which contains all the data for the custom fields.
My question is this: Wouldn't it be better if adding a new field in the php backend would just create a new column in the "users" table instead of creating a one-to-many relationship where each user entry also matches a few rows in the "user_custom_data" which makes it really hard to search and adds unneeded complexity? Is it considered bad practice if the application dynamically alters the structure of a table?
Thanks
"Is it considered bad practice if the application dynamically alters the structure of a table?"
In my experience, yes. However, you've run into the one place I think it may have a place. But you have figure out how to deal with a couple issues this raises...
Where is the displayed field name stored? What is going to keep track of these new fields? etc...
You could give each such field a standard prefix of some sort hidden to the user creating them, and use the information schema to "find" the fields at run time; but you still have to deal with names that may not be sql friendly, or what if the user wants values restricted to integers, instead of any valid string. All that can be solved by creating an additional table to hold metadata, but then you begin down the road of (in most cases) making a cure (for EAV) worse than the disease.
Edit: I would almost sooner give "admin" users limited interfaces to make additional tables for these things rather than columns in the core tables.