more flexible relationship between two tables in database?

Question

Let's say there are two tables. (I made example table just to explain the concept of my idea, there would be some mistake to define it.)

TableA 'user'

CREATE TABLE user
(
  id serial PRIMARY KEY
  name character varying(80) UNIQUE NOT NULL,
)

TableB 'history'

CREATE TABLE history
(
  id serial PRIMARY KEY,
  action character varying(80) NOT NULL,
  CONSTRAINT history_action_key UNIQUE (name)
)

I think there two ways to make relationship between two tables.

one way : adding ForeignKey constraint in one table

CREATE TABLE user
    (
      id serial PRIMARY KEY,
      name character varying(80) NOT NULL UNIQUE,
      history_id integer REFERENCES history
    )

It means there is another field named history_id to direct to related history with FK in user table. right?

second way : you are creating new table(lets say TableC 'relationship'). TableC will show only relationship between TableA and TableB

CREATE TABLE relationship
(
  id serial PRIMARY KEY,
  user_id integer NOT NULL REFERENCES remann_users (id),
  history_id integer NOT NULL REFERENCES history,
)

so my question is

is second way to store data in database more flexible?

I feel like second way seems more flexible because User table and History table are totally separated in individual tables. And Just another table have data to tell their relationship. But first way seems User table have a field 'history_id' in it's table. So I feel like its way is more coupling between two tables.

Is my idea correct?

---

I think I was confused with manyTomany relationship first.

'user' table has data like below

pk name

1 John

2 Adam

3 Kelly

'history' table has data like below

pk action

1 played baksetball

2 played video games

Now I want to make relationship between user and history.

if John played baksetball, Adam played basketball and Kelly played video games, I can make relationship by putting one more column in user table.

'user' table

pk name fk

1 John 1

2 Adam 1

3 Kelly 2

but if there is a case that John played basketball and at the same time played video games. This became manyTomany relationship. I need to make like below to show this relationship.

'user' table

pk name first action(fk) second action(fk)

1 John 1 2

2 Adam 1 NULL

3 Kelly 2 NULL

But it is bad idea because you have to change your schema if you want to add more action to one specific user. for example, user played baksetball, played soccer and played video game. you need to add third action(fk) and other user row will have null values.

so you can make another table to combine two tables without this problem. it would be second way I explained above. making another table to show relationship between two tables was just way to make it more efficient in manytomany situation. right?

Here is what i am confused from your answer. "more flexible" more flexible means it is more flexible to show m2m datas if you have bridge table like second way or just m2m relationship is more flexible? I am confused more than what?

Answer 1

It means there is another field named history_id to direct to related history with FK in user table. right?

Name it whatever you want, but yes.

I feel like second way seems more flexible because User table and History table are totally separated in individual tables. And Just another table have data to tell their relationship. But first way seems User table have a field 'history_id' in it's table. So I feel like its way is more coupling between two tables. ... Is my idea correct?

I got a kick out of the my idea. Yea, you're right. What you're calling a relationship table establishes what database admins call Many-to-many model.

• It's more flexible.
• It's also slower.
• It's more difficult to query.

The question is can a user have more than one history, and if they can't why are you going to make all of your queries more complex to support that?

In a normal M2M table in PostgreSQL you've got something like this,

CREATE TABLE user (
  user_id serial PRIMARY KEY,
  name    text   NOT NULL
);
CREATE TABLE history (
  history_id serial PRIMARY KEY,
  tz         timestamp DEFAULT NOW()
);
CREATE TABLE relationship (
  user_id int REFERENCES users,
  history_id integer REFERENCES history,
  PRIMARY KEY (user_id, history_id)
)

• What happens if you have one user with two histories?

like,

INSERT INTO relationship VALUES (1,1), (1,2);

• What happens if we have two users with the same history?

like,

INSERT INTO relationship VALUES (1,1), (2,1);

If these aren't possible in your app, don't go Many-to-many.