I'm working with PostgreSQL 10. I'd like to have a table that allows for the same value in a column, but not in the row. The table I created:
CREATE TABLE teams
(
team_id SERIAL PRIMARY KEY
team_lead character varying(250) NOT NULL,
member1 character varying(250) NOT NULL,
member2 character varying(250),
member3 character varying(250)
)
This would be allowed:
| team_lead | member1 | member2 |
------------------------------------------------
| Jane Doe | Bill Smith | Shirley Green |
| Jane Doe | Carol Lewis | John Doe |
This would NOT:
| team_lead | member1 | member2 |
--------------------------------------------
| Jane Doe | Jane Doe | Shirley Green |
| Bill Smith | Carol Lewis | Bill Smith |
Does anyone know how I would accomplish this? Thank you in advance.
Best Answer
This is called a "many-to-many" relationship. One person can be a member of multiple teams and a team has multiple members. One way to model this, could be: