Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  • Teammates
  • Coaches
  • Mentors
  • Friends

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;
share|improve this question

closed as unclear what you're asking by Max Vernon, RolandoMySQLDBA, Paul White, Mike Walsh, Mark Storey-Smith Nov 6 '13 at 18:54

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
What does "limit/offset" has to do with a database design? –  ypercube Mar 29 '12 at 19:57
    
What are model_name and model_id for? You never mention them in your problem description. –  FrustratedWithFormsDesigner Mar 29 '12 at 20:13