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