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
I think I see the fundamental point where we disagree. I try to keep as much business logic in the database as possible. That's more of a DBA perspective, I guess: don't trust these pesky app developers, always assume they'll try to put broken data in your DB.
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...
haha I can see why we disagree. Same problems, different solutions.
Often there are dozens of applications (or independent
modules) using the database
I know! I always assume that someone will try to pull this on me, some shortsighted bugger always does, and I end up on crusde protecting my platform against this!
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...
Yeah. When I was doing a lot of relational work, I'd enforce these checks by having business logic layers around my ORM.
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