0

I am working on a site that will allow users to submit video, images, and another guides (multiple steps that include text and an image).

On the main page of the site, there will be a list of the last 5-10 items or so that were uploaded.

Originally, I was planning on having a table for each, so 'videos', 'images', and 'guides'. The 'guides' table would have another table 'steps' so each step would be it's own row with a 'guide_id'.

On the main page, each of these elements would required different code to render (iframe for videos, img tag for images, etc...).

Is there a better way to create the database for this? Maybe a content item table that has all the different content in it with a 'type' column?

If not, what would be the best way to write a query to select 5-10 pieces from all three tables and be able to tell what type of content each row of the result would be so it can be rendered with the proper code?

By the way, I am using Laravel PHP framework if that influences that answer.

Sean
  • 1,758
  • 3
  • 20
  • 34

2 Answers2

0

Put both in one table example name it media and add columns like media type

Shehabic
  • 6,787
  • 9
  • 52
  • 93
0

Use 2 tables. media and media_type.

media table:

id       name     uploaded_at                  media_type_id
-------------------------------------------------------
1        aaa      2012-11-17 11:17:13            1
2        bbb      2012-11-17 11:20:11            2
3        ccc      2012-11-17 11:32:12            1
4        ddd      2012-11-17 11:45:10            3

media_type table:

id       name
----------------------
1        video
2        image
3        guide

This is the basic normalized database structure for your model. You can add extra columns description, etc.. required for your media table. Further you can add, remove media types and change reference to media table as required. Having this structure, you can query as follows.

Last 5 media uploaded:

SELECT id, name FROM media ORDER BY uploaded_at DESC LIMIT 5;

Last 5 media of a particular media type:

SELECT media.id, media.name FROM media INNER JOIN media_type ON media.media_type_id = media_type.id 
  WHERE media_type.name = 'video' ORDER BY uploaded_at DESC LIMIT 5;
chandimak
  • 201
  • 3
  • 17
  • When designing a database, is it recommended to have fewer larger tables, or more smaller tables? – Sean Feb 27 '13 at 15:17
  • It's always arguable. It's better having normalized as a start(this structure). You can think about denormalizing or changing the structure when data and access rates get increased. Table joins costs more than single tables when retrieving data. In this particular case, having above mentioned structure even you can query through `media_type_id` instead of `media_type.name` after identifying it properly without a JOIN. You have more [here](http://stackoverflow.com/questions/59482/whats-the-better-database-design-more-tables-or-more-columns). – chandimak Feb 27 '13 at 15:55