I'm working on a privacy settings problem, which is proving to be cumbersome to design.
Essentially I have:
- User created content
- User can define access groups for content.
- Access groups vary depending on relationship of the viewer to the creator (this is the challenge).
This is for an education system with user created content, teams etc...
Privacy settings for example would be:
Below is my Schema so far. The challenge is not storing the data but determining the relationships between the viewer and the creator of the content in a query with the benefits of limit/offset etc...
-- Create syntax for TABLE 'privacy_settings' CREATE TABLE `privacy_settings` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `privacy_id` int(11) unsigned NOT NULL, `model_name` varchar(55) NOT NULL DEFAULT '', `model_id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `privacy_id` (`privacy_id`), CONSTRAINT `privacy_settings_ibfk_1` FOREIGN KEY (`privacy_id`) REFERENCES `privacy_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; CREATE TABLE `privacy_types` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;