CODEWITHSUNDEEP
javasqlhibernate
eduardohl
eduardohl

Reputation: 1216

Hibernate - What primary key should I use in a one to many relationship without an ID on the child table

In a very simplified way, I'm trying to create the following structure:

Table Post
id (PK)
name
content

Table Tag
post_id (FK to post id)
name

So that a post might have many tags, but the tags don't have a primary id. (the primary key so far would be the composite post_id + name)

Now what I wanted to know is if it is a bad practice (to omit the id column in the tag table) and what would be the possible values to use as a primary key for the tag table when using hibernate.

Upvotes: 0

Views: 117

Answers (2)

mrjoltcola
mrjoltcola

Reputation: 20842

I wanted to know is if it is a bad practice (to omit the id column in the tag table)

Yes, it is a bad practice to omit a primary key, especially if it is a table in a relationship. In your case, it would depend on whether Tag.Name is a candidate for primary key, combined with post_id. If that is so, you then have to decide whether you can deal with composite (complex) keys, or single field keys. For consistency, try to stick with the same scheme across the database. If you use ID in some tables, use it in all.

In addition, with ORMs and/or web frameworks, you should stick to single column primary keys (if that means using synthesized keys then so be it) to simplify interoperability and implementation with various stacks, frameworks, presentation layers, data grids (Rails, jqui, Extjs, etc.).

If you do add a synthetic key (ID column recommended), make sure to add appropriate unique constraints around the other natural/candidate keys in the table since ID's constraint won't be enough to enforce the data integrity of the other fields in the table.

what would be the possible values to use as a primary key for the tag table when using hibernate.

Depends on requirements. Most apps do well with sequences / auto-increment keys (32 or 64 bit integer values). If you need to scale a web app, and have multiple nodes creating records in parallel, consider GUIDs, which allow you to offload the key value generation onto the application code. I prefer simple sequences.

Upvotes: 1

duffymo
duffymo

Reputation: 308813

Every row in a normalized database has a primary key. That's a must.

post_id feels like a foreign key that should refer to the post table id primary key.

I don't care much for this design. A post can have many tags; a tag can be applied to many posts. It should be a many-to-many design with a join table.

create table post (
    id int not null auto_increment,
    text varchar(140),
    primary key id
);

create table tag (
   id int not null auto_increment,
   name varchar(80) not null,
   primary key id
);

create table post_tag (
   post_id int,
   tag_id int,
   primary key(post_id, tag_id),
   foreign key(post_id) references post(id),
   foreign key(tag_id) reference tag(id)
);

Upvotes: 2

Related Questions