Take the 2-minute tour ×

I've been thinking a lot trying to figure out how to make a flexible system to hold many values trying to avoid the option of adding more fields to table in the future. The only thing I could think off, is to make a table that will look like this:

    `id` int(11) NOT NULL auto_increment,
    `name` varchar(50) NOT NULL,
    `value` varchar(500) default NULL,
    `form_id` int(11) NOT NULL,
    PRIMARY KEY  (`id`)

|   id   |  name   |  value   | form_id|
|  100   |fullname |  Steve   |   1    |
|  101   |email    |ab@c.com  |   1    |
|  102   |fullname |  John    |   1    |
|  103   |email    |cd@c.com  |   1    |

This way, I could save each value in a row, and it would be as dynamic as I'd want. I'm aware of the bad performance in a very long tables.

Now I've also figured out how to make the View(front end) of the values in a "Regular" table. Looks just like a normal table.

|  ID    | Email   |Fullname  |
|   1    |ab@c.com | Steve    |
|   2    |cd@c.com | John     |

Now I want to create a temporary table instead of PHP loops. Any ideas how to make this work? How do I create a stored procedure that will receive the form_id as parameter and will return a table like this?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

Congratulations. You have re-invented the Entity-Attribute-Value model.

This model has existed for quite a while, but has proven to perform quite bad in a relational database system. You should probably not use it.

This answer makes a nice list of the pro and cons of EAV. The biggest pro is what you have discovered, it's easier to design. The biggest con is what I'm telling here: it's worse on performance.

Since usually you design far less often than your queries run, it might be better to think a bit longer while designing, and have faster queries.

share|improve this answer
I'm aware of the Bad performance, but the dynamic options covers. it. –  Livne Berebi Mar 14 '12 at 7:02
By the way, never feel bad about (re)inventing something that already exists: it means your invention was good enough to already be thought of by someone else. Keep going, and one day, you'll improve something or think of something completely new. –  Konerak Feb 7 '13 at 14:07

NoSQL is considered more dynamic Try the schemaless approach. this may be a goot starting point:http://www.igvita.com/2010/03/01/schema-free-mysql-vs-nosql/

share|improve this answer

Your Answer


