Best way with relation tables

Question

I have a question about tables and relations tables ...
Actually, I have these 3 tables

CREATE TABLE USER (
  ID int(11) NOT NULL AUTO_INCREMENT,
  NAME varchar(14) DEFAULT NULL
);

CREATE TABLE COUNTRY (
  ID int(11) NOT NULL AUTO_INCREMENT,
  COUNTRY_NAME varchar(14) DEFAULT NULL
);

CREATE TABLE USER_COUNTRY_REL (
  ID int(11) NOT NULL AUTO_INCREMENT,
  ID_USER int(11) NOT NULL,
  ID_COUNTRY int(11) NOT NULL,
);

Ok, so now, 1 user can have one or more country, so, several entries in the table USER_COUNTRY_REL for ONE user.
But, my table USER contains almost 130.000 entries ...
Even for 1 country by user, it's almost 10Mo for the USER_COUNTRY_REL table.
And I have several related tables in this style ...

My question is, is it the fastest, better way to do?

This would not be better to put directly in the USER table, COUNTRY field that contains the different ID (like this: "2, 6, ...")?

Thanks guys ;)

Its fine just create foreign keys and primary keys as @hjpotter92 wrote Create Indexes. — Ali Exalter, Nov 20 '13 at 14:27
I have indexes ... I was just wondering if there was not a better way. And if it was the right way. — , Nov 20 '13 at 14:27
It is the right way, except that your PK on the `USER_COUNTRY_REL` table should be on the two columns `(ID_USER , ID_COUNTRY)`, and you really don't need `ID` at all. — Charles Bretana, Nov 20 '13 at 14:31
To answer the original question; in a word, no. In two words, definitely not. — Strawberry, Nov 20 '13 at 14:35

Klazen108 · Accepted Answer · 2013-11-20T14:41:34.047

1

The way you have it is the most optimal as far as time constraints go. Sure, it takes up more space, but that's part of space-time tradeoff - If you want to be faster, you use more space; if you want to use less space, it will run slower (on average).

Also, think of the future. Right now, you're probably selecting the countries for each user, but just wait. Thanks to the magic of scope creep, your application will one day need to select all the users in a given country, at which point scanning each user's "COUNTRY" field to find matches will be incredibly slow, as opposed to just going backwards through the USER_COUNTRY_REL table like you could do now.

In general, for a 1-to-1 or 1-to-many correlation, you can link by foreign key. For a many-to-many correlation, you want to have a relation table in between the two. This scenario is a many-to-many relationship, as each user has multiple countries, and each country has multiple users.

edited Nov 20 '13 at 14:41

answered Nov 20 '13 at 14:33

Klazen108

690
4
19

1

The "[space–time tradeoff](http://en.wikipedia.org/wiki/Space%E2%80%93time_tradeoff)" would be a bit more appropriate than "Heisenberg's uncertainty principle of Computing". :) – eggyal Nov 20 '13 at 14:39
@eggyal aha I knew there was an official name for it :) – Klazen108 Nov 20 '13 at 14:40
1

@Volvox no, you don't need one for one-to-many, but this scenario is many-to-many, as each country can have many users, and each user can have many countries. I'll clarify my answer. – Klazen108 Nov 20 '13 at 14:44

score 0 · Answer 2 · answered Nov 20 '13 at 14:32

0

Why not try like this: Create table country first

CREATE TABLE COUNTRY (
  CID int(11) NOT NULL AUTO_INCREMENT,
  COUNTRY_NAME varchar(14) DEFAULT NULL
);

Then the table user:

CREATE TABLE USER (
  ID int(11) NOT NULL AUTO_INCREMENT,
  NAME varchar(14) DEFAULT NULL,
  CID Foreign Key References CID inCountry
);

just Create a Foreign Key relation between them.

If you try to put this as explicit relation , there will lot of redundancy data.

This is the better approach. You can also make that Foreign Key as index . So that the databse retrieval becomes fast during search operations.

hope this helps..

Note : Not sure about the exact syntax of the foreign key

answered Nov 20 '13 at 14:32

Sai Avinash

4,683
17
58
96

How does this support the requirement to have more than one country for a user? – D Mac Nov 20 '13 at 14:36
Actually, I can have more than one country for a user ... but thank you :) – Nov 20 '13 at 14:42
1

Actually, he says "Ok, so now, 1 user can have one or more country" – D Mac Nov 20 '13 at 15:16

Best way with relation tables

2 Answers2

Linked