[Tenhundfeld]

I actually prefer just using id as the primary key, and I like the explicitness of seeing the table/alias before the column in complex queries. I don't care too much about typing it out; reservation.id isn't longer than reservation_id, and the savings of USING vs ON seem minimal. I also don't care about deduplicating that one field, as I'll likely need to consider other duplicate fields in the results, like created_at or description.

But I can see arguments in both directions. I think the big thing is consistency. If you consistently use <table name>_id as the PK and your tooling doesn't fight it, then cool.

I do feel like this benefit breaks down rather quickly though. For example, if you wanted to track who created a guest record, you probably wouldn't name the column user_id as it's ambiguous. So you might use creator_id to signify it points to the user who created the guest, not to the user invited to the reservation – thus negating your ability to join to a user table via USING.

That's not to say it's a bad tip, just because there are cases where it doesn't fit. Definitely worth knowing. Thanks for sharing.

[jessedhillon]

When you have to join on composite PKs, it's easier to appreciate the parent's advice.

[erpellan]

Some things that I'd love to see as options in rdbms:

Enforced globally unique column names - natural joins on everything.

Throw an exception if a query requires a table scan (bonus points if it printed the statement that would create the appropriate index).

Translate all update and delete statements into inserts (enforced immutable data + versioning).

[bmdavi3]

For queries returning results from a majority of rows in the table, won't a sequential table scan be faster than an index scan?

[jpitz]

For non-clustered indices, yes.

[jeremysmyth]

and if the order is not important. Reordering large numbers of rows can be a very expensive operation.

[moron4hire]

You can force the last one but just dropping the delete and update permissions from your role.

[Tenhundfeld]

Yeah, I hear that. I also tend to always use surrogate keys. It can have performance implications, but that's typically not my biggest concern.

EDIT: I guess that doesn't really address your point of needing to join on a composite natural key though.

[ahachete]

Do you know SSSKA? Society to Stop Surrogate Key Abuse: http://www.slideshare.net/PGExperts/keyvil-lightning-talk?qi... ;)

[Tenhundfeld]

I didn't know about it, but I don't find those slides compelling, not at all. For example, their response to the "keys shouldn't change" is "what is CASCADE for then?". That seems like a naive view of data.

It ignores things like integrating with other systems. If I export my data to an OLAP database, I can't just cascade that key change through it without manual effort.

It also ignores managing historical data. Maybe I don't want that natural key update cascaded everywhere. In their example of hotel room number, if that public-facing number changed (which does happen), I wouldn't necessarily want that cascaded everywhere. For example, I might need past reservations/invoices from before the change to keep the old room number, to match all correspondence with the guest, etc. Okay, so maybe you don't automatically cascade everywhere, but then you need some way to link that old room number and the new room number in all of your reporting.

Bleh. That's a lot of headache that can be greatly minimized with a surrogate key, for very little drawback in my experience. Sure, I can imagine scenarios where the performance impact or additional storage could actually be a real negative, but for your average CRUD app, I think surrogate keys add way, way more value than cost.

[ahachete]

Sure, there's CASCADE, but as you pointed out it has several drawbacks.

That's why ideally natural keys should be immutable. That has many advantages over surrogate keys.

There's a follow up post (posts) to this presentation: http://www.databasesoup.com/2015/03/primary-keyvil-reprised.... where many problems caused by the abuse of surrogate keys are illustrated.

But the main point is simple: surrogate keys (abuse) is an easy way to data corruption. Not in the sense of durability as in ACID, but in the sense of humans (manually or by bugs in the software) duplicating information that is not enforced to be unique thanks to using non-sense surrogate keys as the uniqueness criteria.

[Tenhundfeld]

If a combination of data should always be unique in a table, you should put a unique constraint/index on those columns to ensure that data integrity. I think that's mostly unrelated to the surrogate key vs natural key debate.

Your point about immutable keys is important, I think. A "natural key" can mean different things, as I understand the term. You could call a person's social security number a natural key, which for the record is a bad idea as they can change . But you could also call the combination of user_id and reservation_id a composite natural key, which is immutable and not so bad in my book. I'd still use a surrogate key though.

It might seem like I care about this more than I actually do. I've just found over the years of writing systems that I fairly often regretted not having a surrogate key on a table, but I've never once regretted adding a surrogate key that ended up not being essential.

What I've seen happen is that the data model expands/changes and I need to reference a join table. I might just start out referencing it by the composite natural key. Then the model changes again, and now I need to add another piece of data that changes the natural key. For example, maybe the natural key was (user_id, reservation_id), but now it's (user_id, reservation_id, membership_type_id). Now I need to think about anywhere I referenced that join table by the natural key.