I've got the above set of tables shown in the ER Diagram above.
members have many users.
members have many approval_groups.
approval_groups have many approval_group_users.
approval_group_users.member_id must be of the same member as it's approval_group.
approval_group_users.user_id must be of the same member.
I can add foreign keys to satisfy all these constraints but it involves creating composite unique keys on user.member_id and user.id as well as approval_group.member_id and approval_group.id.
It just feels wrong creating unique keys that use the primary key though. I'm aware that this entire model might be improved by not putting member_id in the tables and using link tables but as soon as I say something like approval_group.name must be unique by member that all breaks down and I'm back to where I started.
Am I crazy in thinking that these composite keys using the primary key are the only way to implement this constraint? I could just say forget it not bother enforcing integrity with users and approval_groups with their members.
One other note is that I added approval_group_user.member_id to satisfy the constraints I need. These are a simplified examples of existing tables in a real application that has no FKs defined currently. Adding a new field to these tables is an easy change. Adding new tables would be prohibitive.
I'd love some feedback from the pros. Thanks!
