Read http://it.toolbox.com/blogs/database-soup/primary-keyvil-par... (an often-quoted series of blog posts on why surrogate numeric keys are evil).

In general, if you have an entity with a well-defined natural key (users and their logins, shipments and their tracking codes), why would you use an autogenerated key that's meaningless?

Case in point, my current schema, designed from scratch. Users can have multiple dashboards, each dashboard has a label. A user cannot have multiple dashboards with the same label, but the two users can of course use the same label for their dashboards (like "sales" or "mydashboard"). The dashboard's table primary key is (username, label) - that pair uniquely identifies a dashboard, so it's a perfect PK candidate.

Also, as already noted in one response, many-to-many relationships are typically modelled by an intermediate table that has foreign keys to both sides of the many-to-many, and its foreign key is the sum of these foreign keys.

EDIT: added a concrete example

Surrogate PKs also allow you to not have to worry about ON UPDATE cascades. A gig I did a few years ago we decided to use some natural PKs, but as is reality, these PKs had to change all the time, not as much as part of regular operation, but more as we modified how the site worked and referenced information as well as when we had to go in and correct data that was mis-labeled (it was a content aggregation site sucking in many gigs of data per day). Plus the natural PKs made our indexes, primary key as well as all the referencing foreign key indexes, huge and poorly performing.

Incidentally, part III of the keyvil blog posts talks about reasons to use surrogates :)

As always, nothing's black or white, sometimes surrogates are OK, sometimes they're not. But I see schemas abusing surrogates much more often than schemas abusing natural keys...

What if you want to offer an option to change user login name later? This is a headache if the rest of your database points to your UID by name.

Same thing if you are making a book database. Sure, in theory the ISBN is a unique book identifier, but what if this somehow doesn't hold (ie, same ISBN but different covers). When you use only the 'natural' identifier you're stuck. Or what if someone typed the ISBN wrong? In a distributed system it can be incredibly difficult to update all the references, for example in users's shopping carts.

For these reasons the canonical DB design advice seems to be to use "meaningless" unique identifiers for objects (doesn't matter if this is autoincrement or UUID etc).

If you have a distributed system, generating unique identifiers and keeping referential consistency across the distributed parts is already a difficult problem. But that's a digression. If I ever get so many users I'll have to partition the user table across databases, I'll have much more trouble that just their IDs (and I'll be rich :) )

You ask, "In general, if you have an entity with a well-defined natural key (users and their logins, shipments and their tracking codes), why would you use an autogenerated key that's meaningless?"

I like my approach because of reduced cog. overload. When I'm designing my database, or writing queries that jump through several tables, I always know that my foreign key in table Person [1] is going to be of the form fk_organistaion_id. All I have to do is remember table names and relationships. If I also had to remember arbitrary keys, that would increase memory overload. When I'm doing consulting and working on several applications in parallel that's less practical. Similarly, I always know I can store a reference to an ID column and know I'm it will be there and uniquely identify a row.

I don't think the many-to-many example really answers to my question. The issue of whether it is commonly done is different to whether it is good practice.

I can imagine there might be performance advantages to having a composite primary key for a join table. But - I expect you could get equivalent performance on id|fk_a_id|fk_b_id by adding an index. This comes back to the principle of - do you write your code principally to be run, or to be read. I have a memory like a sieve and write to be read.

I think composite primary keys are done for the wrong reason at times.

The problem in the example in the article linked to is not a poor use of keys, it's a poor design of system. They're trying to enforce type at the schema level. Though he doesn't spell it out, I expect the reason they're getting bizarreness is because they either have people interacting with the database at too-low a level, or because they have multiple applications hung off it. More on this in a second. [2]

Similarly, I think your attempt to use the database to enforcing typing rules will work at some levels but runs out. For example - imagine if a user was only alowed to have three labels. Or that the label musn't have any spaces in it.

While you can delve into triggers [3] I think it's misguided to think you can enforce a general sense of business logic at the database level. That stuff should be done by an application surrounding the database. Then all interaction with the database should go through that one-and-only-one system that owns the database.

Databases have some type information but it's very primitive and inadequate for all but the most simple of scenarios. I've found that once you acknowledge that you start designing schemas and the systems around them in a way that is very different to what you'd learn in the Oracle course.

--

[1] Another quirk of my style - singular table names - because it makes it easier to wrap ORMs around it without having code that reads as bizarre

[2] The second example is riduculous. They assigned an int to the wrong place. That's not a problem with schema design, that's just a stupid mistake.

[3] I've done plenty of this work on hairy enterprise systems

It's often not possible to model all business rules in SQL, but the more you manage to cram into the database, the less probable it is you'll end up with inconsistent data.

Often there are dozens of applications (or independent modules) using the database. Keeping the business rules (like "every user has to have a first name" or "no two customers can rent the same car at the same time") in a centralised place helps a lot with keeping everything in order. It's often not possible to limit the interaction with the database to a one-and-only system (imagine different teams writing different parts of the system, app changes, outsourced integrations, etc)

So, if a label can't have spaces in it, that's easy - slap on a check constraint that enforces it. Then you know that even if the new hire writes a "change my label" funcionality and forgets to enforce the no spaces rule, she won't screw up your data.

If a user can have at most three labels, things get more interesting. Offhand I'm not sure how I'd model that and it actually is a difficult problem, best solved with triggers IMHO, as you have better control over locking and can guard yourself against concurrency issues.

Anyway, the discussion on "all logic in DB" vs "all logic in the app" is part of the neverending struggle between DBAs and app programmers...

    Often there are dozens of applications (or independent
    modules) using the database

It is fundamentally bad design to have more than one application dependent on a database, and you're right, it's used everywhere. If I do a PhD one day, it will be on this topic. It's the number one stupid design in enterprise computing.

I've spent years trying to write a logic framework to surround schemas to make it possible to do the functionality of pl/sql, but with stateful sessions. Every eighteen months I have a new go at it and rewrite.

I have the ideas working, and use the patterns now, but haven't managed to create a framework of it that would be usable to anyone else.

    Anyway, the discussion on "all logic in DB" vs "all
    logic in the app" is part of the neverending struggle
    between DBAs and app programmers...

Assuming so, I think composite key is premature optimisation.

SQLAlchemy can also map to views or selects - if a view or select is against multiple tables, now you have a composite primary key by nature. A core piece of SQLAlchemy is that we select rows from many things besides physical tables.

Your sentence is a key part about the differences between Django ORM and SqlALchemy. Every article should include it.

  table a (
    id integer primary key,
    data text
  )

  table b (
    id integer primary key,
    postal_code text
  )

  select *
  from a inner join b on a.id = b.id
  where b.postal_code = '97000'

  NESTED LOOP
    TABLE B
      WHERE postal_code = '97000'
    TABLE A

1. Use postal_code as the key.

2. Perform two queries:

  select * from b where postal_code = '97000';

  select * from a where id in (*); -- or similar

3. Use Oracle which has something called a 'star join' which basically does option #2 automatically for you.

Obviously this is the simple case. Things get more complex the more tables you add in.

The query plan would be either:

  NESTED LOOP
    TABLE B
      WHERE postal_code = '97000'
    TABLE A (index scans)

  TABLE A (1 full table scan)
  TABLE B
      WHERE postal_code = '97000'