2013/10/8 Eric Schwarzenbach <[email protected]> > > > On Tuesday, October 8, 2013 11:19:02 AM UTC-4, Lukas Eder wrote: >> >> >> >> >> 2013/10/8 Eric Schwarzenbach <[email protected]> >> >> >>> >>> 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, >>> >> >> I was just going to say it, but then you said it yourself :-) >> >> >>> 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, [...] >>> >> >> ... I really have to read his book, eventually ... >> > > Which one? But yes, you really ought to read some of his writings. >
I was thinking of this one: Date, C. J. (2011). SQL and relational theory: how to write accurate SQL code. Sebastopol, CA: O’Reilly. Retrieved from http://proquest.safaribooksonline.com/9781449319724 I was made aware of that book by a reader of my recent article, here: http://tech.pro/tutorial/1555/10-easy-steps-to-a-complete-understanding-of-sql > > >> >>> [...] 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. >>> >> >> True, those are actual benefits of "more strictly" relational modelling. >> >> I think there are more pragmatic, straightforward, non-philosophical >> reasons why surrogate keys can be better quite quickly, though. Do you >> think it is faster joining CITY with COUNTRY based on an INT >> (CITY.COUNTRY_ID = COUNTRY.ID) or based on a VARCHAR(255) >> (CITY.COUNTRY_NAME = COUNTRY.NAME) ? >> > > That is implementation dependent, and yes that has long been one of the > primary arguments for replacing any textual key with a numeric surrogate. > However in many modern implementations the difference is really not very > significant. Based on that same dimishment of this implementation concern, > many have switched to advocating UUIDs rather than sequencial integers for > surrogate keys. > > But do you really want to your logical model heavily influenced by > implementation performance issues? (Yes, sometimes you may have to, but my > feeling it don't start from that position!) > > >> >> But then again, C.J. Date stated that UNION is superior to UNION ALL, so >> I'm not sure if real-world performance is relevant in this discussion :-) >> >> Thinking of it, do SQL databases actually physically store (and >> duplicate) the natural candidate foreign key, or do they create a "virtual" >> surrogate key based on ROWIDs? I'll ask Quora: >> http://www.quora.com/Database-**Systems/In-a-more-strict-** >> relational-model-I-wouldnt-**use-surrogate-keys-Id-use-** >> natural-candidate-keys-**possibly-spanning-several-non-** >> numeric-columns-How-is-this-**implemented<http://www.quora.com/Database-Systems/In-a-more-strict-relational-model-I-wouldnt-use-surrogate-keys-Id-use-natural-candidate-keys-possibly-spanning-several-non-numeric-columns-How-is-this-implemented> >> > > Again, this depends on the implementaiton (and I'm not going to read that > because on priciple I'm not going to log in and give it access to info from > my account just to view their site). And I would certainly argue that they > should if this is desirable for performance. Wouldn't you agree that > implementation is the correct place to address performance and not logical > model design? (And yes, I know in the real world we are forced to do > unideal things to work around these unpleasant realities. I'm not arguing > we should not do so when necessry, just that we should not pervert out very > design principles around them.) > I live in a world where there are things like NUMBER(1) instead of BOOLEAN and VARCHAR2 instead of... VARCHAR. Oh, and I've built a workaround around this DB2 limitation into jOOQ: http://stackoverflow.com/q/9234021/521799 Not to speak of limitations of number of bind values as mentioned in here http://blog.jooq.org/2013/04/28/rare-uses-of-a-controlflowexception/ - SQLite: 999 - Ingres 10.1.0: 1024 - Sybase ASE 15.5: 2000 - SQL Server 2008: 2100 ;-) Maybe I've lived in this world too long... -- 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.
