A dynamic content solution for a flexible mysql table

Question

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:

CREATE TABLE IF NOT EXISTS `form_data` (
    `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?

score 1 · Accepted Answer · edited May 23 '17 at 11:54

1

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.

edited May 23 '17 at 11:54

Community

1
1

answered Mar 07 '12 at 08:16

Konerak

39,272
12
98
118

1

I'm aware of the Bad performance, but the dynamic options covers. it. – Livne Berebi Mar 14 '12 at 07:02
2

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 07 '13 at 14:07

score 0 · Answer 2 · answered Mar 07 '12 at 12:09

0

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/

answered Mar 07 '12 at 12:09

ApriOri

2,618
29
48

A dynamic content solution for a flexible mysql table

2 Answers2