[thelist] database structure for speedier queries

[Ken Schaefer]

I would have to ask how "big" is "very long".

Most modern DBMS are quite capable of searching through tables with millions (or tens of millions) of records given enough RAM, appropriate indexes and good disk I/O (multiple spindles as required). Is your table likely to have more than this?

The downside to having a separate table is that you break normalisation rules, and then querying may become an issue (e.g. to present a consolidated historical view you need to query two separate tables). Of course, only you can say whether this is more efficient or not (it really depends on what types of queries you are going to run, and how frequently).

Cheers
Ken

-----Original Message-----
From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Nan Harbison
Sent: Saturday, 1 December 2007 5:26 AM
To: thelist at lists.evolt.org
Subject: [thelist] database structure for speedier queries

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.

TIA
Nan