I agree with the suggestion to keep it simple. When you are ready to tackle a more complex but very flexible method of handling many tables and databases, think about using Metadata. I have used Metadata tables many times to save my bacon when the table structures changed a lot or there were 100’s of tables and a 1000 columns total. Metadata means data describing data. I will give you an example of how Metadata can help you create, drop, modify columns, write SQL queries, basically the whole shooting match. This is a dynamic way to generating the SQL to drive the entire process.

Create a few Metadata tables that are populated by Class::DBI or by other methods. Then write programs that read the Metadata and create SQL scripts on the fly. Pass parameters to this Generator so that you can generate CREATE, DROP, etc scripts or SQL queries and for specific databases or tables. Once you have this Generator program written, you only have to populate the Metadata tables to create new tables or make modification. This Metadata system is then reusable for other database projects and you can spend your time enhancing the Generator instead of writing scripts and debugging syntax.

META_MAST is the master table that contains the definition of each column. META_KYS is the table that contains the list of keys. META_REL is the table that contains relationships between tables.

First create a table called META_MAST, with the following columns:

  • Id INTEGER PRIMARY KEY,
  • db_name VARCHAR(50) Database name
  • tbl_name VARCHAR(50) Table name
  • col_name VARCHAR(50) Column name
  • dtype CHAR(1) Data type: V:Varchar I:Integer C:Char
  • len INTEGER Length of the column
  • ky_col CHAR(1) Is this the primary key? Y/N
  • notes VARCHAR(255) Notes about the column

    • Create a table called META_KYS:

  • Id INTEGER PRIMARY KEY,
  • ky_exp VARCHAR(255) Key expression

    • Create a table called META_REL:

  • Id INTEGER PRIMARY KEY,
  • tbl_rel VARCHAR(255) Table relationships

    • Richard

    There are three types of people in this world, those that can count and those that cannot. Anon

    In reply to Re: MySQL Table Creation using DBI by richardX
    in thread MySQL Table Creation using DBI by blink

    Title:
    Use:  <p> text here (a paragraph) </p>
    and:  <code> code here </code>
    to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.