New Unique Number In History Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

New Unique Number In History Table

Author

Topic

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-05-23 : 07:40:57

Hello,
I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811

This is the table "People" I have created:

|PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes....

Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.

My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.

As always, thanks for the help!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2008-05-23 : 08:59:18

Well, one old technique is the Seed Table idea - you have a single row for a specific seedtype, in a Seed table, and when creating a new person, you retrieve (and update) the seed value. There are always some issues with this design, especially in high volume "new preson creates" for example, because effectively, only 1 seed value can be created at a point in time.


CREATE TABLE SeedValue
(SeedValue INT NOT NULL,
 SeedType VARCHAR(50))

INSERT INTO SeedValue (seedValue, SeedType) values (1,'Person Id Seed Value')

CREATE PROC GetNewSeed ( @SeedType VARCHAR(50),@SeedValue INT OUTPUT )
AS
BEGIN
 UPDATE SeedValue SET  @SeedValue = SeedValue = SeedValue + 1 WHERE seedType = @SeedType 
END

DECLARE @SeedType VARCHAR(50),@SeedValue INT
SET @SeedType = 'Person Id Seed Value'
EXEC GetNewSeed @SeedType, @SeedValue OUTPUT
SELECT @SeedValue

The usage is USALLY done outside a transaction, since you do not want to lock that ROW for the duration of the transaction, BUT that means that if the PERSON create fails after you've retrieved the Seed, you will not have used all the sequential seeds.

There will be other ways, but I think that this can work in reasonable volues, without having a signifcant design change

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

pootle_flump

1064 Posts

Posted - 2008-05-23 : 09:01:07

Hi Michael

This isn't perhaps a compelling reason all on its own to use this design but it does solve your problem: as mentioned in that thread, I use a normal people table (perhaps with an identity as the PK) and then a separate "audit" or "instance" table. As such, there is only one master person record per person in the people table and all instances of that record in the audit table. I believe there are other advantages too which is why I prefer that design. I suppose solving your problem could be considered a handy side effect of it.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-05-23 : 10:43:10

Thanks for the help. The best I've eneded up with is a table that just holds PersonId like:

|PersonId (PK)| <-Has identity property

Which means I can create foreign key constraints on other tables that include PersonId. I can't really see any other way round it. I could put other fields in the table as mentioned by pootle_flump, but I do not expect to ever be referencing a person without referencing a time anyway as history is very important in this database.

Is there anyway to create a new row in this kind of table? Usually a normal insert to other fields besides the Id field updates the Id, but when the Id is the only field this cannot be done.

Thanks very much!

pootle_flump

1064 Posts

Posted - 2008-05-23 : 12:06:41

[code]INSERT INTO myt(id_col)
SELECT DEFAULT

SELECT SCOPE_IDENTITY()[/code]
Now - without any attributes of course this isn't really much different from Wanderers solution - just that there is more going on under the hood rather than explicitly with code. The RI is just smoke and mirrors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources