Mysql – designing data model similar to Power Point Presentation slides

database-designMySQL

I am trying to come up with a data model that would adequately describe business rules, similar to power point presentations with one exception. The structure of the slides is predefined – each slide has a slide style. I need to store the contents the user inputed for that style (which image he/she chose, what is the String in heading 1 or 2…)

By slide style I mean that some slides have 1 heading, some 2, some 3 others, some have 1 image in it, others 8…some have a background video, some solid color, others backgorund image..

The order of slides in a presentation is also important. Users can add slides to presentation, remove them, replace them. One slide style can be repeated multiple times in a project. If you want to print the presentation, it gets printed in the correct order.

How would you construct such data model?

I've created users and projects table, but I am having problems with how to represent and slide_style. slide_style has so many different options and I see no elegant way of doing this. How would you do this?

slide_style and project are in M:N relationship, so I will create another table project_slides and link them together, but how to represent so many different styles is the real issue here. 

CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(256) DEFAULT NULL,
  `password` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE `projects` (
  `project_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`project_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Best Answer

It sounds like you are trying to store a whole lot of information, as if you are creating an entire Powerpoint application. Not a bad thing, but as you have indicated you do not want to just use Slide Templates in files, you have to store everything about the Slide (both Templates and user Slides) in the database. This would enable you to reproduce the template on the fly based on the data saved once the user creates their own Presentation, based on a template.

I would approach this with a data model that uses an Entity-Attribute-Value method to store (what could be) a large number of Elements on your slides. The following is the data model I have come up with for you:

enter image description here

The interesting part, that you are probably struggling with, is on the right hand side of the model. SlideElement is an Element object that appears on your Slide. Each Element has a defined set of Attributes as described by the type of element you are adding.

This may not make much sense to you unless you see some example data as shown below:

enter image description here

So each Attribute defines the Attributes of an Element - an Element (could also be called "Control" or something similar) being a text box/picture/video, in the above data. Elements are placed on Slides at particular locations, defined by the X/Y Position as described on the Element table and with a particular size (Length/Width). The Element entity, along with all the Attributes, is an instance of an Element, with its value described by the "Value" field. For a text box, the value would be its text/text size/colour/back colour etc, for videos/photos, their location/format/encoding/type etc.

Note the "PresentationType" field on the Presentation. I have put this as the identifier to decide if the file is a "Template" or "User" presentation. If the user chooses one of the Template files, the Presentation/Slide/SlideElement/Element rows would all be copied from the "Template" tables and re-inserted for the User tables. The default Element values would therefore be used to start with, with the user overwriting these as they work on their Slide design. The user would be able to move the elements around their slide as well, this would be reflected in the "SlideElement" table XYPos values as well as the size of the Element. The user could also add Elements, like a new text box or photo, which would create a new set of Element rows based on what was added. You would need a default list of Elements to add for each type, that step I haven't modelled but could be done easily.

Related Question