One-to-many or many-to-many?

This is BestPractice question (or at least I hope it is).

EDIT: This example is just to clarify the problem. It could be Person, Office, ContactInformation.

The problem:

There are 3 tables:

RightsSet
(
    ID,
    CanView,
    CanEdit,
    ObjectTypeID,
    ObjectID
)

User
(
    ID,
    Username,
    Password,
    ProfileID
)

ProfileID
(
    ID,
    Name,
    Description
)

Both User and Profile can have multiple RightsSets. RightsSet must belong to 1 (and only 1) User or Profile.

How to achieve this? Should I create additional tables

UserRightsSets
(
    UserID,
    RightsSetID
)

ProfileRightsSets
(
    ProfileID,
    RightsSetID
)

The problem with this is: How to make sure when ProfileRightsSet is deleted, the RightsSet is also deleted (because that RightsSet belongs to profile and to profile alone)? How to make sure RightsSet belongs only to profile and not to Profile and User?

Alternatively, I could modify RightsSet table

RightsSet
(
    ID,
    CanView,
    CanEdit,
    ObjectTypeID,
    ObjectID,
    Use开发者_开发百科rID,
    ProfileID
)

The problem with this... Well, what if multiple objects share RightsSet? (Ok, I can't think of any example, but I'm sure there are valid scenarios where more than 2 kinds of entity share one kind of entity.)

---

Add a supertype Entity table that has subtypes User and Profile (1:1 relationship with both).

Then, let the Entity and RightsSet be in 1:n relationship.

---

This was a tough one for me to wrap my head around but you might consider the structure below:

Entity
---------
EntityId
ProfileId --nullable
UserId --nullable

RightsSet
----------
EntityId
CanView
CanEdit
ObjectTypeID
ObjectID

With this method you can specify a UNIQUE constraint on ProfileId and UserId in the Entity table and then create as many records in RightsSet as is needed. Two possible problems would be when ProfileId and UserId in Entity are both null or when they both have values. You could probably do some validation before you insert records to ensure this does not happen.