Sequences

[slimdave]

Originally posted by onlysimon
object-relational database systems are not OLTP systems. There is no need to tune performance...

Get those asbestos underpants on, my friend.

[marist89]

Originally posted by onlysimon
There is no need to tune performance, why add extra columns?

...until, of course, it goes into production. This is one of the most absurd statements I've ever heard (although ashley75 still holds the top spot!)

Take a database of NYSE companies. maybe the ticker is the key on one table, yet the ticker is not unique since companies change/reuse tickers, so another table maps ticker to ISIN... ISIN is a specific issue to a country etc.. so the referential integrity is best maintained using 'real' keys rather than psuedo keys.

Actually, that's a better argument of WHY to use a surrogate key.

[marist89]

Originally posted by slimdave
Get those asbestos underpants on, my friend.

ROTFL!!! You knew it was coming, didn't you...

[onlysimon]

until, of course, it goes into production. This is one of the most absurd statements I've ever heard

Ok maybe a rather poor use of words. I mean a reporting system, where there is a query every second or so, not a banking system.

[TomazZ]

Originally posted by edli
Can you show me how?

If you have a lot of tables that need or you have decided for whatever reason (right SANJAY?!) to have a surrogate primary key based on a sequence, think of creating a meta-table to list all of them (with PK column names, maybe). On this you can base a procedure which would create sequence for each table.
The procedure can create also triggers on insert to take care of automatically filling the keys.

[SANJAY_G]

My point was when you have natural business keys available and are not subject to change why to use surrogate keys ?

For example if I have empno in the emp table and it is inmutable I will choose it as primary key, Why to add another column and populate with sequence?

[chrisrlong]

My, but I've missed a lot of replies.

Originally posted by onlysimon
object-relational database systems are not OLTP systems. There is no need to tune performance, why add extra columns?

You've got to be joking. I must say, that is a type of database that I haven't work on - one where the performance was unimportant.

Also, I don't even see how you can say that an object-relational implementation cannot be of an OLTP type. They are completely different variables. But what correlation does exist is that most object-relational databases are OLTP, I would think. Not many people go to the trouble of creating an object layer to report against, although it's certainly possible.

However, it also sounds like you may have mis-read me. I was speaking about OLTP and OLAP where the rule applies. Actually, OLTP is what I am always preaching about. DSS is a different matter, as de-normalizing rules the day and keys are in short supply anyway. Personally, I would likely be copying the PKs from an OLTP system most times anyway, but that's getting off-topic.

Originally posted by onlysimon
The reason not to use them is when a high level (coarse grained) model, exists concurrently with a finer grained model.

I fully admit, I haven't done any huge warehouses with multiple granularities, so you may certainly be correct there, assuming that is what you are referring to.

Originally posted by onlysimon
Remember than many systems contain a great deal of historical data (I worked on one that had data from 1960s) so theres a bias against change also.

Sure, but again off-topic, I believe. How to do things properly often conflicts with real-world special-case restrictions. The question was un-encumbered by such specifics, so we have been speaking in general terms. As a rule, surrogate keys are best in OLTP. There are always exceptions to any rule, but surrogate keys should be the rule. As a rule, you should wear your seat-belt, but if the belt's broken - whatcha gonna do?

Moving on...

Originally posted by marist89
Personally, I don't adhere to the Conventional Wisdom that every table should have a surrogate PK.

Heh - I know - we've been here before . But I've never heard it referred to as 'conventional wisdom'. I've been preaching it for many years now, but it's come from my own experiences and reasoning - I had never heard of it as conventional wisdom - I'll have to come up with a new one now, I hate being in the mainstream

Originally posted by marist89
When you are dealing with large datasets that need to be partitioned by range, surrogate keys are impractical. The need to maintain a primary key with a non-partitioned unique index defeats the whole purpose of partitioning.

And we've had this one before, which I deferred to Jurij on. He somewhat agreed and somewhat disagreed, if I remember, but it certainly seemed like it may have been one of the special cases.

Originally posted by onlysimon
Ok maybe a rather poor use of words. I mean a reporting system, where there is a query every second or so, not a banking system.

Again - my rule doesn't necessarily apply to DSS. But trust me, performance is still of major importance in DSS

Originally posted by TomazZ
... think of creating a meta-table to list all of them (with PK column names, maybe). On this you can base a procedure which would create sequence for each table.
The procedure can create also triggers on insert to take care of automatically filling the keys.

Two points here. I use ERwin and I have a template set up where all you have to do is set some flags to get all manner of generated code for you, including sequences and triggers. Just saying that automation is always nice!

As for the trigger, I also fully agree. More OLTP rules: De-normalized fields and surrogate PKs should be trigger-populated.

Originally posted by SANJAY_G
My point was when you have natural business keys available and are not subject to change why to use surrogate keys ?

For example if I have empno in the emp table and it is inmutable I will choose it as primary key, Why to add another column and populate with sequence?

Another special case... IF you have an existing key that is NOT user-entered and will NEVER change and is already a TIGHT numeric (doesn't start at a billion, say), then you can probably use that field as the PK. But be realistic, how many times does all that happen. More likely, the employees have some emp-id that they are given, or use SSN, but the value is input by a human. Right there's a problem. Humans can make mistakes and mistakes have to be fixed. So if somebody entered the value wrong, and the error wasn't found until maybe a couple months later when there are now perhaps hundreds of records in a dozen tables that use tha key - now fixing it is a major PITA. Whereas if you had a surrogate key instead, it is a single update of a single column in a single record.

Again, go read the arguments I linked to earlier - I go through most of this.

Wow.

Good god, man - don't you ever shut up?

Aye-aye. Shutting up, sir.

- Chris