Radical use of MySQL comments

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi everyone,

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
need touched
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


Quoted text here. Click to load it

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.
Rik Wasmus

Re: Radical use of MySQL comments

Quoted text here. Click to load it
Quoted text here. Click to load it
Quoted text here. Click to load it

...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".

Quoted text here. Click to load it

...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)


Site Timeline