It might seem appropriate to post this in the MySql section but I guess this is also a PHP question.
Which is the more efficient way for adding new fields? I haven't written the code yet because I want to get the database structure right before coding. Here's my logic.
I want the user to be able to add new fields to an existing database table eg: employee table. Should I just Alter the table and add a new field OR should I just create another table ex: employee_meta and have all new fields saved there?
my initial plan was to have something like this
//to add new field query = "ALTER TABLE Employee Add $customfield $data_type"; //that way it'll be easier to refine searches like //select * from employee where $customfield = $value;
but if I were to add another table
- employee_id - foreign key
if I were to do something like that, it should be easier to add new fields via the UPDATE method. And it should also give you more control over who gets the new field.
redundant data! lets say I have 5 custom fields for all employees, that means there will be 5 employee_id for all employees, and if I had 500 employees then I will have to go through 2500 records to get what I want. Also searching will be more complicated.
So, if anyone has any advice, it will be greatly appreciated