[thelist] Magic data table - there is a reason

[Mattias Thorslund]

Carolyn Jewel wrote:

> Maintainability is an issue already, since the client wants to collect
>more information from registered users than the current system is
>configured to gather.
>

..and yet I think it was precisely concerns about maintainability that 
lead this developer to design the database the way he did.  I think I 
understand what motivated this guy, though the solution has problems, too.

I don't think it is *such* a crazy idea, from a maintenance point of 
view, if there is a consistent set of classes/functions to 
select/insert/update/delete the data. 

It doesn't do anything for performance, however.  I expect there would 
be a performance overhead since the queries would become more complex. 

I think this scheme was implemented with the intent to reduce the number 
of locations that need to be updated when the data structure changes. 
With a typical setup, changes to the data structure such as adding a 
field to a table potentially impacts all of the following:

* All the SQL queries that use the table: selects, inserts, updates, 
even deletes.  The number of select statements impacted can be huge.
* Any view or stored procedure that use the table.
* Code in the business layer or presentation layer - your PHP/ASP/etc: 
Web forms, classes etc.

All this maintenance translates into time wasted on keeping things in 
sync. I've done this for much too long. No more for me.



I use a similar structure for certain lookup lists in order to avoid 
having a large number little lookup tables that only have a few items in 
them.  This is for rarely changing data such as Gender, Units of 
Measurement and the like.  These tables typically exist only to hold the 
options of a <select> list box or a series of radio buttons. 

Yes, my table has one more field than would have otherwise been needed, 
the ListID, but I don't have to create any new table, write any more 
code or SQL statement to have another list. Very simple, and with 
negligible performance impact.



The other thing I do is model my data structure in XML files, which are 
used to build tables, and generate SQL statements, web forms, other PHP 
code and so on.  Since the data structure has one single source, it is 
consistent across the entire application. 

Another benefit to this is that if I find a better way to do something 
which is repeated in many pages, I only need to change my template, and 
regenerate te app - the update is now everywhere at once.

So this solves the problem that I think our unknown developer was trying 
to address.


Mattias Thorslund
Code Generation is COOL!