Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Radical use of MySQL comments
February 13, 2008, 4:38 pm
rate this thread
My company advertises "not off the shelf" programming and what we
provide can be fit to each clients needs. With that the biggest goal
I have is to have the 80% of what everyone needs ready to roll - but
make that other 20% unique to the client's project fast to turn out.
I'm about to begin programming an "editor" page that looks at the
table being worked with - and then based on column type, naming
conventions, etc - PHP draws the form with appropriate inputs for
columns. I need to put a friendly label on the field - so instead of
"StatusID" it could say "Page Status" --- my thought is to put "Page
Status" in the comments for that field and pull it out at time of
display to the user.
Pros I see in this are
Editing the database to add/edit/remove fields will also affect the
editor page at the same time
The same edit will also handle Insert/Update statements so no SQL will
Logic written 1 time for rules of how to display column inputs will
rarely if ever need touched again
Time to finish that 20% custom to each client is drastically reduced
Can you please provide cons to this idea? So far internally its been
"Thats not how its done" and I'm asking "why can't it be done?"
Re: Radical use of MySQL comments
The problem is not this cannot be done (after all, it's basically how
PHPMyAdmin works for instance). Aside from the fact this has quite some
overhead, the problem is not all functionality can be grasped
automatically from a database/table setup/description/comments.
Before you know it, you have a foreign key to be entered in a field
somewhere (which is fine), but in creating a select box, you don't want a
key, you want a usefull name for the client, so this has to be stored
somewhere else. Also a comment? Nope, that one is allready in use, unless
you decide to write you own little language/data format into a comment.
(Also keep in mind you're tied into MySQL with this kind of code, no way
it's as easy as another database interface for eveything to work on
another database, there will probably be an enormous amount of
code/queries to be rewritten.)
Well, for obvious reasons, we decide against a pseudo language/format in a
database interpreted by another script, it will quickly become to
cumbersome writing an interface to set the comments on each field in each
table correctly. Another table perhaps, describing relations between
tables. Ah, now we're getting somewhere. That was for only one kind of
relation though, one-to-one, many-to-many, and more exotic relations are
all possible. And what if there has to be some custom HTML/other layout
for the client?
Before you know it, half of your database consists of tables just
describing the rest of the database. Any alteration in that heavily
cascades straight into production code, and even determening what kind of
interface to generate for the user can take dozens, if not hundreds, of
queries as to what fields are present and what relations they may hold. It
will be a slow, cumbersome system, and a nightmare to maintain, track
bugs, or even alter. I don't even want to think about writing an interface
to create or maintain the database that creates an interface.
Offcourse, this story is a little bit over the top, but as soon as you
start out like you suggest, the urge to create more and more elaborate
database constructs to keep the code static has overwelmed most of the
ones that started out like this. It's not a novel idea, but one that has
been tried with the best intent, and become a monster. There are some
great stories about this at for instance <http://thedailywtf.com/
A script is for scripting, a database to hold data, and almost all
projects that diluted the difference between those two have failed (yeah,
I know, stored procedures are a good thing). Scripts are a lot more
flexible, easier to maintain and troubleshoot, and mostly run faster then
you can query a database. The only way this will work is by realising the
limitations, and setting strict boundaries (to here in the database in no
further). That almost always results in something like PHPMyAdmin (or
other database admin interfaces), which are perfectly suitable for the
savvy users, but not for most clients applications are made for.
Offcourse, for simple tables with no external or very limited relations it
could go well for a long time. Be very certain that is how it will be
_and_remain_ though. The next coder, implementing a new feature desired by
the client, might be very tempted to tweak the database a little to avoid
having to do a large rewrite on your code, and will not be aware of the
boundaries you yourself have set. And the next... And the next...
If you're worried about re- and rewriting database interfaces again and
again, building a code generator might be better worth your time. Enter
once which table/what kind of fields it should maintain, and have some
generic output for that. That way, you can tweak/alter/improve the
generated code without having it cascade all through your project/the
database, making it far more maintainable.
- C. (http://symcbean.blogspot.c
February 14, 2008, 1:02 pm
Re: Radical use of MySQL comments
...and others. Not very radical really.
The main problem here is how to maintain the metadata. There already
several solutions which attempt to address this specifically for PHP
(metabase/metastorage for instance). There are lots of non-language
specific tools out there (most of which use XML to describe the
structure). I'd be having a long hard look at what has been published
elsewhere before defining my own "standard".
...only if used correctly, and even then.....? Typically encapsulating
complex transactions is better done at the PHP layer - anything else
is a view. (C dons flame retardant overalls)