| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2007-09-24 : 14:15:36
|
| Hello,I normally use Uniqueidentifier or sometimes an Indentity column for my table primary keys. However, I have an interesting situation and I am considering NOT using uniqueidentifier or identity for the primary key.On this table, there needs to be a column for an employee's id. However, the ID can be in one of three ranges depending on the employee's status (Full time, contractor or something else..I forget what the 3rd one is). So, a full time employee can be anything from 100 to 20000, the contractor can be 40000 to 100000, and the third type is 120000 to 150000. I was considering using another table that would store the next or last number for each type...so a column for each type. Then, upon a save (via the web UI) the new employee would be inserted...and we'd pass the new employee id (based on their type) as the value to an insert stored proc. I figured on making this column the primary key rather than creating an artificial key. Does anyone have any pros or cons with this type of model?Thanks - will |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-24 : 14:20:49
|
| The biggest con IMHO will be that you are creating a hotspot for contention on that ID generating table. Depending on how many inserts will be done it may or may not be a problem.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-09-24 : 14:40:19
|
quote:
Originally posted by dinakar
The biggest con IMHO will be that you are creating a hotspot for contention on that ID generating table. Depending on how many inserts will be done it may or may not be a problem.
Good point. But, I am not sure of another way to handle this situation. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-24 : 14:41:52
|
| Do you create employess everyday/hour/minute? Otherwise it may not be an issue.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-09-24 : 14:52:30
|
quote:
Originally posted by dinakar
Do you create employess everyday/hour/minute? Otherwise it may not be an issue.
I'd say that there would be a surge of employee creation at the beginning....but probably only 2 or 3 administrators creating them around the same time. After that...just an occasional employee would be created. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-24 : 15:01:10
|
| You might want to test the scenario out then.. there's really no other way to find out..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-09-24 : 15:37:32
|
quote:
Originally posted by dinakar
You might want to test the scenario out then.. there's really no other way to find out..
Thanks for the advice. Guess that is my next step. - will |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2007-09-28 : 21:14:14
|
| >> I normally use Uniqueidentifier or sometimes an IDENTITY column ..<<That is completely non-relational Do you know a what a key is? You can google some of my longs rants, but the real question is: What do you want to use IDENTITY for? The usual things are:a) A fake identifier when the newbie does not know what the industry standards or company accounting practice for a relational key are. It cannot be a key ** by definition**. It is not a subset of attributes of an entity in the data model. There will be no validation or verification, but speed is more important than data integrity to a newbie. b) A fake surrogate key when the SQL product does not have a surrogate mechanism. You are doing by hand what the SQL Server should be doing for you and hiding from you. But it is a bitch to write a major component of an RDBMS in T-SQL -- and it is impossible to isolate it from all the users, present and future. (Teradata uses hashing, SAND uses compressed bit vectors and other VLDB product have their own tricks to get true surrogates; SQL Server does not).c) A sequence source for physical display of result set rows at the last minute. You add it to the SELECT list in the cursor, which has an ORDER BY clause. Since this is a cursor and not a table, I do not mind it here. But the front end really ought to handle this line numbering stuff. These days you can use "ROW_NUMBER() OVER (ORDER BY x) AS rn" if you need to see a sequence in a row, and it will be a true (no gaps, no weird starting number) sequence. d) A sequential part of an encoding. The gaps and portabilty are still huge problems. The "MAX(foo_nbr) +1" trick is good. But a better designed encoding and policy is better. I know that you want a "Magical, one-size-fits-all" answer. It does not exist. I tell the "ID-iots" that want it that God put a 17-digit Hebrew number on *everything* in Creation and all you have to do to build a database is read it! Sounds stupid, doesn't it? Just as stupid as putting an IDENTITY, isn't it? >> the ID can be in one of three ranges depending on the employee's status <<Another design error! If you have a copy of Chris Date's books, look up his Ziti Motors example. You have two different attributes crammed into one attribute. --CELKO--Joe Celko, SQL Guru |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-09-28 : 21:34:56
|
| Dude! Cut down on the Jolt and give us all a break. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-29 : 05:00:17
|
| 3 tables and union them together in a VIEW?Other than the hotspot thing, one of the issues I would be concerned about is how a new range can be added when the existing one is full.I hate these customer-designed overloaded-key-solutions. I'm converting some data today. They have XXX_YYY_ZZZ type styles for their product codes. Product group, Material and Colour. Except that someone made a type in the YYY part on a range of products. So I've mechanically generated all the choices (for some other "massaging" of products they want to do, only I now have to add a bunch of CASE statements to cater for the typos. If they have Group, Material and Colour in three separate columns they'd be able to fix those typos ...One thing to consider about the "I was considering using another table that would store the next or last number for each type" is to allow allocation of batches of numbers. So if, for example, you are bulk inserting 100 employees you can get the next number, increment by 100, and then use that range for your import, without having to increment it 100 times. That may help with the hot-spot issue."I normally use Uniqueidentifier"Note that they can cause lousy index performance during insertsNice to see you here Joe!Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-29 : 17:17:46
|
| It is a mistake to blend the status of an employee with the record's surrogate key. A surrogate key, by definition, should not convey any particular information about the data it identifies. Are you going to change the employee ID if the employee's status changes?Use a second column to designate the employee status.e4 d5 xd5 Nf6 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-30 : 05:21:18
|
"Use a second column to designate the employee status."+1 to that  |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-10-01 : 10:59:58
|
| According to the customer, the employee status/type has never changed. The idea of another column to denote their type sounds good. However, there still needs to be a way for the system to generate the incrementing Employee ID based on their type. This is their business rule, not mine. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-01 : 11:34:14
|
| You are probably stuck with what they already have, but the way I would tackle it would be to have a status column of 1=Full time, 2=contractor, 3=OtherThing, and an IDENTITY for the ID, and then "display" the ID with the Status (prefix, or suffix) for the "Human Readible Form"Kristen |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-10-01 : 12:00:14
|
| Yes, I am stuck with what they already have "designed". It is unfortunate and I have tried to convince them to change...but this is just the way that they operate.Your idea of a single identity column combined with a employee status/type column is intriguing. I think that this is my favorite answer so far! I will go through a few scenarios and make sure that it will work with what they need.thanks - will |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-01 : 12:14:07
|
| As a last-ditch you can have "their number" and "your number".Yours is database-internal only, and can be (say) Identity.There's is external, and you can generate that in some other way, perhaps using a trigger when that column IS NULL and using the Status to decide which "range" to allocate from. You could do something like MAX(TheirNumber) WHERE Status = "The Status For This Employee)But that's kinda where you came in, eh? Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-10-03 : 07:22:35
|
| Agreed on the "this is not ideal" and fair enough on the "this is the business requirement".As another idea that can use proprietry SQL Server number generation you could have a normal key that you cascade throughout your database for foreign keys. You would then create three tables, related one to one to your person table. Each uses a super key to relate to the person table (person_internal_id, person_type_code). Each table represents one person type. By putting a check constraint on the person_type_code in these three tables you can ensure integrity in so far as a person can only be one type. You also have an identity column on each of these three tables, incorporating your business logic in the DDL (e.g. person_exposed_id INT IDENTITY(40000, 1) in the contractor person type table).Create a view unioning these three tables and relating them to the person table. Short of a trigger in the person table to ensure that there is always a related record, and to handle peron type changes should you need to, you would then be good to go.An alternative anyway. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
Primary Key question