[thelist] Magic data table

[Maximillian Schwanekamp]

Edwin Horneij wrote:

>I may have inherited a project from another developer, and I am not
>sure what to do about the database structure he created. It is
>impressive, but strikes me as somewhat unorthodox.
>
>He has created a kind of "one size fits all" meta-table that contains
>everything -- HTML templates, product information, user registration
>info, and probably a lot more.
>  
>
Is there only one table in the database?  Your description seems to 
suggest that.

>The columns are named like this: "Field_1_Name", "Field_1_Type",
>"Field_1_Value", "Field_2_Name", and so on. Queries take the form
>"SELECT * FROM Content WHERE Field_1_Name = 'First Name' AND
>Field_1_Value = 'Edwin'".
>
>My predecessor seems to have also written PHP code that changes the
>structure of the table where necessary. 
>
Crazy.  That could be useful in some instances, but the sample data you 
gave suggests this is pretty standard stuff.  Do you maintain some sort 
of map of this super table?  Any thought to maintainability for future devs?

>For example, if a newly
>submitted value for a given entity's attribute exceeds 255 characters,
>the system moves all of the data for that attribute of that type of
>entity from the default varchar column to a text column. I _think_ it
>will also add new columns to accomodate more attributes than there are
>columns available, should that be necessary.
>
Maybe there is a good reason for this, but I doubt it.  Again, I can 
think of a few instances where this sort of thing might be handy - where 
an application requires maximum flexibility in certain limited 
instances.  Without knowing more about the app, it's hard to make 
judgements, but Conventional Wisdom(tm) suggests that scripts should not 
be modifying table structure on-the-fly in 99.9% of cases.  After 
extended use and/or with concurrent connections, that kind of system 
will likely slow to a crawl. 
-- 
Maximillian Von Schwanekamp
Dynamic Websites and E-Commerce
NeptuneWebworks.com <http://www.neptunewebworks.com/>
voice: 541-302-1438
fax: 208-730-6504