Quote:
Originally Posted by Old Pedant
There are many reasons NOT to have multiple tables with the same structure and many reasons to combine them into one table.
|
About the ONLY reason I can think of for setting up multiple files with the same structure is where you have a high percentage of the records that are effectively "dead" and are still there only as an archive of past information. Rather than having an active/archived flag field it might be worth splitting them into a separate table as then almost all of the queries need only deal with the subset of still active records. That split would work really well with say one million active records and 49 million archived records as then most searches are only looking at the 2% of the total data.
For any situation other than that simply adding an extra field is the most efficient alternative.
It is really amazing the number of people who add unnecessary id fields to their database tables and then wonder why they have trouble detecting duplicate inserts - which would not occur if they used the right key in the first place.