On 11/30/07, Nan Harbison <nan at nanharbison.com> wrote: > Hi All, > > I have a table of project data (id, type, contact person, description, etc) > which could get very long. When a project is finished, the user has the > ability to archive the project. > I see two ways to do this: > > 1. create a duplicate table with all the same fields ('archived_projects' > or something) and move the information over to that table. > > 2. have a field in the 'projects' table to mark it as archived > > It seems to me that the first way would be more efficient because when it is > searching for projects in progress, it checks a shorter list of projects. > > Do you agree with this? I don't know much about efficient design. We would need more information. However, for your sake I will assume the following: 1) database server has plenty of RAM so processing of larger datasets would not require unusual amounts of disk i/o 2) The only thing you would ever search on in your application is id and type (and archived_projects, if you went that route). I'll further assume that type has enough values to make an index/partition useful. 3) ... When I finished this list, I would turn around and tell you to forget about it and add an dte_archived field (rather than a yes/no bit). Null means active, non-null means archived. At any time you could implement a batch job that takes (sysdate - dte_archived) > x records and moves them to another table or data store, possibly sitting on cheaper storage. Otherwise, we're just taking our best guess at how your database software and hardware would perform under arbitrarily large data sets. My guess is that it will be fine, but the point is that with the above column addition, you can plan for the situation when it is not fine, if that time ever comes. Until then, I wouldn't worry about it too much. -- Matt Warden Cincinnati, OH, USA http://mattwarden.com This email proudly and graciously contributes to entropy.