issue with database modelling of many-tom-many

Question

I want two model 2 simple tables: Account & Manager.

Account can have multiple managers and Manager can have multiple accounts to manage. so we have many-to-many relation between them.

This is how I created them in the db:

CREATE TABLE Account (
    accountId int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    PRIMARY KEY (accountId)
);

CREATE TABLE Manager (
    managerId int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    accountId int NOT NULL,
    PRIMARY KEY (managerId),
    FOREIGN KEY (accountId) REFERENCES Account (accountId)
);

the problem which is probably obvious to you is that I will have duplicated names and different id's for the same manager, like here:

How would you recommend an sql newbie to do it? :)

I think the way I model it is a one-to-many...

`many-to-many` requires **3** tables: 2 main ones and one special *link* table. — PM 77-1, Jan 31 '18 at 22:44

score 0 · Answer 1 · answered Jan 31 '18 at 22:46

0

Create a Mapping table ( Egs Account_Manager) to Map Account and Manager and AccountID and ManagerID should be foreign Key.

Regards Abdul

answered Jan 31 '18 at 22:46

Abdul Azeez

807
10
18

score 0 · Answer 2 · answered Jan 31 '18 at 22:50

I would eliminate the accountid/foreign key from manager and introduce a new table to cross reference the two tables. Something like this:

CREATE TABLE ManagerAccount(
    id int not null auto_increment,
    managerId int not null,
    accountId int not null,
    primary key(id),
    foreign key(managerid) references Manager (ManagerID),
    foreign key(accountId) references Account (AccountID)
)

Maybe throw a unique index over the two foreign keys.

score 0 · Answer 3 · answered Jan 31 '18 at 22:58

Implement a many-to-many relationship with a third table, with foreign keys to the two related tables.

As an example:

person
  id
  person_name

and

club
  id
  club_name

A club can have zero, one or more members; a person can be a member of zero, one or more clubs. It's a many-to-many relationship.

The simplest form of the relationship table:

membership
   club_id       PK, FK ref club.id
   person_id     PK, FK ref person.id

could be defined ...

CREATE TABLE membership
( club_id    INT NOT NULL COMMENT 'PK, FK ref club.id'
, person_id  INT NOT NULL COMMENT 'PK, FK ref person.id'
, PRIMARY KEY (club_id, person_id)
, KEY membership_IX1 (person_id)
, CONSTRAINT FK_membership_club   FOREIGN KEY (club_id)   REFERENCES club (id)
, CONSTRAINT FK_membership_person FOREIGN KEY (person_id) REFERENCES person (id)
)

We note that this relationship itself might have attributes, such as date joined and date resigned. There might also be status (provisional, active, probationary), and we might want to track offices or role within the club.

The relationship might turn out to be more than just a junction or link table. It may actually be an entity in our system. And we probably want to handle it like an entity, adding a separate id column, and also consider removing the requirement that (club_id,person_id) be unique.

score 0 · Answer 4 · answered Jan 31 '18 at 22:59

Usually, the best way of going about modeling a many-to-many relationship is by creating a separate table to hold it. Using your example:

CREATE TABLE Account (
   accountId int NOT NULL AUTO_INCREMENT,
   name varchar(255) NOT NULL,
   PRIMARY KEY (accountId)
);

CREATE TABLE Manager (
    managerId int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    PRIMARY KEY (managerId),
);

CREATE TABLE Account_Manager (
        id int NOT NULL AUTO_INCREMENT,
        accountId int NOT NULL,
        managerId int NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY (accountId) REFERENCES Account(accountId)
        FOREIGN KEY (managerId) REFERENCES Manager(managerId)
 );

This way, any association between a Manager and an Account will be present in the Account_Manager table (e.g. (5,1) would represent patrick's association with the account with accountId = 5). However, to fully understand why this is the most common approach, I'd recommend you read about normalization.

issue with database modelling of many-tom-many

4 Answers4