Object Id Ranges - SQL Server Forums

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

Object Id Ranges

Author

Topic

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-10-01 : 09:55:45

I've heard that there can be some benifts of using a range of Ids for certain objects. For instance, if I have a table of people, then I give them Ids 1-1000, then for a table of cars 1001-2000.

Has anyone heard of any benefits or negatives attached to this approach?

As usual, greatful for the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:06:13

It is not a good idea. What happens in the future when there are not enough IDs left for cars?
If you insist of mixing people and cars in same people (like an EAV design) you can make something like this

DECLARE	@objectType TABLE
	(
		typeID TINYINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		typeCode VARCHAR(3),
		typeName VARCHAR(200)
	)

INSERT	@objectCode
SELECT	'pop', 'People' UNION ALL
SELECT	'car', 'Car'

DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1),
		typeID TINYINT REFERENCES @objectType (typeID)
	)

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-01 : 10:51:07

Keep different entities in different tables, and don't worry about the ID numbers.

Makes everything easier to keep track of. Cars don't have Social Security numbers or hair color, and people don't have model numbers, engine size, or license tag numbers.

CODO ERGO SUM

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-10-01 : 11:23:04

That was my thinking actually. Why I ask is because the company I'm working for already has this system in place and wants Ids for all new tables/entities to be in particular ranges. They claim to have ranges large enough to support objects being added at the same rate for quite a few years into the future so don't see a problem with this system.

We actually deal with a database that includes different objects that connect to each other physically through pipelines. These connections are stored in the pipelines table sepecifically with two columns; a ToId and a FromId. As each object's Id is in a particular range and therefore unique compared to other objects, any object can be connected by a pipeline to another object.

This could be the single benifit for ranges. I suspect there is another way to deal with this issue, but this should probably be in another thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources