1

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?

Jayground
  • 1,797
  • 1
  • 17
  • 32
  • I updated to use the more terse create table syntax. – Evan Carroll Dec 08 '16 at 01:34
  • I think there is some bugs in the above, not sure how you want to clairify them but I left them as-is. – Evan Carroll Dec 08 '16 at 01:34
  • couple of points too, don't use `character varying(80)` for `name`, use `text`. Also, don't use `id`, use `user_id` or `id_user` (my favorite). if you label all pkeys `id` you can't use `USING` or `NATURAL JOIN`, it also makes your queries more confusing. – Evan Carroll Dec 08 '16 at 01:36
  • Thank you, I just wanted to explain the concept of my question with example tables. I didn't pay attention to syntax actually. Well do you have any idea for my question? I wanted to know if two ways to make relationship between two table with FK have difference or if one is more flexible than another. – Jayground Dec 08 '16 at 01:45

1 Answers1

0

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.

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
  • It will be relationship between one user and many histories. Actually for this purpose, I don't have to make many-to-many model. I know this point. But currently, data structure in database could change later. I knew that it would be harder to retrieve data and save data if i make many-to-many relationship. I thought it could be better to keep those tables separately and make it more flexible for change in the future. Because M2M relationship can cover function of just how to call oneToMany relationship(?) which I explained with second way above question. – Jayground Dec 08 '16 at 02:15
  • If you have one user and many history, and one history to many users is not possible then simply store the FOREIGN KEY on the history. `CREATE TABLE history ( id SERIAL NOT NULL, user_id int REFERENCES user );` Now you can insert two history(s) that point to the same user. – Evan Carroll Dec 08 '16 at 02:19
  • So is your opinion is that you'd better not to use M2M relationship if you are not necessary and must use M2M as possible? I want to know this flexibility of M2M model could be better for refactoring and modification in the future even though you are going to get harder to handle it in the first place. – Jayground Dec 08 '16 at 02:19
  • my advice is to stay away from m2m until you *need* many-to-many. – Evan Carroll Dec 08 '16 at 02:20
  • can you give me more clear reason for your advice? It is because performance(slower) and difficulty of handling(query) is more critical than flexiblity(more)? or actually this flexibility is not much beneficial to have those slower performance and difficulty to query at all? I hope you can explain why m2m is more flexible in more detail. definition of 'more' seems quite ambiguous. It would be awesome if you can give some real example. – Jayground Dec 08 '16 at 02:23
  • You can always change it when needed. It's not difficult. Until then it's a premature optimization. It's features you don't need now that come at a cost now. That's bad design methodology imho. – Evan Carroll Dec 08 '16 at 02:37
  • please check my answer. I think comments are getting longer. so I updated my question to ask meaning of 'more flexible' you mentioned in your answer – Jayground Dec 08 '16 at 03:27