Doubt relating three tables

Question

Doubt relating three tables

Navigation

#1 by (0 votes)

1

I have a question about how to relate several tables. I explain a little what I want to relate:

User table: user_name (primary key)
Table Computer: computer_name (primary key), user_name (foreign key that refers to user_name of the User table)

With this relationship, I get a user to control more than one team but that team can only control that user.

With which we would have the following relationship: User 1 ---------- 0 .. * Teams

This way some possible records would be:

user1, equipo1
user2, equipo2
user3, equipo3

Table Group: group_name (primary key)

My problem comes from wanting that in each group there may be different teams, but that these teams are from different users (that is, a user can not compete with two teams of his in the same group).

In this way, a ternary relationship 1: 1: 1 would be created between the tables User, Teams, Groups (we could call Team_Groups) where the primary key would be formed by the attributes that form the primary key of two tables of the relationship any (for example, user_name and group_name).

This way some possible records of Team_groups would be:

user1, grupo1, equipo1
user2, grupo1, equipo2

The problem is that I think that I would also have to create a foreign key composed between user_name and computer_name, otherwise, this could be the case:

user1, grupo1, equipo1
user3, grupo1, equipo1

As you can see, the last record fulfills the primary key conditions (since user3, group1 is different than the first record) but we see that the combination user3, team1 does not exist in the first relation we have seen (since team1 it's from user1). With that, the record could add it to the table when you really should not let it add. Logically, another foreign key would be the group_name that referred to the group_name of the Group table.

This could control it by declaring this relationship, with foreign keys, etc. in any DBMS, whether Access, Mysql, etc. and not by programming?

Greetings and I hope you can help me.

Thank you very much.

mysql

asked by franxiscu 29.01.2017 в 19:47

source

1 answer

"Bin" command in MenuItem Reading packets from a server with Java sockets

score 0 · Answer 1

So that the design of the model solves by itself the problem that you pose, you must first define a primary key with group and user (this will prevent a user from existing twice in the same group) and another foreign key with user and Team to the table Team:

I give you an example of the definition:

CREATE TABLE nombre_grupo 
(
    grupo_id int NOT NULL,
    user_id int NOT NULL,
    equipo_id int NOT NULL,
    PRIMARY KEY (grupo_id, user_id ),
    FOREIGN KEY (user_id, equipo_id) REFERENCES Equipos(user_id, equipo_id)
)