Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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?
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?
