Re: MySQL Table Creation using DBI

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

Comment on Re: MySQL Table Creation using DBI

Replies are listed 'Best First'.
Re: Re: MySQL Table Creation using DBI by the_Don (Scribe) on Sep 05, 2002 at 14:28 UTC
I think I see the point and flexibility in the general theory of the scheme you describe, but I am having trouble understanding how the META_KYS and META_REL are used. Perhaps it is just too early and I am too sick (allergies, not mental) to grasp this, but is there a page or some site that describes a bit further how this is implemented? the_Don ...making offers others can't rufuse.	[reply]
Re: Re: Re: MySQL Table Creation using DBI by richardX (Pilgrim) on Sep 06, 2002 at 05:21 UTC
Sorry Don, my fingers got tired before I got to finish all my thoughts on the idea. The META_REL and META_KYS are for storing the list of columns that make up the multiple keys or relationships that tables have. For example in the ky_exp column could be populated with “cust.cust_lastname+cust.cust_firstname“. When it came time to create the keys for the CUST table of customers, the Generator could read this row and write the proper CREATE statement to a file like createcust.sql. You would also need to add the foreign key from META_MAST to each of these tables along with a column for the name of the database and table name. My ideas and techniques do not come from one place or one project but from a myriad of projects that used a wide variety of Data Warehousing, CASE, and Data Dictionary tools. I would suggest looking in Google for the keywords metadata, SQL, and database. Most of the commercial tools or homegrown tools that I have used, had a database backend. The project that I remember the best that this idea saved me on was a data conversion project, converting Mainframe (MF) data to SQL tables. The MF data to start with was “dirty” and had to be verified using various pieces of code snippets, looking for invalid data, corrupt data, orphan rows, and the like. The next problem was the MF data used data types that SQL did not have, like packed decimal. So I had to write another snippet to decode the packed decimal. Then the data had to be inserted into SQL tables. Sometimes this was a one for one basis, which was a piece of cake. But most of the MF tables had to be split into several tables and populated in a relational way into the SQL. If I chose to do this project by hand coding, I would still be working on it. To make matters worse, the MF programmers were constantly changing the schema, and I was last to know. The icing on the cake was that we were dealing with 200 tables from the MF which created 350 SQL tables. So I created a metadata Generator like the one I described before and stored all the attributes of the MF input data and the SQL output data in my metadata database. When the MF data came to me in large tapes with different schemas, no sweat, I merely remapped it in my schema description tables. Sometimes the SQL programmers came to me with ever changing SQL schemas. Again I just changed the SQL schema description tables. I extensively used TEMP tables and ad-hoc tables for key lookups and temporary data storage until the final COMMIT of each transaction. With this system I could rapidly adapt to any curveballs thrown to me, and I could iteratively output SQL tables so that the SQL programmers could have some test data, and then feedback to me the schema changes on the SQL side. If any programmer added a table, it took me about five minutes to update the metadata and I was ready to crank out the data. Some of the side benefits from using this metadata Generator were documentation, statistics, and accuracy. I wrote several SQL reports that queried the metadata, giving a complete map of the MF tables coming in and where they mapped to in the SQL. Sometimes the programmers used this documentation to find errors in their data structures. I wrote several SQL reports that generated statistics of rows inputted, outputted, errors found and the type of errors, corrections made by the program, etc. Finally, the overall accuracy of my data conversion project was better by far than a set of manually written scripts, and this improvement in productivity was provable to management. Richard There are three types of people in this world, those that can count and those that cannot. Anon	[reply]