Ken, Matt and Joshua, This is a MySQL db, hosted at Godaddy. I don't imagine we will ever have millions of records. I am going on the assumption that there could be at best be a few hundred thousand. We have limited RAM and Godaddy can't tell me how much, I suspect that is because it is being shared with other websites on the shared hosting. We can easily upgrade to premium hosting, with fewer websites on the server. I know this breaks normalization rules. But I thought it might better. It sounds like adding the archived field and setting it to null or not null is the best solution. It would make it easier to query for both current and archived projects. The queries are simple, but could be frequent. I won't figure out customer behavior until we get this site up and runnning. Thanks for all your help! Nan -----Original Message----- From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Ken Schaefer Sent: Saturday, December 01, 2007 1:13 AM To: thelist at lists.evolt.org Subject: Re: [thelist] database structure for speedier queries 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 -- * * 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 !