Re: MySQL Table Creation using DBI

If you're of an object-oriented attitude then I'd take a look at Class::DBI, which allows you to quickly create an object based interface to tables. I find it very useful and a huge timesaver.

As for the "no. of tries" problem - having 5 tables, one for each try, probably isn't the best solution (what happens when somebody decides you need 4 or 6 tries? what happens when you need to add a little bit more info on each try?)

Instead have a table with a primary key of "jobid" & "try number" so you would have something like this:

jobid  try  server  stunit  ... etc ...
    1    1     foo     bar
    1    2 fribble     baz
    2    1    feep  babble

... and so on ...
[download]

This way you're only ever messing with a single table. Makes your code (and future maintainence) much easier.

Comment on Re: MySQL Table Creation using DBI Download Code

Replies are listed 'Best First'.
Re: Re: MySQL Table Creation using DBI by Revelation (Deacon) on Sep 05, 2002 at 00:55 UTC
The structure of the tables is a lot more dependant on how they are used. A single table may or may not be the way to go, depending on the characteristics of your queries, and of the tries. For instance: If you need to query all databases to find something other than the primary key often, a one table structure is probably the best; however, if all your queries, after the insert assume that the lookup row exists, and will only look for values from that, a table for each try would probably be best. When your database gets big, and you look for the 'ended' value for try three of jobpid 200, and you know jobpid 200 exists in the try 3 table, the query may be a great deal faster. On the other hand, if you want to only retrieve jobs with more than three tries for your error logs, then a single database with a'WHERE try = 4' clause. But what if tries 3 and 4 never have some trait. Do you want to leave it blank in your single table? I would recommend making the decision of database structure on my own or putting out as much code, and as many facts as possible. Database structure is always a touchy subject, when getting developmental suggestions, because as adrianh points out, a single table is much more flexible; however, you must decide how much flexibility you are willing to sacrifice for speed. You may want to read up on Database Normalization 1 2, before you decide what the structure of you database will be. While youdevelop your 'project'(of which we know very little, and therefore our suggestions may be completely wrong), adrianh's suggestion of a single table is the right one. I could be completely wrong Gyan Kapur gyan.kapur@rhhllp.com	[reply]
Re^3: MySQL Table Creation using DBI by adrianh (Chancellor) on Sep 05, 2002 at 11:00 UTC
Actually for the table format I suggested doing look for the 'ended' value for try three of jobpid 200 would probably be faster than doing WHERE try = 4 since the former is a primary key lookup (jobid & try) of the new table - where the latter might well have to do a full table scan. If you are doing queries like this a separate table for each try might be faster (although many databases would allow you to build a separate index on the try #). :-) That said I totally agree with Gyan's comments on the fact that DB design is not trivial. You know more about your data, and how it will be queried, so having a single table may not be the best solution.	[reply]
Re: Re: MySQL Table Creation using DBI by elwarren (Priest) on Sep 05, 2002 at 20:52 UTC
Do like adrianh says and just insert rows into a table instead of creating a new table every time. What would you do when you have hundreds of tables? Would you join a hundred tables together to build your reports? With a single table it would just be a single query. What would you name your tables when you have that many? Are you going to write a maintenence routine to cleanup the database after creating a dropping hundreds of tables on a daily basis? With a single table it's the database's responsibility to clean the table's storage space. (actually, this is also a concern, but not as drastic as the impact of dropping and creating tables, depending on the database you're using.)	[reply]