Friday, February 24, 2012

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 BI 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.|||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|||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 DBCC REINDEX(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

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|||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(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

No comments:

Post a Comment