On Tuesday, October 8, 2013 3:39:12 AM UTC-4, Lukas Eder wrote: > > > > but there ARE distinct advantages of not generating a surrogate key when >> you have a good natural candidate key already, even if they can change. > > > I'm curious about this. What would be those advantages apart from using a > little less storage, of course? > > Storage is one. Another is that when using surrogate keys, you always have to do additional joins to get back to some meaningful information (take the usage of that city table for example). You always need to get back to the natural key to make sense of the information making use of this table. This amounts to a lot more everyday complexity. And yes, I realize you could use the same argument against normalizing tables, but there are good reasons for the normalization. With the surrogate key you are adding complexity, often for no good reason.(And I do not argue there is never a good reason. I add surrogate keys when there is a good reason, just not by default.)
I've used the always-have-an-immutable-non-composite surrogate key / never-use-natural-joins / etc approach. I don't think there is a name for it, but there is a whole series of tendencies / preferences in database design that runs counter to the C.J. Date style relational approach, that in my opinion stems from OO thinking, from the tendency to think of tables as containing "entities" rather than logical propositions. I feel I can speak to this because I come from that world, and once shared that view and have gradually come over to the other side. I can tell you in having used both approaches it is a pleasure working with a database where you can use natural joins and don't have to do additional joins all over the place every time you want to throw an ad hoc query at the database and get meaningful information out of it. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
