Creating new tables is database specific, although DBI allows you to send through any database-specific commands directly to driver.

I am guessing if you want to create many tables, all these tables will be similar, right? Maybe you can create one meta-table, with fields like TBLNAME, KEY, CHARFLD1, CHARFLD2, ... NUMFLD1, NUMFLD2..., TXTFLD1, TXTFLD2... etc. Then, instead of creating table, you need just insert rows into meta-table. This way you will have new table implemented as "SELECT * FROM meta-table WHERE TBLNAME = 'mynewtable'". I know that proposed implementation might be half-empty meta-table (often most of the fields will not be used), but OTOH you can add "table" dynamically.

We used this approach once and it was very successfull. We have database which was very touchy when DB stucture changed - all code required recompilation. Using our approach we avoided a lot of pain. We put more that hundred small simple tables (CODE DESCRIPTION) and were able to add new features into our code which usually reqired changes of structure rather easy.

Here is how to optimize this approach: (1) you need index by TBLNAME and KEY. (2) if you want to avoid many empty fields, you may put all remaining fields (after TABLENAME and KEY) into one long text field as CSV (comma-separated values), packing and unpacking them as needed.

Obviously, meta-table approach is valid if you need to store hundreds small tables (between dozen and couple hundred rows each max), if you want to have hundreds of thousands of rows, it deserves special table.

I hope it makes sense, it's too late and I am sleepy like a dog now...If you need more info, /msg or reply here.

pmas
To make errors is human. But to make million errors per second, you need a computer.

In reply to Re: have a script autmatically create a SQL database by pmas
in thread have a script autmatically create a SQL database by lwilliams

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.