5 ms·

There are strong opinions on it, but I firmly believe that "always have an autoincremental id" is a very wrong approach. Read http://it.toolbox.com/blogs/datab

by wulczer 15y ago

There are strong opinions on it, but I firmly believe that "always have an autoincremental id" is a very wrong approach. Read http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327 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

zzzeek 15y ago
DBAs will emphasize surrogate integer primary keys largely for performance reasons. On SQL Server and others, their performance and size usage in indexes and such is well defined and well optimized. I acutally had a few natural PKs suggested in my current schema and the DBAs insisted that every table primary key on a surrogate. 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.
- wulczer 15y ago
  Yeah, these might all be valid reasons to use surrogate keys. I'm not saying there are none, just that always using one often leads to design mistakes. 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...
  - zzzeek 15y ago
    After my experience in not using them for a large schema, using an int pk is pretty much a default decision for me, unless the table's PK is meaningful compared to another table like an association table. Doing big data migration jobs where the primary keys have to change is very painful. An identity-map ORM like SQLAlchemy is keying everything on primary key - SQLA supports mutable PKs and cascading updates (both via the DB, as well as in memory if you're on sqlite/MyISAM) fully, but the changing in-memory identities is fundamentally painful to deal with when you're shoveling around a lot of data and mutations.
cturner 15y ago
Thanks to you and others for respectful responses on what I didn't realise was a hot-button issue. 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
wladimir 15y ago
well-defined natural key (users and their logins, 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).
- wulczer 15y ago
  That's what ON UPDATE CASCADE is for. 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 :) )
  - wladimir 15y ago
    Yes, but in practice it frequently happens that you refer to records in various different places. Either client-side in API-using software, or in a completely separate database, etc. In such cases, "Update all references in the world" will be entirely impractical and bug-prone.

by wulczer 15y ago

zzzeek 15y ago

DBAs will emphasize surrogate integer primary keys largely for performance reasons. On SQL Server and others, their performance and size usage in indexes and such is well defined and well optimized. I acutally had a few natural PKs suggested in my current schema and the DBAs insisted that every table primary key on a surrogate. 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.

wulczer 15y ago
Yeah, these might all be valid reasons to use surrogate keys. I'm not saying there are none, just that always using one often leads to design mistakes. 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...
- zzzeek 15y ago
  After my experience in not using them for a large schema, using an int pk is pretty much a default decision for me, unless the table's PK is meaningful compared to another table like an association table. Doing big data migration jobs where the primary keys have to change is very painful. An identity-map ORM like SQLAlchemy is keying everything on primary key - SQLA supports mutable PKs and cascading updates (both via the DB, as well as in memory if you're on sqlite/MyISAM) fully, but the changing in-memory identities is fundamentally painful to deal with when you're shoveling around a lot of data and mutations.

cturner 15y ago

Thanks to you and others for respectful responses on what I didn't realise was a hot-button issue. 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

wladimir 15y ago

well-defined natural key (users and their logins, 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).

wulczer 15y ago
That's what ON UPDATE CASCADE is for. 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 :) )
- wladimir 15y ago
  Yes, but in practice it frequently happens that you refer to records in various different places. Either client-side in API-using software, or in a completely separate database, etc. In such cases, "Update all references in the world" will be entirely impractical and bug-prone.