3

What is the ID antipattern and why / under which circumstances is it considered an antipattern?

The id antipattern is having a unique ID column in each table. The ID is generated for each new record. Why is this an antipattern? – Andrew Wolfe 11 mins ago

2 Answers 2

8

The id antipattern is having a unique ID column in each table without requiring (or often discouraging) the application of alternate unique keys. The ID is generated for each new record. Why is this an antipattern?

Numeric surrogate keys are fine, including when they are singleton primary keys.

However, every time I have seen a schema in which EVERY table has this id column, the designer wanted the row to be just like a memory handle. This avoids the crucial question of primary key design and fosters repeated insertion of contradictory redundant data. It makes database designers tear their hair out.

In the memory of a running application, memory handles like this are transient. They are dead when the application exits, and no restart of the application tries to use them again. This makes pointers and object handles useful in that context.

Similarly, storing a handle/pointer to one object as a piece of another object makes sense. It imposes ownership of the one object over the other.

These handles have an intrinsic meaning only insofar as some client came in and needed data munged around. Apart from client action the handle is devoid of meaning.

However, a database stores permanent data with permanent IDs. You need to have a way of getting back to the data you want three days afterward. To do that you need to have something meaningful somewhere, not just memory handles made permanent.

Why do database designers care about primary keys and alternate keys? Because when someone comes back to the database and can't find their data, the database is worthless. If you need to browse and analyze the data, how will you figure it out if everywhere you look there are columns named 'id' with no other constraints to tell you what they mean? Which of the 90000 rows with object_label = 'Important Thing' is the one you want?

3
  • Ah ok, " without ... the application of alternate unique keys" seems to be key here. People really do that? Guess I'm glad I'm not at the worst end then :-)
    – Martin
    Oct 9, 2014 at 20:40
  • 4
    Yes - every table should have a Natural Primary Key in addition to any Surrogate Primary Key added to improve join performance. This cannot be repeated often, or strongly, enough. Oct 9, 2014 at 21:43
  • @Martin - I wish it weren't so, but seriously many coders don't think about natural keys Nov 3, 2017 at 19:38
0

I think the comment in your linked question is referring to the idea that you could be using natural keys instead of generated keys for everything. But I don't think your use of generated keys in this instance is unwarranted; you want a single unique key that can reference a single row of data.

I do have a question about your choice UUID's, though. They are unique, but are a relatively large data type. Is this a distributed system which merges the data together later?

1
  • Sorry, the linked question isn't from me :-) I wondered the same about the UUIDs though :-)
    – Martin
    Oct 9, 2014 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.