Relacje użytkownik - role, problem z zaprojektowaniem

Witam.

Mam mały problem z zaprojektowaniem bazy danych.

W bazie chcę mieć użytkowników i role. Użytkownik może mieć dowolną liczbę ról. Wobec tego zrobiłem 3 tabele:

1. Users (Id, Name, e-mail... (i więcej kolumn))) . Id jest PK
2. Roles (Id, Name). Id jest PK
3. UserRoles (UserId, RoleId). Relacja wiele do wielu.

Wszystko ładnie działa. Ale trzeba dodać jeszcze jedną funkcjonalność - użytkownik może używać w danym czasie tylko jednej roli - spośród swoich ról - ta rola oczywiście musi być pamiętana w bazie.

Stworzyłem więc na razie taką tabelę:

4. UserCurrentRoles (UserId, RoleId) .

UserId jest tu kluczem główny, unikatowy - bo użytkownik ma tylko jedną rolę "bieżącą". Ale chyba taki model bazy nie ma sensu - bo jak ktoś usunie/zmieni tabelę UserRoles, to będą konflikty w UserCurrentRoles. Wiem, że można dodać triggery - ale czy nie da się tego lepiej zaprojektować?

Jak zmienić te strukturę aby można było uzyskać to co opisałem? Z góry dzięki za pomoc.</i>

Deti napisał(a)

Ale trzeba dodać jeszcze jedną funkcjonalność - użytkownik może używać w danym czasie tylko jednej roli - spośród swoich ról - ta rola oczywiście musi być pamiętana w bazie.

nie bardzo rozumiem co to znaczy, że może używać tylko jednej roli

Stworzyłem więc na razie taką tabelę:

4. UserCurrentRoles (UserId, RoleId) .

UserId jest tu kluczem główny, unikatowy - bo użytkownik ma tylko jedną rolę "bieżącą".

a nie prościej dodać kolumnę do tabeli usera? Przecież ta tabelka to jest 1-1 do usera i 1-1 do roli

Ale chyba taki model bazy nie ma sensu - bo jak ktoś usunie/zmieni tabelę UserRoles, to będą konflikty w UserCurrentRoles. Wiem, że można dodać triggery - ale czy nie da się tego lepiej zaprojektować?

to załatwia odpowiedni FK - odpowiednio trzeba ustawić referential actions

nie bardzo rozumiem co to znaczy, że może używać tylko jednej roli

Takie są założenia programu - to znaczy - z punktu widzenia serwera - w danym czasie dla użytkownika może nie być ustawione lub być ustawiona dokładnie jedna rola aktywna - oczywiście spośród ról jakie posiada użytkownik.

a nie prościej dodać kolumnę do tabeli usera? Przecież ta tabelka to jest 1-1 do usera i 1-1 do roli

to załatwia odpowiedni FK - odpowiednio trzeba ustawić referential actions

No właśnie, .. tylko nie wiem jak ustawić FK gdy ma się odwoływać do tabeli UserRoles - czyli praktycznie do relacji wiele do wielu.

jak wiele do wielu - jedn do wielu.
Jak ktoś usunie role z listy dostępnych to co chcesz wstawić userom, którzy mieli ją ustawioną?
Bo rozumiem, że nie pozwalasz zmieniać wartości w kolumnach, które są PK

Jak ktoś usunie role z listy dostępnych to co chcesz wstawić userom, którzy mieli ją ustawioną?
Bo rozumiem, że nie pozwalasz zmieniać wartości w kolumnach, które są PK

PK się nie zmienia.

Jeśli ktoś usunie rolę z tabeli Roles (lub użytkownika z tabeli Users) -> automatycznie usuwa się odpowiedni rekord(y) w tabeli UserRoles (ON DELETE CASCADE)

Gdyby teraz zastosować twój pomysł - czyli dodać kolumnę "ActiveRole" do tabeli Users, nie wiem jak skonfigurować klucze, aby modyfikacje tabeli UserRoles powodowały automatyczne ustawienie NULL'a dla kolumny ActiveRole, jeśli ta role już nie występuje w rolach tego użytkownika.

Ok, można dać triggery - ale czy nie da się tego lepiej zrobić ?

1. jak usunie usera to usunie i rekord - nic tu nie trzeba zmieniać
2. ActiveRole zamiast wskazywać na UserRoles może wskazywać na Roles i tutaj już nie masz problemu - On DELETE CASCADE załatwia sprawe

. ActiveRole zamiast wskazywać na UserRoles może wskazywać na Roles i tutaj już nie masz problemu - On DELETE CASCADE załatwia sprawe

Racje, jednak .. - skasowanie roli to najmniejszy problem. Problemem jest usunięcie jednej roli z listy ról użytkownika (UserRoles). Po usunięciu mamy w bazie dziwny konflikt - użytkownik ma przypisane role A, B, C, a jako ActiveRole - ma rolę D.