Seeking advice for database structure

[qazplm114477]

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

Employee table

• id
• name
• emp_no

//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_meta table

• id
• employee_id - foreign key
• field_name
• field_content

PROs:
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.

CONs:
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

thanks

[arionyx]

First off, why do you need two id fields? Why not just use the index id as the employee id. At which point, meta_table could be something like this:

Meta_Table
employee_id
field_name
field_content

The search would be something like:

SELECT * FROM Meta_Table Where employee_id = ?? AND field_name = 'field'

[mschroeder]

@arionyx

That design won't work because employee_id in that table can not be a primary key or even a unique index.

If it is set that way than 1 employee can only have 1 row in that table representing only one field and value combination.

@qazplm114477

Check out this article: http://www.codeforest.net/keyvalue-tables-and-how-to-use-them-in-php-and-mysql

It essentially validates your initial thoughts but also brings up some interesting points as to the shortcomings of this meta data design.

[qazplm114477]

Thanks for replying everyone!

@mschroeder

Thanks for the link, It pretty much addresses my concern, unfortunately the tutorial does say, "mainly use it for storing some arbitrary data that will never be used for filtering". This is one of my biggest concern, the program I am feebly attempting to develop relies heavily on multiple filtering of data...

do you think it's worth having a table with 15-20-30 fields or should I risk making queries that are overly complex and may get up to 50 lines long?

again, thanks for the link. all I need now is know which will do the least amount of damage in the long run... unless there's another way