[thelist] database structure for speedier queries

Joel D Canfield joel at streamliine.com
Fri Nov 30 12:38:04 CST 2007

> 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.

How many millions of records will there be? ;)

Unless you see the database growing to a large number of records (read:
at least hundreds of thousands) I'd stick with the simpler solution of
adding a bit field to signify whether or not it's been archived, as you
suggest. (I built a simple search tool for the half million records in
our document management database. Complex searches requiring my very own
special wonky joins sometimes took two or three seconds. That was the
worst case scenario. The old, tired server it ran on wasn't even
breathing hard searching that little pool of records.)

Adding an 'archived' bit also allows the possibility of 'unarchiving'
something just as simply.

I always worry about moving data, because it really means copying and
deleting. And, as an information freak, deleting stuff, even if you
copied it elsewhere, scares me.

But now that I've spewn (is that a word?) all that, let's back up a bit
and ask, what's the business purpose of archiving a project? Is it
purely informational, or is it designed to prevent someone from
accidentally using archived data? Is there a reason someone might want
to see archived and current projects in the same report or search

Once you really get your head around the business logic, the technical
logic should become self-evident.

If not, you know where we are ;)


More information about the thelist mailing list