Hi Frans,

LLBLGen Pro appears to be a really great product - thanks!

Hence, I am looking forward to using LLBLGen Pro in my next project.

So in my next database design, I'd like to know your opinion on natural vs. surrogate keys.

On the web the opinions are endless, but does your product work better using one technique over the other?

In other words, what are the advantages/disavantages of using natural vs. surrogate keys with respect to your product?

Lastly, if it doesn't really matter which technique is used from your product's perspective, do you still have an opinion on this matter?

--thanks,

I don't think it will matter with LLBLGen Pro.

I always use natural keys, unless they will be composed of more than one column, in this case I go for the surrogate key.

One big advantage of a natural key, is that if you are displaying rows from a child table (FK-Side table), most probably you won't need to join with the main table to get the natural unique field to display, it's already there. This can be highly recognized in Lookup tables.

I mean if you want to display Customers with their Country Names. Now if you have the table Country with PK as a surrogate key, you would have to join with that table to get the Country Name. but if you use the Country name as a PK, you won't have to join, as the Country Name will already exist in the FK in the Customer table.

The debate of natural vs. surrogate keys is an old one, and indeed not related to LLBLGen Pro per se. I tend to favor over to surrogate keys after I've been in the camp for natural keys for a long time. The main reason I'm for surrogate keys is that they guarantee that you don't have to alter a PK value. With natural keys you always run the risk of having to change the PK value because of a typo or, for example when a value has to change even though it was considered unchangeable.

Also, it's often very hard to find a non-changing attribute in an entity which will uniquely identify the entity and also won't change in the future.

Otis wrote:

The debate of natural vs. surrogate keys is an old one, and indeed not related to LLBLGen Pro per se. I tend to favor over to surrogate keys after I've been in the camp for natural keys for a long time. The main reason I'm for surrogate keys is that they guarantee that you don't have to alter a PK value. With natural keys you always run the risk of having to change the PK value because of a typo or, for example when a value has to change even though it was considered unchangeable.

Also, it's often very hard to find a non-changing attribute in an entity which will uniquely identify the entity and also won't change in the future.

So I am getting a split decision from Solutions Design - ouch!

So I am getting a split decision from Solutions Design - ouch!

That's democracy in it's finest form

Anyway I use surrogate keys instead of a composite PK. (instead of having composite FKs in each table that refers to this PK table). I use it also when there is no gurantee of a unique column (and this happens most of the time).

Frans said:

With natural keys you always run the risk of having to change the PK value because of a typo or, for example when a value has to change even though it was considered unchangeable.

With database cascading updates, this won't be a problem.

I find that surrogate keys give more opportunities for code reuse/simplicity outside of the database. Being able to depend on the quantity and and data type of key columns is noice.

Jeff...

TJ wrote:

Otis wrote:

The debate of natural vs. surrogate keys is an old one, and indeed not related to LLBLGen Pro per se. I tend to favor over to surrogate keys after I've been in the camp for natural keys for a long time. The main reason I'm for surrogate keys is that they guarantee that you don't have to alter a PK value. With natural keys you always run the risk of having to change the PK value because of a typo or, for example when a value has to change even though it was considered unchangeable.

Also, it's often very hard to find a non-changing attribute in an entity which will uniquely identify the entity and also won't change in the future.

So I am getting a split decision from Solutions Design - ouch!

It perfectly highlights that this aspect of database programming is something that's been debated for years, and is likely not going to end anytimg soon

I prefer Natural child birth , yet I really prefer Surrogate keys when it comes to database design. So, I am somewhat split in the global sense but I'm very one minded when it comes to the database thing.

Hope this helps

Fishy

Walaa wrote:

So I am getting a split decision from Solutions Design - ouch!

That's democracy in it's finest form

Anyway I use surrogate keys instead of a composite PK. (instead of having composite FKs in each table that refers to this PK table). I use it also when there is no gurantee of a unique column (and this happens most of the time).

Frans said:

With natural keys you always run the risk of having to change the PK value because of a typo or, for example when a value has to change even though it was considered unchangeable.

With database cascading updates, this won't be a problem.

Pardon me, but it looks like Solutions Design is really on the same sheet of music after all - that is, most of the time!

PS - It's good to be King

Fishy wrote:

I prefer Natural child birth , yet I really prefer Surrogate keys when it comes to database design. So, I am somewhat split in the global sense but I'm very one minded when it comes to the database thing.

Hope this helps

Fishy

Fishy, I prefer Tuna child birth - but whatever floats your boat!

Otis wrote:

The debate of natural vs. surrogate keys is an old one, and indeed not related to LLBLGen Pro per se. I tend to favor over to surrogate keys after I've been in the camp for natural keys for a long time. The main reason I'm for surrogate keys is that they guarantee that you don't have to alter a PK value. With natural keys you always run the risk of having to change the PK value because of a typo or, for example when a value has to change even though it was considered unchangeable.

Also, it's often very hard to find a non-changing attribute in an entity which will uniquely identify the entity and also won't change in the future.

I'm sorry to ressurect an old thread, but it suddenly is pertinent to me

I have a question about this debate that I can't seem to find an answer for. One of the main arguments I read for the use of surrogate keys is because of updates - changes to the primary key. Yet, doesn't a Cascade Update solve that problem?

Shara wrote:

Otis wrote:

The debate of natural vs. surrogate keys is an old one, and indeed not related to LLBLGen Pro per se. I tend to favor over to surrogate keys after I've been in the camp for natural keys for a long time. The main reason I'm for surrogate keys is that they guarantee that you don't have to alter a PK value. With natural keys you always run the risk of having to change the PK value because of a typo or, for example when a value has to change even though it was considered unchangeable.

Also, it's often very hard to find a non-changing attribute in an entity which will uniquely identify the entity and also won't change in the future.

I'm sorry to ressurect an old thread, but it suddenly is pertinent to me

I have a question about this debate that I can't seem to find an answer for. One of the main arguments I read for the use of surrogate keys is because of updates - changes to the primary key. Yet, doesn't a Cascade Update solve that problem?

Cascading an update (and other triggers) can invalidate data that users may have open, because the PKs that they know about may change. It would be nice if you could cascade the update to the active users, but I don't know a good way to do this.

I'll put my .02 USD in here. I'm for surrogate keys because:

1) No worries about the PK value changing. It is guaranteed to be a "stable" key that won't ever change, thus eliminating the need for triggers or CASCADE UPDATE 2) Usually the surrogate key is an integer value, which will perform better (generally) in joins than character values, increasing (even if slightly) the performance of the application

This approach DOES mean that you still need to put a unique index (alternate key) on your tables to guarantee entity integrity, which a LOT of people overlook. i.e. it's not enough to create a surrogate key by itself. Otherwise you could have a table that looks like this:

ID DESCRIPTION

1 foo 2 foo 3 foo 4 foo 5 foo

mikeg22 wrote:

Cascading an update (and other triggers) can invalidate data that users may have open, because the PKs that they know about may change. It would be nice if you could cascade the update to the active users, but I don't know a good way to do this.

Ah, see I knew there was something I was missing. That makes sense. It would certainly compound concurrency issues which already exist.

Thanks!

Shara wrote:

mikeg22 wrote:

Cascading an update (and other triggers) can invalidate data that users may have open, because the PKs that they know about may change. It would be nice if you could cascade the update to the active users, but I don't know a good way to do this.

Ah, see I knew there was something I was missing. That makes sense. It would certainly compound concurrency issues which already exist.

Thanks!

That said, we use natural keys for lookup tables that should never, ever change. Maybe delete or insert, but the state California shouldn't ever change its name. If there is something like this whose name might change, its likely that you would need the old value anyway, and it should be a table that records changes as inserts (with an Effective Date).

Chester said:

2) Usually the surrogate key is an integer value, which will perform better (generally) in joins than character values, increasing (even if slightly) the performance of the application

Speaking about simple lookup tables holding names only, When using natural keys, you will not need the join, as the Name value would be present in the FK at the the FK-Side table.

mikeg22 said:

That said, we use natural keys for lookup tables that should never, ever change. Maybe delete or insert, but the state California shouldn't ever change its name.

And if they changed the state name, a cascade update will do the trick. Comparing the probability of updates in lookup tables, with the number of times you need to join when selecting data from a FK-Side table (when using surrogate keys). Then it appears to me that a cascade update every now and then is not costly, when thinking of eliminating the joins from the selects that might occur at every second.

We use natural keys, but each table has also a column RecordId that is an identity colum with unique constraint which is used for example in UI for binding. Works pretty good for us.