What's the best SQL scheme for this project?

Question

Hi, i want to make a php app that let people submit photos/videos/sounds

Now, everything uploaded a record will be added to SQL database with file infos in this way:

Photos_table : File_Name | Picture_Type | Picture_Width | Picture_height

Videos_table : File_Name | Duration | Codec

Sounds_table : File_Name | Bitrate | Duration | Codec

The App will displays all recent items of all kinds. Is it good to do like the previous scheme, or making all infos in ONE table like this :

File_Name | Picture_Type | Picture_Width | Picture_height | Duration | Codec | Bitrate

I mean what's the fastest way to gather the infos, i just think the first scheme is more organized than the last one.

Thanks

Answer 1

Use the first scheme - as each table represents a different type with different information.

If you had the second scheme, you would end up with lots of nulls in your table.

Most DB professionals would go with the first one, as it is more normalized.

Answer 2

Definitely not all in one. If you are uploading media then I would create a table of all media:

Media_table: ID | MediaPath | ETC

Photos_table : MediaID | Picture_Type | Picture_Width | Picture_height

Videos_table : MediaID | Duration | Codec

Sounds_table : MediaID | Bitrate | Duration | Codec

Look into database normalization.

Answer 3

There will not be a big performance difference. However, using your second method does denormalize your database a bit which is considered bad practice.

Answer 4

Although the answer by durilai is the best, if you somehow don't agree with it you can also keep the three separate tables and combine their results in one of two ways:

• 1 query with a UNION.
• 3 queries, combined afterwards with PHP.