Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Application / User defined categories?

    I have about 10 required (never to change) categories in my tbAccountCategories table, but after that, the user can add as many
    as he/she wishes. I use an identity field to identify each record. The problem is I want to reserve the first 20 rows for the application and thereafter is for the user. Should I just create 20 rows with the category description field = 'Reserved' ?

    Also, how do I reindex the identity fields that it cleans up gaps and starts back at 0?

    Mike B

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I personally would not rely on Identy fields or any primary key maintaining an uninterupted sequential order. The maintenance is a pain in the a$$.

    Have you thought about adding a flag field to designate which records are permanent? You could create your 20 reserved records with the flag set, and then only allow records without the flag set to be deleted.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by blindman
    I personally would not rely on Identy fields or any primary key maintaining an uninterupted sequential order. The maintenance is a pain in the a$$.

    Have you thought about adding a flag field to designate which records are permanent? You could create your 20 reserved records with the flag set, and then only allow records without the flag set to be deleted.
    Good advice!

    Mike B

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Application / User defined categories?

    Originally posted by MikeB_2k4
    I have about 10 required (never to change) categories in my tbAccountCategories table
    Yeah right....never speak in absoultes..(unless it's on the rocks with a twist)


    ...but after that, the user can add as many
    as he/she wishes. I use an identity field to identify each record.
    AAAAAAAAAAAAAAAAAAAAAHHHHHHHHHHHHH

    You've got an account code, no?


    The problem is I want to reserve the first 20 rows for the application and thereafter is for the user. Should I just create 20 rows with the category description field = 'Reserved' ?
    OK...I'm officially lost...do you just need a type field?


    Also, how do I reindex the identity fields that it cleans up gaps and starts back at 0?
    You don't...(the gap part), you can reset the seed though with [s]DBCC REINDEX[/s](EDIT: What a scrub...CHECKIDENT)..but are you sure you want to do that? What happens to all the existing rows? You'll run in to a dup key problem...

    Cut and paste this to check it out...

    But I think you need to rethingk what you're doing

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY,Col2 char(1))
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
    GO
    
    SELECT * FROM myTable99
    GO
    
    DELETE FROM myTable99 WHERE Col1 = 2
    GO
    
    DBCC CHECKIDENT (myTable99,RESEED,0)
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Last edited by Brett Kaiser; 03-09-04 at 12:03.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    134

    Re: Application / User defined categories?

    [SIZE=1]Originally posted by Brett Kaiser
    Yeah right....never speak in absoultes..(unless it's on the rocks with a twist)
    ummmm, absolute!

    You've got an account code, no?
    Yes and they are unique

    OK...I'm officially lost...do you just need a type field?
    Famouse words of Ed McMahon on Johnny Carson show
    "UhhhHuhhh, you are correct sir!"

    You don't...(the gap part), you can reset the seed though with [s]DBCC REINDEX[/s](EDIT: What a scrub...CHECKIDENT)..but are you sure you want to do that? What happens to all the existing rows? You'll run in to a dup key problem...
    So it is best to just leave it the way it is?

    Cut and paste this to check it out...

    But I think you need to rethingk what you're doing
    Which part, the reindex or the "adding categories"?

    Thanks for the reply Brett

    Mike

Posting Permissions

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