Hello all...  please read

I've struggled with this for YEARS


-------------------------------------------------
THE PROBLEM:
-------------------------------------------------
        MySQL alone cannot represent a complex data-structure.

        Re-modeling of MySQL data is required in Perl, PHP, Java,
        etc.

     *  MySQL needs to have the ability to add properties to
        columns and tables.

-------------------------------------------------
Explanation:
-------------------------------------------------
        I have numerous MySQL front-end and back-end clients
        written in Perl, PHP, and Java.

        In each language - I have had to re-model the entire
        MySQL data stucture in that programming language.

        A PAIN IN THE BUTT.

        If I change one column, add one field, play with one
        table... I have to go into all of that code in all of
        those languages and mess with either how the front-ends
        and/or how the back-ends are interpreting the
        data-schema.


-------------------------------------------------
OBJECTIVE
-------------------------------------------------
        To get MySQL to be the one, central data-source for
        nearly everything used in your applications and
        data-interfaces.

        Not just data...
                ->Dynamic data.

        No more manual SQL statements in your applications.
                ->Dynamic SQL.


-------------------------------------------------
PICTURE THIS
-------------------------------------------------

     bear with me...

     -----------------------------------------
     EXAMPLE # 1
     -----------------------------------------

        Removing a single column from one of your MySQL reports
        would be as simple as doing this:


  *   mysql> UPDATE myTable.myColumn SET isVisible=FALSE;


        It doesn't matter what programming language the report is
        programmed in - that column would no longer show up in
        the report.

     -----------------------------------------
     EXAMPLE # 2
     -----------------------------------------

        Or change what name is displayed for your column name in
        all your MySQL reports?


  *   mysql> UPDATE myTable.myColumn SET Alias='My Display Name';


        That's right! Your column display names can have spaces!
        Programming your column names by hand is now optional.

        You can add and customize any column or table to have any
        property you wish.


     -----------------------------------------
     OK, BUT WHY ?
     -----------------------------------------

        You could do the same by just editing the SQL statements
        of your Perl, PHP, Java, etc. application, easy...

        RIGHT?

     *  WRONG.  *

        1.) You've got to go into (for example) your Perl scripts
            and change the name of the fields that are being
            shown there, the SQL statement itself, and the
            variables in Perl that the SQL statement is
            returning.

        2.) Then go into your Visual Basic application (you know,
            the one that all your accounting people use) and edit
            that code and SQL.  Then you have to re-deploy the
            Visual Basic application on all those desktops.

        3.) Or how about that PHP script that the sales people
            on-the-road use - it has the same SQL report! Gotta
            change that, too.

        4.) What about that other Java program?  5.) the C++ one?


     -----------------------------------------
     YOUR POINT ?
     -----------------------------------------

 * -->  MySQL needs to have the ability to add properties to
        columns and tables.

        The more flexible MySQL becomes, the more flexible your
        applications become... and the easier they are to develop
        and use.

        Instead of SQL code in your applications, automatically
        generated SQL.

        Instead of editing programming language code... a simple
        SQL statement from the MySQL shell could do a lot of
        what's needed to alter how your applications interpret
        the data schema.

        One SQL statement... one minute of time.


-------------------------------------------------
POSSIBLE SOLUTION #1
-------------------------------------------------
        --> Add properties/attributes to MySQL columns & tables

        For columns...

        Currently we have:
                             +----------------
                             |  Field
                             |  Type
                             |  Null
                             |  Key
                             |  Default
                             |  Extra
                             +----------------

        Are these properties flexible enough?

        I do not believe so.

        With the new file formats in MySQL 4.x and more complex
        data tables such as InnoDB, is it possible to ADD
        properties to columns? even add properties to tables?

        We could have:
                             +----------------
                             |  Field
                             |  Type
                             |  Null
                             |  Key
                             |  Default
                             |  Extra

                      (added)
                             |  Alias
                             |  isVisible
                             |  [more custom]
                             +----------------

        Alias and isVisible are simply custom properties. Any
        properties could be added to a column... or to a table.

        Properties of columns and tables will use the same
        data-types that MySQL currently uses.


-------------------------------------------------
POSSIBLE SOLUTION #2
-------------------------------------------------
        --> Two new tables:  TABLES and COLUMNS

                                TABLES
                             +----------------
                             |  Name
                      (added)
                             |  Alias
                             |  Description
                             |  [custom fields]
                             +----------------

                                COLUMNS
                             +----------------
                             |  Name
                             |  TableName
                      (added)
                             |  Alias
                             |  isVisible
                             |  [custom fields]
                             +----------------

        This would enable us nearly all the flexibility we need.
        Allowing the properties of both the tables and the
        columns to be foreign keys of other tables we use in the
        database, and already implement the current MySQL
        data-types.  It would allow for all the speed we need
        with no almost no further coding - using already existing
        MySQL indexes and the high speeds of the query parser.

        'Name' is the only required field for the TABLES table.
        'Name' and 'TableName' are the only required fields for
        the COLUMNS table. All other fields are customizable.


        I can add any field I want to the COLUMNS table:

                                COLUMNS
                             +----------------
                             |  Name
                             |  TableName
                      (added)
                             |  HTMLFormType
                             |  [custom fields]
                             +----------------


        But is it practical?

        Forcing for the existance of every database to contain
        two tables named TABLES and COLUMNS ?

        This solution is already being used by some.

        Some Transparent Persistance implementations in Java,
        such as POEM, use this method.  But POEM relies on the
        programming language of the MySQL client to keep an
        up-to-date listing of the TABLES and COLUMNS tables.

        If, for example, a table was added manually through the
        MySQL shell, those two tables (TABLES and COLUMNS) would
        contain old data.  Or even worse, if one of the tables
        were removed manually, again through the MySQL shell,
        there would be an invalid reference to a non-existant
        table in TABLES.  Perhaps a trigger could be used when
        they are available.

        The only possible downfall to this approach is that for
        any properties we wish to assign to a column - we must
        add to all columns.  So, even though a property may only
        apply to a couple columns in a particular table, it would
        be a property for every column in every table, although
        it might not need to actually be defined (could be left
        NULL).


-------------------------------------------------
Conclusion
-------------------------------------------------

        I believe we've all worked our way around this problem
        thus far.  Using the MySQL client's programming language
        to overcome dynamic MySQL schema barriers.  However, if
        we're all trying to do the same thing... in different
        ways... in awkward ways - we're doing it in the wrong
        ways.

        * There needs to be a standard.

        I'm talking about every interface ever devised for MySQL,
        not just HTML formatting.  After all, it's all about
        interfaces, is it not?  MySQL holds the data - but the
        data means nothing unless it can be viewed, added, and
        edited -- hence an interface.

        * If we can make MySQL more flexible - we can make our
        data interfaces more flexible.

        If we can program our interfaces and backends to look at
        column properties instead of just our columns - we'll
        truly reach a Dynamic database environment!  But without
        this - it's SO much more work for everyone!  A headache
        for the programmer, a nightmare to just add one column,
        then add it into all that code, keep the new code all in
        a versioning system, and then redistribute standalone
        client applications.  When with dynamic column
        properties... all one would have to do is make a column
        and (for example) mark it visible.

        One SQL statement... One minute of time.

        Am I missing something here that all other programmers
        are using?  Am I embarassing myself as some intermediate
        programmer?  Perhaps... but in my humble opinion and in
        light of what I've been exposed to, I feel there is a
        significant need for dynamic schema properties or
        something very similar.


Please advise.

I appreciate everyone's time and help... much thanks in advance.


Regards,

-Ryan Hatch
 Database Administrator
 HiTech Enterprises, Inc.
 825 Ontario Road
 Green Bay, WI  54311
 (920) 465-4600


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to