CODEWITHSUNDEEP
mysqlruby-on-railsdatabasedatabase-performance
maxedison
maxedison

Reputation: 17553

How to architect a database for related content types

I am building a website that offers training courses. Each training is made up of a series of training slides. There are currently 4 different slide types, but this could expand. The slide types have the following data (types in parentheses):

Type 1
summary (text)
goals (text)

Type 2
title (varchar)
subtitle (varchar)

Type 3
title (varchar)
content (text)

Type 4
title (varchar)
intro (text)
question (varchar)

I'm not sure of what the best way is to architect this in the database. I see the following possible options:

  1. Use a single table that contains columns for all slide types, with an additional column specifying the slide type
  2. Use a single table that contains columns for just the shared data types, and use additional tables for each non-standard field (I think Drupal does it this way, no?)
  3. Use a different table per slide type

Problems
2 & 3 would require a lot of table joins any time I want to assemble all the slides for an individual training, while #1 would result in the table having columns with lots of NULL values. How should I weigh these issues against each other? Are there standard best practice guidelines for this kind of situation?

Additional Considerations
- Slide Type 4 will also have a multiple-choice question associated with it, as well as different feedback associated with each answer choice (eg the user selects and answer and feedback pops up on screen). - I'm building this in Rails. Not sure if that matters either. - I don't expect my site to have more than 1000 slides, but I'd be interested to hear how the number of slides might affect the best approach.

Upvotes: 0

Views: 61

Answers (1)

ChristopherW
ChristopherW

Reputation: 1063

Given that Type 2, 3, and 4 all share similarities, I believe it would be worth while to just make a single slide type and reuse name depending on the slide. You could even specify the type as part of the table. Then, as you expand slides, just migrate up your slide table to new values. The only time I believe this would be a terrible idea is if you are expecting excessive amounts of values in your database. For example, if you had millions of slides, then the amount of data wasted might be an issue, but for a simple solution that simplifies the logic, one table is best.

This would be my suggestion for a migration for this so called slide table.

Type 2, 3 and 4 share slide_title which is the title

Goals, content and intro are all text so they can be shared in my opinion. They are also generic descriptions so the type would dictate the meaning of it. So, this is called slide_description.

create_table :slides do |t|
  t.column :slide_id, :string, :limit => 36, :null => false
  t.column :slide_title, :string, :limit => 64
  t.column :slide_description, :text
  t.column :slide_goals, :text
  t.column :slide_summary, :text
  t.column :slide_type, :integer, :null => false
  t.column :created_at, :datetime
  t.column :updated_at, :datetime
end

Now, each slide should have the ability to have questions associated with it so that each slide has_one question. This way, you can disassociate the questions from the slides at a later point if you need to use them elsewhere or change the question of the fly in an object oriented way thanks to active records.

create_table :questions do |t|
  t.column :question_id, :string, :limit => 36, :null => false
  t.column :question_title, :string, :limit => 64

  //Other things for questions

  t.column :created_at, :datetime
  t.column :updated_at, :datetime
end

Upvotes: 1

Related Questions