I have a database that will contain 43 tables (among others). For these particular tables, they will each contain the same type of information, each representing a particular week in the NASCAR season. I used the following script to create one table:

DROP TABLE IF EXISTS gulfOwnerDriversWeek1;

CREATE TABLE gulfOwnerDriversWeek1 (
ownerIndex int primary key,
driverIndex1 int,
driverIndex2 int,
driverIndex3 int,
driverIndex4 int
);

This table represents a draft sheet for one particular week (Week 1) for a particular team. My first question is, should try to put all weeks (43 of them) into one database, or should I keep each week in its own table (seems to be the smartest method).

The only way I know to create a table right now is either through the mysql command line or a simple script. To create every table I need for my league in this manner would be very difficult. Is there an easy way to create 43 identical tables where the only discrepency between the table names would be the number on the end: gulfOwnerDriversWeek'X'?

I have lots of tables like this for stats & what not and can't bring myself to create every one separetly at the command line. Thanks.

 

 

 

Reply

use looping w/ php

Reply

Another approach to this problem is to "merge" week info into your drivers table, instead of spreading it all over 43 tables, requiring in this way a script language to manage it on an higher abstraction level.

If you add and ID_WEEK column to gulfOwnerDrivers table, setting ID_WEEK from 1 to 43 for every set of drivers per week, you'll be able to store all your data in one table.

All queries concerning this table will be constrained by a WHERE ID_WEEK = <week> clausole in order to select every time only one particular week results.

prototype of query for gulfOwnerDrivers:


<week> is an alias for a certain number of week.

Reply