|go ahead... be a heretic|
Re: have a script autmatically create a SQL databaseby pmas (Hermit)
|on Sep 08, 2001 at 09:03 UTC||Need Help??|
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.