CODEWITHSUNDEEP
mysqlsqldatabase
user3522444
user3522444

Reputation: 77

many to many table relationship dilemma

by constructing a brigde table I would link many salesman and group together

I've made three table like below

salesman
========
uId

salesGroupLinked
================
uId
groupId

group
======
groupId

so it should be able to allow 1 salesman can have many groups and also many groups can own by 1 salesman. But now I thought of expand the relationship.

Says I want each salesman can have their performances score for each group. Where should I put the column? If it's at group table then it's obsoleted, because groupId is unique, so it may give a score to many salesman.

Upvotes: 0

Views: 59

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146603

put the score in the link table.

The pk on that table should still be uId and groupId, but it should also have a column for the score.

EDITED to shopw DDL statements to Create table with Primary Key

create table salesGroupLinked
( uId integer not null, 
  groupId integer not null,
  performanceScore integer null,
  Constraint PK_salesGroupLinked Primary Key clustered (uId, groupId)
 )

The composite PK for this table itself guarantees that there can never be more than one row in this table with the same values for uId and groupId.

Upvotes: 0

Tomas Pastircak
Tomas Pastircak

Reputation: 2857

Just by thinking about the problem: What is the key of the performance score? It is settings of one salesman's group. So, the settings should be on the table where they are linked. It will be unique for each pair of (salesman, group).

So the table would look like

salesGroupLinked
================
uId
groupId
PerformanceScore

Upvotes: 1

Related Questions