Forum Moderators: open
I have a database that holds company information for each month that we receive a job. All of my tables are named by the month and year (i.e. March_2008). Each new month creates a new table. Each table contains the same columns. I want to be able to build in a search component so that I can go back and find old work based on the infomation stored, but am at a loss as to how to go about it. I was originally thinking about using UNION, but I was unsure how that would work with the growing number of tables each month. Does anyone have a suggestion? Also, is there a better/more effecient way to build the table (maybe put everything into one)? The reason for them being seperated into seperate tables now is that we assign an internal number to each one (i.e. 0803001, then the next month would start back over at 0804001), the last three digits are incremented to the next number. I was unsure how to do acheive the right count if everything was in one table. My PHP code grabs the next number for me automatically by counting the number of rows in the table and adding one. I know this isn't a PHP forum, I am not asking for advice on that, just thought someone might have a better way to build the table(s). Thanks for your help in advance.
This is the layout of a table if it helps at all:
Table Name: March_2008
Columns: assignment_num app_name claim_num insured_name ins_co adj_name claim_status date_assigned date_completed date_inspected supp_assigned supp_completed
JRNeher
I assume the table holds something like invoices, which have a serial number, and you want the serial number of make some real-world sense.
Assigning the serial number doesn't sound difficult. Simply query the database for the highest serial number. Compare the first 4 digits with the current year and month. If they are equal, increment the serial number by one. If they are not equal, assign the current year and month, with "001" appended.
Depending on the database, you may also be able to create a special "autoincrement" function that overrides the standard "add 1".
how would I go about seperating out the February numbers to get the next available March number (51)?
The February numbers are irrelevant. All you care about is the maximum value for the number, and the current date.
SELECT MAX(serial_number) FROM mytable;
Now, if the first 4 digits of the result are the current year and month, then simply increment the serial number by 1. Otherwise, you start over for the new month.
Of course, the SELECT about and the INSERT have to be in a transaction, to prevent somebody else from slipping-in between the time that your read the max serial number and when you insert the new one.
