[thelist] database structure for speedier queries

[Nan Harbison]

Hi Joel,

If this website flies, there could be millions of rows, you could see me on
Oprah and I will have a permanent job, woo hoo!
Seriously though, the information is strictly a nice thing to have, it is
not essential stuff, but yes, someone might want to see current and archived
projects in the same report, I suppose. That wouldn't be hard to code
though.

Thanks for your help and lucky for me, I know where you are!

Nan


-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Joel D Canfield
Sent: Friday, November 30, 2007 1:38 PM
To: thelist at lists.evolt.org
Subject: Re: [thelist] database structure for speedier queries

> 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 results?

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

If not, you know where we are ;)

joel
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester and archives
of thelist go to: http://lists.evolt.org Workers of the Web, evolt !