[thelist] database structure for speedier queries

[Matt Warden]

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.