Page 1 of 6 123 ... LastLast
Results 1 to 15 of 86
  1. #1
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34

    Smile Unanswered: Database Design Question

    I am writing a job tracking app with ASP/MSSQL and I have a question about setting up an archive system.

    For my app, 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.

    Over the next 2 or 3 years, this system will likely grow to 100,000+ records.

    My question is, should I (1) 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 app running the fastest? Any suggestions would be appreciated. Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i always prefer a single table with the jobs flagged as not active (1 or 0)
    it makes for simpler querying later with just
    select * from table1
    where active is 1
    and
    when you want to see completed tasks
    select * from table1
    where active is 0

    (less tables, less joins etc,.... )

  3. #3
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    Cool ... thanks!

    Do you think 100,000+ records somewhere down the line will be a problem when running reports or doing searches if I just use a flag? I'm trying to design this w/ optimized tuning in mind because I don't know how long it will be in place.

    When I flag a job as complete (bit 0,1) and then query w/

    WHERE open=(0 or 1)

    It still has to check every record correct? How many records before there is a noticable difference in the speed of the app?

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    two ways to think 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
    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'm of the mind that you should be okay with this but just monitor performance during operations

  5. #5
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    Thanks for your ideas!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The optimizer will likely not use an index on a boolean column, regardless of whether it is bit, tiny int, or even char(1). There just isn't sufficient cardinality to make it worthwhile.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    true
    this is an area where i have (gulp!) envied ORACLE
    they have bitmap indexes that you can use on heavily duplicated columns like gender and yes\no

    oh well maybe in yukon.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, but that's not the point.

    You could create an index on boolean values stored in a tinyint field, but the optimizer would likely ignore it because its not any more efficient than using a table scan. The cardinality is likely so low (high? I can never remember...) that the small performance boost gained by using the index is offset by the overhead of accessing the index.

    I'm not an Oracle DBA, but I don't see how Oracle would gain much from indexing boolean values either.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    yes and using bitmap indexes on yes no or gender or small sets like rating can give you a boost
    but you are right about the boolean but still i prefer the 1/0 flag to the splitting up of tables

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I agree. In most cases (not all) a flag is better than splitting the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    And the rambling goes on. Well, I don't know who would come up with an idea on creating an index just on a Gender or Yes/No field. And you guys keep on going on it. Kind of silly, would you really consider doing it? Its value WILL come to play if such a field is combined with something else.

    And another thing, - YOU CAN CREATE AN INDEX ON A BIT FIELD, even by itself.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes and no.

    You can't create an index on a bit field through Enterprise Manager's table design form.

    Also, Books Online states:

    "Columns of type bit cannot have indexes on them. "

    But you can create the index using SQL:

    CREATE INDEX [IX_BitTest] ON [dbo].[YourTable]([BitValue]) ON [PRIMARY]
    GO

    ...so there is a loophole or inconsistency in SQL server regarding bit values.

    Lot's of people come up with the idea of indexing boolean fields because they don't have an in depth understanding of how SQL Server works. That's why they come to dbforums for advice, not ridicule Bob.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by blindman
    ...Lot's of people come up with the idea of indexing boolean fields because they don't have an in depth understanding of how SQL Server works. That's why they come to dbforums for advice, not ridicule Bob.
    Hey, you started first, I just try to survive here. Remember your comments about my inability to understand English? Help me out here, - weren't you ridiculing me?

    Somet things I found funny, others I saw as an insult, but in neither of those posts were you even attempting to hide or retract your intent to ridicule. I guess you yourself can't take your own medicine, hey?!

    Originally posted by blindman
    ...You can't create an index on a bit field through Enterprise Manager's table design form.

    Also, Books Online states:

    "Columns of type bit cannot have indexes on them. "

    Which Books Online are you reading, Lindman? Get on with the program, everybody else did!

    SQL Server Books Online January 2004 Update

  14. #14
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    YEAH BOB!!!!

    rdjabarov does insulted.....

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Always go with your strength.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •