• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Is composite-primary key the best solution - Hibernate Spring

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am building chat application and I want a table - chats and another - messages. Chat will have many messages, also id and user_first and user_second. Right now I am doing it with a composite primary key where the composite is user_first and user_second and the id is unique auto-increment:



is there any better way to do it and why?
 
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why have the id fields if that's not your actual id (primary key)? I'd either drop the id field, or make it your @Id and add a unique constraint on the combination of the other two fields.
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I second Rob's suggestion. Use a unique single-field auto-generated value for your primary key. You can retrieve by user or whatever as needed by defining secondary keys.

Compound keys are a real pain, I can say from experience. Avoid them when you can.
 
Bartender
Posts: 10780
71
Hibernate Eclipse IDE Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:Compound keys are a real pain, I can say from experience. Avoid them when you can.


Then I suggest that's a product weakness.
As an old modeller and DBA, I detest "ID"s slapped into tables for no other reason than to satisfy a spurious uniqueness constraint that would be "natural" for a compound key, and then require equally spurious secondary keys and constraints - one of which will almost certainly ALSO be unique.

My 2 cents. :-)

Winston
 
Saloon Keeper
Posts: 15484
363
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I love using natural/compound keys, but I will concede that it can be a pain if the keys are big (long strings or many components) and have to be used in many other tables, causing those tables to bloat.

I don't like using surrogate keys if a row can be identified uniquely by natural properties, but it's important to consider issues like how well the keys can be indexed or how large they are.
 
Winston Gutkowski
Bartender
Posts: 10780
71
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Stephan van Hulst wrote:I love using natural/compound keys, but I will concede that it can be a pain if the keys are big (long strings or many components) and have to be used in many other tables, causing those tables to bloat.
I don't like using surrogate keys if a row can be identified uniquely by natural properties, but it's important to consider issues like how well the keys can be indexed or how large they are.


Hmmm. I still reckon that it's a "how" problem inherent to the database or software you're using.

Any relational database worth its salt should allow you to define relationships - especially hierarchical ones - without specifying how to do it - or at the very least it should hide the implementation detail from you.

All I should have to do to create a 1:many relationship between two tables is specify the "additional" portion of the key relative to its parent. I shouldn't have to worry how the database stores them or their associated rows. That is, after all, what they're there for.

Progress already had it 20 years ago in its data language via an "OF" construct, which you could use when creating or retrieving:
ie, create Item OF Category ...
or for each Item OF Category ...
Sadly, SQL took over the frigging universe, and it doesn't translate well.

I guess I'm up to 4 cents now. :-)

Winston
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Stephan van Hulst wrote:I love using natural/compound keys, but I will concede that it can be a pain if the keys are big (long strings or many components) and have to be used in many other tables, causing those tables to bloat.

I don't like using surrogate keys if a row can be identified uniquely by natural properties, but it's important to consider issues like how well the keys can be indexed or how large they are.



And that reminds me of an entirely different can of worms.

Sometimes a UUID/GUID is the ideal unique identifier for a record, especially if you have peer-to-peer synchronization with primarily-disconnected databases. But the downside of GUIDs is that they are very long, very random, and have no inherent meaning. That can lead to issues when the primary means of retrieval is related to creation time or some other data value. Also GUIDs cannot be used as a basis for partitioning, unless you want your partitions to be completely random (which is not often the case, I'd wager). So you can either be dogmatic and slog through or cheat and assign an integer primary key to the table in question. if your other values cannot be guaranteed unique.

As for my "defective" DBMS, it was IBM's DB2 and I can tell you you really haven't enjoyed life until you have ported a large multi-client database from a mainframe system where fixed-length space-padded text fields are historically the norm to a PC-based Java system, where variable-length strings are the preference and had to do everything by normalizing key values and then building compound keys out of them.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic