[thelist] Magic data table - there is a reason
Mattias Thorslund
mattias at inreach.com
Thu Aug 19 12:25:28 CDT 2004
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!
More information about the thelist
mailing list