4

Consider the following table structure:

UserGroup(ie. 'Admin')
    ID = 'Admin'
    ApplicationFK = 'App1'

JoinTable
    UserGroupFK = 'Admin'
    PermissionFK = 'Approve'
    PermissionValue = 'READ'

Permission(ie. 'Approve')
    ID = 'Approve'
    ApplicationFK = 'App2'

UserPermission
    UserFK = 'John'
    PermissionFK = 'Approve'
    PermissionValue = 'WRITE'

User
    ID = 'John'

Where you have a jointable between UserGroup and Permission, with both UserGroup and Permission having foreign keys to Application.

Is there any way to enforce integrity, to avoid the situation above, where the ApplicationFKs don't match? I cannot simply remove one of the Foreign Keys, as it is possible to have a Permission without any UserGroups, and possible to have a UserGroup without any permissions.

6
  • 3
    Yes, I call this the "diamond" problem. Check any of these questions/answers: diamond Commented Jul 6, 2017 at 21:34
  • 3
    The "trick" is to add ApplicationFK to the lowest table in the diamond (JoinTable in this case) and modify the Foreign Key and Unique constraints accordingly. Commented Jul 6, 2017 at 21:35
  • @ypercubeᵀᴹ I've added additional information about related tables I'd previously thought unnecessary. I've also created an answer, attempting to confirm I've correctly understood your suggestion - is it correct?
    – Sarov
    Commented Jul 7, 2017 at 13:57
  • No, it doesn't look what I has thinking of. I'll add an answer later tonight. Commented Jul 7, 2017 at 14:00
  • @ypercubeᵀᴹ Updated below answer after some more thought.
    – Sarov
    Commented Jul 7, 2017 at 14:24

2 Answers 2

1

Making sure I understand/have correctly elaborated on @ypercubeᵀᴹ's comment/linked answer:

The correct structure should be:

UserGroup(ie. 'Admin')
    ID = 'Admin'
    ApplicationFK = 'App1'
    [PK: ID, ApplicationFK]

JoinTable
    UserGroupFK = 'Admin'
    ApplicationFK = 'App1'
    PermissionFK = 'Approve'
    PermissionValue = 'READ'
    [PK: UserGroupFK, ApplicationFK, PermissionFK]
    [FK -> UserGroupFK+ApplicationFK -> UserGroup]
    [FK -> PermissionFK+ApplicationFK -> Permission]

Permission(ie. 'Approve')
    ID = 'Approve'
    ApplicationFK = 'App1'
    [PK: ID, ApplicationFK]

UserPermission
    UserFK = 'John'
    PermissionFK = 'Approve'
    ApplicationFK = 'App1'
    PermissionValue = 'WRITE'
    [FK -> PermissionFK+ApplicationFK -> Permission]

User
    ID = 'John'
2
  • The row you show in JoinTable would be invalid, as you don't show a Permission row ('Approve','App1')
    – RDFozz
    Commented Jul 7, 2017 at 15:47
  • Also - Wherever you're tying to Permission, you need to provide the full key: the permission ID and the application ID. The only real difference between UserPermission and JoinTable is one should assign permissions for an application to a user, and the other to a user group.
    – RDFozz
    Commented Jul 7, 2017 at 15:51
0

The question will be solved after you define primary keys correctly.

Please remember that FK must point to other table's primary key (or unique key).

It is legal (at least in SQL) to use multi-field FKs.

Not the answer you're looking for? Browse other questions tagged or ask your own question.