Alternative of Identity column?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

Alternative of Identity column??

Author

Topic

Obelixous
Starting Member

3 Posts

Posted - 2003-04-07 : 21:59:33

Within my database, I have two tables (actually there are others as well).

Folder table - contains information corresponding to folders
Document table - contains information corresponding to documents

The structure of the tables is as follows:

Folder table
{
FolderId
FolderName
NextDocId
Other fields...
}

Document table
{
FolderId
DocId
DocumentName
Other fields...
}

Whenever a document is added to a folder, NextDocId corresponding to folder record is incremented and updated and a record is inserted in document table. The performance in such a setup degrades significantly if many clients try to add documents to the same folder.

I have the following solution, make DocId in document table an identity column and remove NextDocIndex from folder table.

Does somebody have another solution for this problem?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-07 : 22:32:31

What exactly does NextDocID mean/do?

Is a FolderName unique?
Is the DocumentName unique per Folder?

DavidM

"SQL-3 is an abomination.."

Obelixous
Starting Member

3 Posts

Posted - 2003-04-08 : 00:45:12

Regarding NextDocId, please consider the following

Contents of Folder table
FolderId FolderName NextDocId
1 Fol1 3
2 Fol2 5

Contents of Document Table
FolderId DocId DocumentName
1 1 Doc11
1 2 Doc12
2 1 Doc21
2 2 Doc22
2 3 Doc23
2 4 Doc24

When a document is to be added to folder with name Fol2, the corresponding record is locked, the current value of NextDocIndex, i.e. 5 is maintained in a local variable and the record is updated using incremented value 6.

i.e. the updated folder table is
2 Fol2 6

Then a record is inserted into the document table as follows:
2 5 Doc25

FolderName and DocumentName are not central to this issue, you can consider them to be unique or non-unique if you want.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-08 : 07:26:36

I think the reason why byrmol is asking is: why do you even need a folderId or documentId field? why not just make the document name and folder name the primary keys? Then you don't have to worry about maintaining extra ID fields.

Of course, if the folder names can or will change often, this may not be the best approach, but if they remain relatively constant I would recommend it.

With the ID fields, and no other constraints, you may end up with two folders or documents with the same name -- and it doesn't seem to me (without knowing what all this is modelling) like you would want that.

- Jeff

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-08 : 15:32:36

Hey, what do you guys think about natural keys versus surrogate keys?

[ducks]

Cheers
-b

adm
Starting Member

3 Posts

Posted - 2003-04-08 : 16:49:10

Maybe I'm missing something, but isn't NewDocID a derived value? It appears at first glance you could just omit it and derive it on-the-fly when necessary.

Obelixous
Starting Member

3 Posts

Posted - 2003-04-08 : 20:55:54

For queries raised by byrmol and jsmith8858

i shall add more details, the structuring is slightly more complex, the folder structure can be hierarchical, so the actual table structure is like this

Folder table
{
FolderId
ParentFolderId
FolderName
NextDocId
Other fields...
}

So, FolderName is unique for a particular ParentFolderId. Similarly DocumentName is unique for a particular FolderId. There are constraints defined on the Folder and Document Table to ensure this.

Also, the FolderId, DocumentId combination is used in other tables to uniquely identify a document. For e.g. I have a page table, containing information about pages in a document.

For aiken

I don't understand the applicability of natuaral and surrogate keys in this system. Perhaps you could elaborate further.

For adm

I could not understand your suggestion, more details / elaboration shall be greatly appreciated.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-09 : 09:27:22

I agree with adm, your NextDocID is actually a misnomer - it should have been named NextDocIndex. You need to know what should be DocID for the newly added document in this folder, right? It can be easily calculated when required, for example:

DECLARE @NextDocIndex INT

SELECT @NextDocIndex = COUNT(DocID) FROM Document WHERE FolderID = @FolderID

IF @NextDocIndex IS NULL
SET @NextDocIndex = 1
ELSE
SET @NextDocIndex = @NextDocIndex + 1

There you have it!

OS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-09 : 11:21:32

I think the question is, then, why not use:

Foldername / Documentname

as your unique key, and reference documents that way? then you don't have to worry about calculating the document ID for each document.

Or, just create an indentity field for your documentID (like you mentioned) and use that. If you need to display nice "document numbers" from 1-n for each document in a folder, if you have an identity documentId you can do it this way:

select document.*, (select count(*) from document a where document.folder = a.folder and document.documentId <= a.documentID) as DocumentNumber
From
Documents

or if you use the names as the primary key:

select document.*, (select count(*) from document a where document.folder = a.folder and document.docName <= a.docName) as DocumentNumber
From
Documents

just some ideas ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources