Sql-server – Correct Model for Users in a Collection with Items in that Collection

database-designsql server

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'.

Best Answer

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))