User/Ecosystem Interaction - Many To Many Relationship with a catch - Help

Databases
1

I have two very simple tables, User and Ecosystem:


User
- ID [PK]
- loginName
# ...

Ecosystem
- ID [PK]
# ...

I want a many to many relationship, so I created another table, UserEcosystem:


UserEcosystem
- UserID
- EcosystemID
UNIQUE(UserID, EcosystemID)

All pretty standard stuff.

Now, the tricky part.

I want to make loginName unique, but only within a particular ecosystem. So, if a user doesn’t belong to a certain ecosystem, that loginName can be reused by a different user. However, if that login name is already used in that ecosystem, it can’t be.

Anyone have a way to do this at the database level? (I can handle the code level easy enough.)

Would just adding another foreign key to UserEcosystem be appropriate for it, then make a unique index between the three values?

There are actually at least four values like loginName that I need to account for, so I don’t know if that is a smart idea (that would be 6 columns in that table with 6 foreign keys and 4 multi-column UNIQUE indexes… doesn’t sound high performance =p).

Any ideas?

Thanks.

2

User

  • ID
    PK(ID)
  • loginName
    UNIQUE(ID,loginName) – redundant but needed for FK reference

UserEcosystem

  • UserID
    FK(UserID) references User
  • loginName
    FK(UserID,loginName) references User
  • EcosystemID
    FK(EcosystemID) references Ecosystem
    PK(UserID, EcosystemID)
    UNIQUE(loginName, EcosystemID)
3

Thanks.

4