0

Sorry for my vague title, but I didn't really find how to succinctly formulate my issue. I have the following story I want to model:

There are Users that belong to a certain Group. These Users can place Items in that group, and they are 'visible' to all other Users in that Group only.

How I would initially do it: have a table for User, Group and Item. I would then place a UserGroup mapping table to map the Users in their respective Group, and the Item would have a Foreign Key to Group and User. Now I'd like to enforce also that when a User places an Item within a Group, that that user would actually belong to that Group in the first place, as to not have that Items could be placed in a Group by Users who don't belong to that particular Group, and I am having trouble wrapping my head around how to cleanly do this.

Can/should I enforce this on the DB level? If so, what would be an elegant solution to this? I am using SQL Server if this matters.

Edit: after thinking a little more on it, I would also add the following requirements: an Item can only be created by a User, so it shouldn't be able to be spawned into existence on its own. However, upon deletion deletion of a User, the Item should still exist, showing the User has been deleted. On deletion of a Group, the Item shouldn't necessarily 'stay alive'.

0

I think you are looking to use a composite foreign key from the table that models the items, to the table that models the group relationship, not to the parent users and groups, something along the lines of (pseudo code):

CREATE TABLE Users (UserIdentifier, (other attributes)); 
CREATE TABLE Groups (GroupIdentifier, (other attributes)); 
CREATE TABLE Items (ItemIdentifier, (other attributes));

CREATE TABLE Users_Groups (UserIdentifier REFERENCES Users, GroupIdentifier REFERENCES Groups);

CREATE TABLE Itmes_Users_Groups (Item REFERENCES Items, UserIdentifier, GroupIdentifier, (UserIdentifier + GroupIdentifier REFERENCES User_Groups))

HTH

  • Thanks, that does make sense to make a composite FK to the relation table. In your case, it could be possible though that there are Items that don't belong to a UserGroup, and this is something I also would want. Would it make sense then to actually have this reference in the Items table itself, and make that composite key not nullable? I am also already imagining though this being a pain if some User would be removed and I would want to still display the Item with a '(deleted)' User.. maybe fix that with a cascade delete and have a -1 deleted User record... (just thinking out loud here) – Christophe Loeys Dec 14 '18 at 19:05
  • We are not mind readers. Please specify the full requirements if you want us to be able to help. – SQLRaptor Dec 14 '18 at 21:30
  • Sorry, as I said, I was just thinking out loud a bit since I got new ideas of that... I did edit my question now though. – Christophe Loeys Dec 14 '18 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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