CODEWITHSUNDEEP
mysqldatabase-design
pg-robban
pg-robban

Reputation: 1435

How should I structure this database?

I'm writing some books for a website where the contents will be accessed through a database (MySQL) and shown on slides (using PHP and JQuery). A slide here is a chunk of HTML data to output for that particular slide.

Now what I want to achieve is some Powerpoint-esque technique where paragraphs and bullet points become added to slides, for example:

Slide one:

<p>Lorem impsum...</p>

Slide two:

<p>Lorem impsum...</p>
<p>Dolor sit amet...</p>

In other words, there is going to be a lot of redundant information because of how most slides contain text already available in other slides. So is there a reasonably easy way to structure the database for this or should I rather save the full text of each slide as it is?

The structure I have right now is:

books
  id
  name

chapters
  bookid references books.id
  chapterno
  chaptername

sections
  bookid references chapters.bookid
  chapterno references chapters.chapterno
  sectionno
  sectionid references sectiontexts.id

sectiontexts
  id
  text

One of the other problems here for example is, if say a chapter has 10 slides and I delete slide 3, then I will have to change section numbers of sections 4-10.

Upvotes: 1

Views: 83

Answers (2)

Tim
Tim

Reputation: 5421

If it is desirable to be able to make a correction to a chunk of text in one place and have the correction appear everywhere that chunk is used, then you should store the chunks as separate items and refer to them in the body of the item in which they are to appear, bringing the referred to item into the body at display/rendering time.

As for section numbers being deleted, just store a decimal value (2.0, 2.5, 3.0) which, when sorted, yields the order in which items are to appear, but calculate the "display number" on-the-fly. That way, gaps do not matter, and you can easily insert a section, e.g. a section inserted between section 4.0 and section 5.0 would be given 4.5, another section inserted between 4.0 and 4.5 would be given 4.25, and so on.

Upvotes: 1

Paul Sonier
Paul Sonier

Reputation: 39510

I'd save the full text of each slide. It's a reasonably common occurrence for users to have subsequent slides in PowerPoint presentations that modify the previous contents. As well, the amount of data required to store each slide is really very small, since you're dealing with text data, and the size increase is so small that it doesn't even particularly matter when pitted against the increase in complexity of the slide-to-previous-slide association design. Go with full text for each slide.

Upvotes: 1

Related Questions