0

I've got following situation: I want to connect multiple records from one table with some kind of relation. Record could have no connection to other, or could have multiple of them (1 or more). There is no hierarchy in this relation.

For example:

CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

I've thought of two ideas:

  1. Make a new column in this table, which will contain some relationId. It won't reference anything. When new record is inserted, I will generate new relationId and put it there. If I would want to connect other record with this one, I will simply put the same relationId.

Example:

CREATE TABLE x
(
  x_id NUMBER(19, 0) NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL,
  relation_id NUMBER(19, 0) NOT NULL
);

insert into x values (nextval, 'blah', 1);
insert into x values (nextval, 'blah2', 1);

It will connect these two rows.

pros:

  • very easy
  • easy queries to get all records connected to particular record
  • no overhead

cons:

  • hibernate entity will contain only relationId, no collection of related records (or maybe it's possible somehow?)

    1. Make a separate join table, and connect rows with many-to-many relation. Join table would contain two column with ids, so one entry would connect two rows.

Example:

CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 NUMBER(19, 0) NOT NULL REFERENCES x (x_id),
  x_id2 NUMBER(19, 0) NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2)
);

insert into x values (1, 'blah');
insert into x values (2, 'blah2');

insert into bridge_x values (1, 2);
insert into bridge_x values (2, 1);

pros:

  • normalized relation
  • easy hibernate entity mapping, with collection containing related records

cons:

  • overhead (with multiple connected rows, every pair must be inserted)

What is the best way to do this? Is there any other way than these two?

mdziob
  • 1,116
  • 13
  • 26
  • Can you please be more specific about what kind of relationships are allowed and what aren't ? If a row has multiple connections to other rows, does it mean all theses rows are connected with each other like in a set? (e.g. row1, row2, row3 are part of the same relationship and they can't be part of another relationship)? – AlexG May 24 '17 at 14:10
  • @AlexG, yes exactly. Row could be only in one relationship with one or more other rows. – mdziob May 24 '17 at 21:36
  • Your first idea seems fine. I think you should allow "null", and only set the relationId when you need it. It's not meaningful to keep a relationId of a record if it's alone in its relationship. – AlexG May 25 '17 at 01:23
  • @AlexG is it somehow possible to map hibernate entity to have list of related rows in Set? – mdziob May 25 '17 at 07:51

1 Answers1

1

The best way in my experience is to use normalization as you've said in your second option. What you are looking for here is to create a foreign key.

So if you use the example you've given in example 2 and then apply the following SQL statement, you will create a relational database that can have 0 to many relations. 

ALTER TABLE `bridgex` ADD  CONSTRAINT `fk_1` FOREIGN KEY (`xID`) REFERENCES `x`(`xID`) ON DELETE NO ACTION ON UPDATE NO ACTION;
MinistryOfChaps
  • 1,458
  • 18
  • 31
  • Is this really the best choice? Are there any other pros and cons for this approach? – mdziob May 24 '17 at 21:40
  • Personally yes and from your question this seems to be what you are looking if you want to keep consistency in your database and to avoid orphaned data (to make sure your x_id in table bridgeX actually links to a x_id in table X. So with the foreign key I've given you above, this will ensure that if you want to delete a record from table X, all records from bridgeX must be deleted first. If you want to delete a row in table X and delete all associated data to that row in table bridgeX, you can change the foreign key to CASCADE on delete. – MinistryOfChaps May 25 '17 at 11:13
  • Also, [this stackoverflow thread](https://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys) is a great place to see more in-detail pros and cons of foreign keys. – MinistryOfChaps May 25 '17 at 11:18