-
Implementing composite keys with JPA and Hibernate (41 messages)
- Posted by: Frank Charles
- Posted on: September 03 2009 08:06 EDT
Nowadays, with the widespread use and deployment of Object-Relational Mapping (ORM) tools, you don't generally have to think too hard about such arcane issues as composite keys. Occasionally, you come across a situation where a composite key is required, and you need a strategy for this. This tip shows you how to implement composite keys with JPA and Hibernate.Threaded Messages (41)
- Composite Keys by John Smith on September 03 2009 13:22 EDT
- Re: Composite Keys by Ilya Sterin on September 03 2009 14:24 EDT
-
Re: Composite Keys by Alessandro Santini on September 03 2009 02:40 EDT
-
Re: Composite Keys by Casual Visitor on September 03 2009 04:26 EDT
-
Re: Composite Keys by James Watson on September 03 2009 04:39 EDT
-
Re: Composite Keys by Mark N on September 03 2009 08:42 EDT
-
Re: Composite Keys by James Watson on September 03 2009 10:03 EDT
-
Re: Composite Keys by Mark N on September 03 2009 10:32 EDT
-
Re: Composite Keys by Daniel Cullender on September 04 2009 03:51 EDT
- Re: Composite Keys by Mark N on September 04 2009 07:37 EDT
-
Re: Composite Keys by James Watson on September 04 2009 09:38 EDT
- Re: Composite Keys by James Watson on September 04 2009 01:16 EDT
-
Re: Composite Keys by Mark N on September 04 2009 07:20 EDT
-
Re: Composite Keys by James Watson on September 04 2009 09:36 EDT
-
Re: Composite Keys by Mark N on September 06 2009 10:51 EDT
- Joins in ORM by art src on September 07 2009 02:29 EDT
- Re: Composite Keys by James Watson on September 07 2009 07:00 EDT
-
Re: Composite Keys by Mark N on September 06 2009 10:51 EDT
-
Re: Composite Keys by James Watson on September 04 2009 09:36 EDT
-
Re: Composite Keys by Daniel Cullender on September 04 2009 03:51 EDT
-
Re: Composite Keys by Mark N on September 03 2009 10:32 EDT
-
Re: Composite Keys by James Watson on September 03 2009 10:03 EDT
-
Re: Composite Keys by Mark N on September 03 2009 08:42 EDT
-
Re: Composite Keys by James Watson on September 03 2009 04:39 EDT
- Do you know of Simpleorm ? by Franck Routier on September 09 2009 03:10 EDT
-
Re: Composite Keys by Casual Visitor on September 03 2009 04:26 EDT
-
Re: Composite Keys by Mark N on September 03 2009 08:56 EDT
- Re: Composite Keys by art src on September 07 2009 02:12 EDT
-
Re: Composite Keys by Alessandro Santini on September 03 2009 02:40 EDT
- Re: Composite Keys by Alex Besogonov on September 03 2009 14:30 EDT
- Re: Composite Keys by Alex Krotov on September 30 2009 05:46 EDT
- Re: Composite Keys by James Watson on September 03 2009 16:10 EDT
-
Re: Composite Keys by Ilya Sterin on September 03 2009 04:16 EDT
-
Re: Composite Keys by James Watson on September 03 2009 04:32 EDT
-
Re: Composite Keys by James Watson on September 03 2009 04:40 EDT
-
Iso codes for currency / country by rob bygrave on September 03 2009 07:39 EDT
- Re: Iso codes for currency / country by Mark N on September 03 2009 08:46 EDT
- Re: Iso codes for currency / country by Mike Lythgoe on September 04 2009 03:25 EDT
-
Re: Composite Keys by alberto gori on September 04 2009 02:54 EDT
- Re: Composite Keys by James Watson on September 04 2009 09:43 EDT
-
Iso codes for currency / country by rob bygrave on September 03 2009 07:39 EDT
-
Re: Composite Keys by James Watson on September 03 2009 04:40 EDT
-
Re: Composite Keys by James Watson on September 03 2009 04:32 EDT
-
Re: Composite Keys by Ilya Sterin on September 03 2009 04:16 EDT
- Re: Composite Keys by Ilya Sterin on September 03 2009 14:24 EDT
- A truely terrible example I'm afraid by rob bygrave on September 03 2009 19:15 EDT
- Re: A truely terrible example I'm afraid by Mark N on September 03 2009 20:44 EDT
- Re: A truely terrible example I'm afraid by Mario Papi on September 04 2009 08:48 EDT
- There are ORMs and ORMs by Guido Anzuoni on September 04 2009 03:34 EDT
- This is a very bad example! by Felix H. Ho??feld on September 04 2009 07:10 EDT
- Re: Implementing composite keys with JPA and Hibernate by netbug netbug on September 06 2009 08:07 EDT
- Re: Implementing composite keys with JPA and Hibernate by Mark N on September 06 2009 23:19 EDT
- Re: Implementing composite keys with JPA and Hibernate by ^C^ ^C^ on September 07 2009 04:18 EDT
-
Re: Implementing composite keys with JPA and Hibernate by netbug netbug on September 07 2009 12:00 EDT
- Re: Unrelated whines by ^C^ ^C^ on September 07 2009 02:10 EDT
-
Re: Implementing composite keys with JPA and Hibernate by netbug netbug on September 07 2009 12:00 EDT
-
Composite Keys[ Go to top ]
- Posted by: John Smith
- Posted on: September 03 2009 13:22 EDT
- in response to Frank Charles
"I think it's quite unusual to intentionally design a database this way nowadays" It is only unusual to design a database this way if you have a tool that makes it so hard to use tables with composite keys. It is always better to design a table to use natural primary keys than artificial keys and it is terrible that the tool makes it so difficult to support this. -
Re: Composite Keys[ Go to top ]
- Posted by: Ilya Sterin
- Posted on: September 03 2009 14:24 EDT
- in response to John Smith
"I think it's quite unusual to intentionally design a database this way nowadays"
+1 In the phantom world where you think objects are the center of the universe and forget that the data is stored in a relational schema, that is ideal, but it's never optimal. That's why I get pretty pissed off hearing "map something to legacy database schema". It's not legacy, its good design. You should be able to architect an optimal relational schema and then map it to an optimal domain model and neither should effect the other much.
It is only unusual to design a database this way if you have a tool that makes it so hard to use tables with composite keys. It is always better to design a table to use natural primary keys than artificial keys and it is terrible that the tool makes it so difficult to support this. -
Re: Composite Keys[ Go to top ]
- Posted by: Alessandro Santini
- Posted on: September 03 2009 14:40 EDT
- in response to Ilya Sterin
How can I agree more with you guys? That is why I am less and less convinced about the value of ORM in general and about the awfully lame JPA in particular. I will certainly sound outdated, shortsighted, and any other adjective can come up from your mind - reality is, ORM is introducing more aches than it manages to solve: lazy fetching, composite keys, stored procedures (!!!), only for keeping the code "readable". Please give me iBatis, a bunch of domain objects and the good ol' DAO. I will keep on living quite happily with them.In the phantom world where you think objects are the center of the universe and forget that the data is stored in a relational schema, that is ideal, but it's never optimal. That's why I get pretty pissed off hearing "map something to legacy database schema". It's not legacy, its good design.
-
Re: Composite Keys[ Go to top ]
- Posted by: Casual Visitor
- Posted on: September 03 2009 16:26 EDT
- in response to Alessandro Santini
ORM is introducing more aches than it manages to solve
Yep. The Anti-ORM/Hibernate movement is already under way! -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 03 2009 16:39 EDT
- in response to Casual Visitor
I have to agree with the sentiment that Hibernate burdens the developer more that it should. You end up having to design your database and object model to work well with hibernate. Ideally, tools should help you do what you want to do, not direct your approach.ORM is introducing more aches than it manages to solve
Yep. The Anti-ORM/Hibernate movement is already under way! -
Re: Composite Keys[ Go to top ]
- Posted by: Mark N
- Posted on: September 03 2009 20:42 EDT
- in response to James Watson
I agree with most of what you have said James, but from what I have seen using something like Hibernate actually makes using Composite keys easier. This becomes evident when queries are written.ORM is introducing more aches than it manages to solve
Yep. The Anti-ORM/Hibernate movement is already under way!
I have to agree with the sentiment that Hibernate burdens the developer more that it should. You end up having to design your database and object model to work well with hibernate. Ideally, tools should help you do what you want to do, not direct your approach. -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 03 2009 22:03 EDT
- in response to Mark N
I agree with most of what you have said James, but from what I have seen using something like Hibernate actually makes using Composite keys easier. This becomes evident when queries are written.
I was actually thinking about surrogate keys when I wrote that. I am working on something with Hibernate right now. It works fine. I get my work done. I just have to accommodate the tool in my design more that I think I should. I'm no expert on Hibernate and could definitely be doing it wrong but AFAICT, I would have to completely turn my object model upside down in order to have Hibernate insert parent keys first and thereby satisfy my non-null constraints on my foreign keys. I decided that I'd be better off just dropping the non-null constraints. There are other things like having to have a field on the object to hold the foreign key. Again, I'm no expert but it's a very invasive tool, at least for non-experts anyway. I guess I just wish tool makers would be more cognizant that their tool is not the most important aspect of the design of the application it is used in. -
Re: Composite Keys[ Go to top ]
- Posted by: Mark N
- Posted on: September 03 2009 22:32 EDT
- in response to James Watson
I get your point and I agree that it is not just a straight replacement for saving/updating when doing surrogate vs natural keys. For retrieving, still makes queries easier. On the other hand, doing it without an ORM would still be worse. Been there, done that. I have reverse engineered databases with NHibernate. It as easier to use than coding all the SQL. And I was able to switch from Access to SQL Server without a hitch. On the other hand, the guy who started the db and coded in SQL ... He had tons of problems. (FYI, on the new release, I'm not "letting" him code anything. :) )I'm no expert on Hibernate and could definitely be doing it wrong but AFAICT, I would have to completely turn my object model upside down in order to have Hibernate insert parent keys first and thereby satisfy my non-null constraints on my foreign keys
I have not had that issue that i know of with surrogate keys. Hibernate handles it. Wonder what you are doing different. I'd love to talk to you about it. I've written quite a few projects with Hibernate and NHibernate. Anyway, with natural or surrogate keys, if you are using FK constraints, you have to insert the parent before the child. -
Re: Composite Keys[ Go to top ]
- Posted by: Daniel Cullender
- Posted on: September 04 2009 03:51 EDT
- in response to Mark N
On the other hand, doing it without an ORM would still be worse. Been there, done that. I have reverse engineered databases with NHibernate. It as easier to use than coding all the SQL. And I was able to switch from Access to SQL Server without a hitch. On the other hand, the guy who started the db and coded in SQL ... He had tons of problems. (FYI, on the new release, I'm not "letting" him code anything. :) )
<blockquote An invasive ORM can blur the seperation of concern between application and database development. Sure they both need to be designed in tandem, but how does one cleanly separate the two with schema specific metadata within your code? I believe a developer should not have change his entire domain model to conform to a database schema. From my past experience, we had a database development team concerned with maintaining and designing the database, and an application team concerned with, well, developing the application. The interface between the two worlds were simple stored procedures. The nice thing about this approach is that each team only had to focus on their discipline. My point/question is where do we draw the line here between modelling massive database in our application, or keeping the two specialisations separate. fit in with the database schema. -
Re: Composite Keys[ Go to top ]
- Posted by: Mark N
- Posted on: September 04 2009 19:37 EDT
- in response to Daniel Cullender
I believe a developer should not have change his entire domain model to conform to a database schema
Right. And the best way to insure that is to develop both. If you switch from surrogate to natural keys - you will have to change code.From my past experience, we had a database development team concerned with maintaining and designing the database, and an application team concerned with, well, developing the application.
I am sure it works. But separating those will eventually (unless very minor) to larger issues. The application consists of all code. SQL is code. DLL is code. Do you have a team just for UI? Just for Services? Just for web services? Just for domain code? Just for ..?simple stored procedures
Simple and SPs should not be in the same sentence.The nice thing about this approach is that each team had to focus their discipline
So they did ALL the SQL? This means everytime users need a new query, you have to involved all players instead of 1.My point/question is where do we draw the line here between modelling massive database in our application, or keeping the two specialisations separate. fit in with the database schema.
The database is not all that magical. Modern databases have become very good. And like I said, the application consists of all parts not just Java/C# code. Not sure what you mean by "modeling massive database in our application". Do you mean we should not use a Domain model? Or should not use annotations in them? -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 04 2009 09:38 EDT
- in response to Mark N
I get your point and I agree that it is not just a straight replacement for saving/updating when doing surrogate vs natural keys.
I think ORM is definitely worthwhile when you have to write to a database. For reads, I think it's overkill. I'm not arguing against ORM. I've used hibernate in the past and chose to use it in this case. I just find some things about it annoying and this foreign key thing just seems kind of stupid to me. It seems like it should be easily solved inside Hibernate.I have not had that issue that i know of with surrogate keys. Hibernate handles it. Wonder what you are doing different. I'd love to talk to you about it. I've written quite a few projects with Hibernate and NHibernate.
Start here: http://docs.jboss.org/hibernate/stable/core/reference/en/html/example-parentchild.html#example-parentchild-bidir And this explains the pain: http://blogs.warwick.ac.uk/colinyates/entry/hibernates_bizzare_interpretation
Anyway, with natural or surrogate keys, if you are using FK constraints, you have to insert the parent before the child. -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 04 2009 13:16 EDT
- in response to James Watson
I just find some things about it annoying and this foreign key thing just seems kind of stupid to me. It seems like it should be easily solved inside Hibernate.
When I posted on of these links above, I noticed that I can set the child column to non-null in the mapping should solve my issue. I don't understand why this is "not the recommended solution", however. That the documentation suggests I use a different object structure is the kind of thing I am talking about. -
Re: Composite Keys[ Go to top ]
- Posted by: Mark N
- Posted on: September 04 2009 19:20 EDT
- in response to James Watson
For reads, I think it's overkill
Depends. Is your model complex? Then no. Do you want to work with domain objects instead of recordsets? Then no. It seems like extra work, but it ends up being less. I looked at your link. How many of those do you have? Add the methods like link says. Sure, it is an issue, but not a big one and I have seldom had it. -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 04 2009 21:36 EDT
- in response to Mark N
I would say the more complex the data the more work it is to make it work with Hibernate. It just takes a lot longer to get things done if I have to create classes just to pull some information out of the database. I can build read only webservice extremely complex queries in an hour or so using SQL and be ready for QA.
Depends. Is your model complex? Then no. Do you want to work with domain objects instead of recordsets? Then no. It seems like extra work, but it ends up being less.I looked at your link. How many of those do you have? Add the methods like link says. Sure, it is an issue, but not a big one and I have seldom had it.
How many what? Parent-child relationships? Are you serious? From a data perspective? Thousands? Tens of thousands? I can't count them all. I've learned to avoid bi-directional relationships if I don't strictly need them. They create a lot more problems than they are worth. Anyway, you can say it's not a big deal but I think I've proven that you really have to adjust your programming to match hibernate if you want things to go smoothly. That's usually a bigger deal to me than database structure. -
Re: Composite Keys[ Go to top ]
- Posted by: Mark N
- Posted on: September 06 2009 22:51 EDT
- in response to James Watson
How many what? Parent-child relationships?
No, the link you showed which is many-to-may with. Single Parent Child is "easy". I was showing people who had zero ORM experience and no real OO development how to do it and they were picking it up pretty quickly ( They create a lot more problems than they are worth. Maybe. But the code you linked to is very much like code that was the "model" of how to do OO in VB many years ago.I can build read only webservice extremely complex queries in an hour or so using SQL and be ready for QA.
Maybe you can. But you are the only person I know of who can. Joins that are complex in SQL will [mostly] be easier in HQL. I am sure we both can come up with examples of something easier in each. But still, sometimes pure SQL is the right thing to do. Doing webservices with objects is usually easier with domain objects, though.Anyway, you can say it's not a big deal but I think I've proven that you really have to adjust your programming to match hibernate if you want things to go smoothly.
I don't see how you have proven it. If you are doing good OO dev you should be doing parent.addChild() not parent.getChildren().add().That's usually a bigger deal to me than database structure.
I am not sure how the above code is more than the amount of code you have to create to deal with parent child relationships, or worse many to many, in pure data structures. Mind, you have have done this in COBOL, VB, Java, Powerbuilder ... . I guess the key are your words "to me". So if it is better for you, then you should do it that way. Doing something you don't believe in is not good no matter if it is better or not. -
Joins in ORM[ Go to top ]
- Posted by: art src
- Posted on: September 07 2009 02:29 EDT
- in response to Mark N
Joins that are complex in SQL will [mostly] be easier in HQL.
I find this too. The ORM know the links and uses them syntactically. The database knows the links too (XX references YYY foreign keys), but SQL does not use that knowledge. Database queries tools do have some room to improve. -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 07 2009 19:00 EDT
- in response to Mark N
Maybe. But the code you linked to is very much like code that was the "model" of how to do OO in VB many years ago.
I don't know what code you are referring to. I have a parent that references a bunch of children. It's been a while since I got my degree but I haven't seen anything to suggest that this is a bad approach. Child to parent relationships are basically useless except for use in bi-directional relationships. Where does your file system start? At the root or at the leaves?
It might just be work environments. My experience is that the ratio of people who (adequately) understand SQL to the number of people who (adequately) understand OO (in the Java sense) is about 10 to 1. For read-only services, Objects are usually just overhead. One exception would be if you need to implement some logic around the data. I often see people create objects just to hold data on it's way to become XML. They add no value. To me, that is a COBOL-style approach. These objects are just like data areas. If you have no business logic in your objects, they aren't really objects in the OOP sense. Not only that, but this approach usually entails having all these objects in memory at once requiring a larger memory footprint where SQL based approaches are much more conducive to streaming.
I can build read only webservice extremely complex queries in an hour or so using SQL and be ready for QA.
Maybe you can. But you are the only person I know of who can. Joins that are complex in SQL will [mostly] be easier in HQL. I am sure we both can come up with examples of something easier in each. But still, sometimes pure SQL is the right thing to do. Doing webservices with objects is usually easier with domain objects, though.
My point was that not all styles of development work well with Hibernate. If you want Hibernate to work well you must subject your code to it's constraints. You don't seem to be arguing that this isn't true but yet you say I haven't proven (proven is too strong a term, sorry) my point. Why?
Anyway, you can say it's not a big deal but I think I've proven that you really have to adjust your programming to match hibernate if you want things to go smoothly.
I don't see how you have proven it. If you are doing good OO dev you should be doing parent.addChild() not parent.getChildren().add().
I just don't see why I should have to add a association to the parent from my children when I don't need it for anything other than to make Hibernate happy. I really don't know what that has to do with COBOL. COBOL doesn't even have objects (well OO COBOL does but does anyone even use that?) Bi-directional associations in hierarchical relationships create GC issues. Holding a reference to any child of any node in the structure prevents any other node in the entire structure from being collected. Unless you really need to walk back up the tree, it's best to keep everything top-down.
I am not sure how the above code is more than the amount of code you have to create to deal with parent child relationships, or worse many to many, in pure data structures. Mind, you have have done this in COBOL, VB, Java, Powerbuilder ... .
That's usually a bigger deal to me than database structure.
I guess the key are your words "to me". So if it is better for you, then you should do it that way. Doing something you don't believe in is not good no matter if it is better or not. -
Do you know of Simpleorm ?[ Go to top ]
- Posted by: Franck Routier
- Posted on: September 09 2009 03:10 EDT
- in response to Alessandro Santini
... Simpleorm is both simple to use and simple to modify. It will allow Compisite primary keys, using compsite keys for foreign keys, and working on coherent detached datasets. Moreover it has a great metadata system that make it quite easy to automate UI generation (be it web ui, using BarracudaMVC for example, or gnome ui, or any other). Have a look at http://www.simpleorm.org/sorm/whitepaper.html -
Re: Composite Keys[ Go to top ]
- Posted by: Mark N
- Posted on: September 03 2009 20:56 EDT
- in response to Ilya Sterin
In the phantom world where you think objects are the center of the universe and forget that the data is stored in a relational schema, that is ideal, but it's never optimal.
By phantom do you mean heretical? Should we bring back burning at stake too?That's why I get pretty pissed off hearing "map something to legacy database schema". It's not legacy, its good design.
[Checking calendar... Nope it is not 1980's ... hmmm]You should be able to architect an optimal relational schema and then map it to an optimal domain model and neither should effect the other much.
Yup. That is what ORM gives you. Actually, ORM will let you have an non-optimal one too. Sadly, your design with composite keys is not optimal nor good design, with or without Objects being central to the design. -
Re: Composite Keys[ Go to top ]
- Posted by: art src
- Posted on: September 07 2009 02:12 EDT
- in response to Mark N
That is one way to do it. Another idea would be that you create only one model of your domain, not two, and zero mappings.You should be able to architect an optimal relational schema and then map it to an optimal domain model and neither should effect the other much.
My optimal object design does not include synthetic id fields. Most of the demands of ORM can be accommodated without to much pain if that is your goal. But the result is neither an optimal database or object model.
Yup. That is what ORM gives you. Actually, ORM will let you have an non-optimal one too. Sadly, your design with composite keys is not optimal nor good design, with or without Objects being central to the design. -
Re: Composite Keys[ Go to top ]
- Posted by: Alex Besogonov
- Posted on: September 03 2009 14:30 EDT
- in response to John Smith
It is always better to design a table to use natural primary keys than artificial keys and it is terrible that the tool makes it so difficult to support this.
??? Use of surrogate keys as primary keys is one of the best design patterns for databases: http://en.wikipedia.org/wiki/Surrogate_key#References -
Re: Composite Keys[ Go to top ]
- Posted by: Alex Krotov
- Posted on: September 30 2009 17:46 EDT
- in response to Alex Besogonov
AFAIK from DB design practices field, composite keys are suitable when: - queries on child tables mostly don’t need ful info from parent table (joins are avoided then) - key values hardly going to changed in foreseen future (update of dependent children code and structure is costly) - semantic rules dictate: child record is meaningful only for this composite key combination - no multilevel composite key transfer is expected (happens when this key becomes part of child PK and get used in subsequent children) -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 03 2009 16:10 EDT
- in response to John Smith
"I think it's quite unusual to intentionally design a database this way nowadays"
Completely disagree. Using logical keys is a really shortsighted idea. I learned this the hard way. I worked on a system where the logical key of a top level table of a large database structure needed to have columns added (then later column removed.) Because pretty much every table in the system had a reference to this logical key, it meant every table had to be modified and every piece of code that touched a table had to be modified and retested. In other words, pretty much the entire database and many thousands of lines of code had to be fixed and retested requiring many thousands of man-hours of work. If the keys had been unrelated to the logical key, a lot of that work would not have been necessary. Ultimately, using logical keys as primary keys is painting yourself into a corner without significant benefit.
It is only unusual to design a database this way if you have a tool that makes it so hard to use tables with composite keys. It is always better to design a table to use natural primary keys than artificial keys and it is terrible that the tool makes it so difficult to support this. -
Re: Composite Keys[ Go to top ]
- Posted by: Ilya Sterin
- Posted on: September 03 2009 16:16 EDT
- in response to James Watson
That depends whether you pick a "good" natural key. If you pick one that you know will weather the time, than you'll be fine. Of course, if you pick one that only represents the business rules today and might change tomorrow, then you're better off with a surrogate key. With that said, even surrogate keys change. Take for example an app using an integer based sequence and then realizing that they need to partition and distribute the data and now they want to go with a UUID surrogate key approach. Nothing will shield you from the one off issues where keys change, outside of planning for it from the beginning and ensuring you reduce the chances of it happening. -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 03 2009 16:32 EDT
- in response to Ilya Sterin
That depends whether you pick a "good" natural key. If you pick one that you know will weather the time, than you'll be fine. Of course, if you pick one that only represents the business rules today and might change tomorrow, then you're better off with a surrogate key.
Since I can't predict the future and the cost of using surrogate keys is minuscule, I can't see any rational reason to adopt a logical key approach. I wish I had a dollar for every time I was told that something would never change.With that said, even surrogate keys change. Take for example an app using an integer based sequence and then realizing that they need to partition and distribute the data and now they want to go with a UUID surrogate key approach. Nothing will shield you from the one off issues where keys change, outside of planning for it from the beginning and ensuring you reduce the chances of it happening.
As I understand it, UUIDs depend on the unlikeliness of very large numbers colliding. If you are afraid of UUIDs colliding with your existing ids, should you really be using them? Also, what prevents you from using a UUID generator and then offsetting it past the sequentially generated keys? All of that aside, It doesn't matter at all if my surrogate keys change. I wasn't talking about changing the values of the keys (although that can be a problem when logical keys are used) I was talking about the number of columns used to define the logical key. You only ever need one column to define a surrogate key. -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 03 2009 16:40 EDT
- in response to James Watson
I wish I had a dollar for every time I was told that something would never change.
Forgot the end: "...and it changed." -
Iso codes for currency / country[ Go to top ]
- Posted by: rob bygrave
- Posted on: September 03 2009 19:39 EDT
- in response to James Watson
I'm pretty much in the surrogate key camp. However, I believe the ISO codes for countries and currencies are good candidates for primary keys (and I wouldn't describe them as surrogate). e.g. "USD", "EUR" etc ... ISO-4217 In my book, good natural keys are pretty rare though - you want to be really really really sure it does not change (aka just reiterating what James said). I also think composite keys are generally fine for "intersection tables". -
Re: Iso codes for currency / country[ Go to top ]
- Posted by: Mark N
- Posted on: September 03 2009 20:46 EDT
- in response to rob bygrave
I'm pretty much in the surrogate key camp. However, I believe the ISO codes for countries and currencies are good candidates for primary keys (and I wouldn't describe them as surrogate).
Not only are natural keys rare, they are typically strings ... and joining on strings ...
e.g. "USD", "EUR" etc ... ISO-4217
In my book, good natural keys are pretty rare though - you want to be really really really sure it does not change (aka just reiterating what James said).
I also think composite keys are generally fine for "intersection tables". -
Re: Iso codes for currency / country[ Go to top ]
- Posted by: Mike Lythgoe
- Posted on: September 04 2009 03:25 EDT
- in response to rob bygrave
I'm pretty much in the surrogate key camp. However, I believe the ISO codes for countries and currencies are good candidates for primary keys (and I wouldn't describe them as surrogate).
Surrogate Keys is not a concept created by the Hibernate folks to make it easier for their framework. It is something that DBAs, data designers etc. came up with many years ago, well before Java even appeared. It was designed to reduce the huge amounts of work required if columns from one table that were used as foreign keys ever changed. And it was designed to make databases easier to maintain. I'm definitely in the surrogate key camp as well. There are a few realistic natural keys, like currency codes and even then you havee to be careful. If you've ever worked on a system where something that seemed absolutely set in stone, such as account numbers, national insurance numbers etc. and then the business decided they needed to change the format, you'll know how much a nightmare it can be with natural keys and how easy it is with surrogate keys. The natural key in the example is unbelievably bad but I would guess that that's the whole point of showing how to do composite keys in Hibernate, sometimes you havee to deal with badly designed databases and Hibernate can cope with them.
e.g. "USD", "EUR" etc ... ISO-4217
In my book, good natural keys are pretty rare though - you want to be really really really sure it does not change (aka just reiterating what James said).
I also think composite keys are generally fine for "intersection tables". -
Re: Composite Keys[ Go to top ]
- Posted by: alberto gori
- Posted on: September 04 2009 02:54 EDT
- in response to James Watson
Hibernate is a ORM framework, and as any kind of good framework it has a philosophy (surrogated keys). You can't take any piece of code and make it work on Spring without any change. Or in any other framework.I wish I had a dollar for every time I was told that something would never change.
Forgot the end: "...and it changed." -
Re: Composite Keys[ Go to top ]
- Posted by: James Watson
- Posted on: September 04 2009 21:43 EDT
- in response to alberto gori
I don't disagree but that has no bearing on what I was discussing.I wish I had a dollar for every time I was told that something would never change.
Forgot the end: "...and it changed."
Hibernate is a ORM framework, and as any kind of good framework it has a philosophy (surrogated keys).
You can't take any piece of code and make it work on Spring without any change. Or in any other framework. -
A truely terrible example I'm afraid[ Go to top ]
- Posted by: rob bygrave
- Posted on: September 03 2009 19:15 EDT
- in response to Frank Charles
The link went to the article that had: create table PURCHASE_ORDERS ( ... primary key (street, city) ) Street and City as a primary key for purchase_orders - holy #$%^ batman!!!! I'm struggling to describe how bad I think this example is. Wow... stunned... -
Re: A truely terrible example I'm afraid[ Go to top ]
- Posted by: Mark N
- Posted on: September 03 2009 20:44 EDT
- in response to rob bygrave
The link went to the article that had:
Totally agree. I had posted on this on Dzone yesterday. A horribly crappy example.
create table PURCHASE_ORDERS (
...
primary key (street, city)
)
Street and City as a primary key for purchase_orders - holy #$%^ batman!!!! I'm struggling to describe how bad I think this example is. Wow... stunned... -
Re: A truely terrible example I'm afraid[ Go to top ]
- Posted by: Mario Papi
- Posted on: September 04 2009 08:48 EDT
- in response to rob bygrave
The link went to the article that had:
Agreed. Even though most intermediate to advanced developers recognize this as poor design, a lot of beginners read these articles and see IBM splashed across the top and think, well, it's from IBM so it must be good design!!
create table PURCHASE_ORDERS (
...
primary key (street, city)
)
Street and City as a primary key for purchase_orders - holy #$%^ batman!!!! I'm struggling to describe how bad I think this example is. Wow... stunned... -
There are ORMs and ORMs[ Go to top ]
- Posted by: Guido Anzuoni
- Posted on: September 04 2009 03:34 EDT
- in response to Frank Charles
I would remember to myself first that ORM != Hibernate. Some of the painful things you have to suffer using a tool are not necessarily intrinsic properties of a technology. And if you think that using an ORM tool is a shortcut to manage tables and columns then you deserve Pro*C, SQLJ, LINQ and any other hell stuff. Guido -
This is a very bad example![ Go to top ]
- Posted by: Felix H. Ho??feld
- Posted on: September 04 2009 07:10 EDT
- in response to Frank Charles
This is a very bad example. The name of that post should be "How not to implement composite keys with JPA". Leaving out the poor choice of a PK that has already been mentioned it misses the most important issue: Overriding the equals() method. I suggest reading https://www.hibernate.org/109.html -
Re: Implementing composite keys with JPA and Hibernate[ Go to top ]
- Posted by: netbug netbug
- Posted on: September 06 2009 08:07 EDT
- in response to Frank Charles
The most terrible wheel ORM invented is the query language. There are JDOQL, HQL, JPQL ... most of them has very short lifespan, duplicating each other but not compatible... -
Re: Implementing composite keys with JPA and Hibernate[ Go to top ]
- Posted by: Mark N
- Posted on: September 06 2009 23:19 EDT
- in response to netbug netbug
The most terrible wheel ORM invented is the query language.
Really? How much real OO development have you done with an ORM such as Subclasses mapped to multiple tables? -
Re: Implementing composite keys with JPA and Hibernate[ Go to top ]
- Posted by: ^C^ ^C^
- Posted on: September 07 2009 04:18 EDT
- in response to netbug netbug
The most terrible wheel ORM invented is the query language.
You mean you should use SQL for everything ? even when the datastore is not RDBMS ? Oh dear, I think we've been through this subject before. The application is using OO, so it makes a lot of sense to use a query language that targets such a domain, whilst also allowing use of SQL in specific areas where the datastore suits it. --Andy (DataNucleus) -
Re: Implementing composite keys with JPA and Hibernate[ Go to top ]
- Posted by: netbug netbug
- Posted on: September 07 2009 12:00 EDT
- in response to ^C^ ^C^
I can see many ORM-QLs will be ruled out & obsoleted from the market soon. If your application is using such an ORM tool, it will be costly to maintain & extend. For example, it will be difficult to find a staff to maintain a JDO 1.0 application 2 years later. However, an application using proper SQL framework such as ibatis or spring-jdbc would be pretty easy to maintain & extend even for people come from other technical background such as c#, vc. Some orm tools supports non-relationsl datastores via special dialect (for example, jdbc driver for excel, csv), this is interesting.The most terrible wheel ORM invented is the query language.
You mean you should use SQL for everything ? even when the datastore is not RDBMS ? Oh dear, I think we've been through this subject before. The application is using OO, so it makes a lot of sense to use a query language that targets such a domain, whilst also allowing use of SQL in specific areas where the datastore suits it.
--Andy (DataNucleus) -
Re: Unrelated whines[ Go to top ]
- Posted by: ^C^ ^C^
- Posted on: September 07 2009 14:10 EDT
- in response to netbug netbug
I can see many ORM-QLs will be ruled out & obsoleted from the market soon. If your application is using such an ORM tool, it will be costly to maintain & extend. For example, it will be difficult to find a staff to maintain a JDO 1.0 application 2 years later.
You were predicting the end of the world some 3+ years ago sir. It didn't happen. You dived into threads back 3+ yrs ago on unrelated topics, and you do the same here. This thread is about composite keys in JPA. This has very little to do with query languages on ORM and non-ORM.However, an application using proper SQL framework such as ibatis or spring-jdbc would be pretty easy to maintain & extend even for people come from other technical background such as c#, vc.
Using JDBC to persist OO information is not interesting; not even slightly. The API you propose is not OO nor attempts to be so. It is good for what it was intended for, as is SQL. JDBC and SQL aren't some sort of global solution to world hunger. For this very reason iBatis and the like target a specific audience. You sir, do not. Use of SQL on OODBMS is a minority interest activity, for the very reasons already outlined. You haven't made any attempt to present a reasoned coherent argument as to why you think SQL is a good idea on non-RDBMS datastores. This was also the case some 3+ years ago. I'll leave you to have a good think :-)
Some orm tools supports non-relationsl datastores via special dialect (for example, jdbc driver for excel, csv), this is interesting.