Author Topic: Database Design Question  (Read 290 times)

0 Members and 1 Guest are viewing this topic.

Offline GBTalk

  • Full Member
  • *
  • Posts: 187
  • Karma: +0/-0
    • View Profile
Database Design Question
« on: August 23, 2008, 06:04:28 PM »
Hi everyone,

I am writing a job tracking application utilizing ASP/MSSQL and I my question has something to do with setting up an archive system.

Consider this my scenario, the database consists of several relational tables that track all of the information about a job. I want to set it up so that if a job is completed, it gets archived. Do you get me?

I mean for sure in the next 2 or 3 years, this system will likely grow to 100,000+ records and i don\'t want the system to crawl like turtle in the long run.

My question is:

(1) should just use a flag to mark the job as archived
(2) make a flat record of the job and move it into one archive table, or
(3) set up archive tables that mirror my production tables and copy the record to the archive tables keeping the relationships intact?

Does anyone have a method that has worked well for them in the past? What will keep my application running the fastest?

Any suggestions would be appreciated.
Many Thanks!

Techronnati | where technology never sleeps

Database Design Question
« on: August 23, 2008, 06:04:28 PM »

Mountain View

Offline JusticeLeague

  • Sr. Member
  • *
  • Posts: 370
  • Karma: +1/-0
    • View Profile
Re: Database Design Question
« Reply #1 on: August 23, 2008, 06:07:29 PM »
I think you can do this by going through either of this:

you could make the column a bit datatype to save on space but it stores 1,0 or null small to the point but you cannot create an index on this column

OR possible to
create the column as tinyint (1 byte , largest value 255) and then you could index it
but the indexing on a heavily duplicated columns is not recommended

you also could create statistics on the flagged column

I strongly believe that you are okay with this but just monitor performance during operations


Related Topics

  Subject / Started by Replies Last post
1 Replies
Last post April 11, 2010, 05:51:34 PM
by Corps
0 Replies
Last post July 16, 2011, 09:33:15 AM
by SprenneAlan
2 Replies
Last post October 20, 2016, 01:29:32 AM
by MoneyRepublic
0 Replies
Last post October 16, 2014, 07:08:15 AM
by h2obubbler
0 Replies
Last post October 19, 2016, 12:38:51 AM
by Corps

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.