Search code examples
mysqldatabase-designdatabase-schemaorganizationproject-organization

MySql/PHP - Designing my first database. Need reviews

I'm designing my first database and writing my first PHP project from scratch and I need someone to review database scheme.

Purpose of PHP script: To display traffic statistic for various sites in my network when I publish promotional videos on sites like Youtube, Myspace, Metacafe, Vimeo etc.

How I plan to organize it:

  1. One site can have multiple categories like "guitar lessons", "singing lessons", "dancing lessons".
  2. Some categories can have subcategories. Like "guitar lessons" can be split to "bass" and "electric"
  3. In one category can be one promotional video. On image below, video is named item.
  4. One item (promotional video) can be created in only one category
  5. One promotional video can be submitted to multiple video sharing sites (Youtube, Vimeo etc..)
  6. One promotional video can have several different meta data like "title" and "description"

Scenarios that can happen:

  1. Maybe someday Youtube will delete one of my promotional video, in that case I would reupload it again with probably different "title" and "description", but I would like to show some old info in statistic like previous number of views before deletion.
  2. I can't think any other right now..

EDIT: I have added table itemStats enter image description here

EDIT2: I'm not sure if I'm going to need separate itemStats table. I'm thinking to join it with itemMeta?

Solution

  • I would suggest that you combine users and usersMeta into one table. There are only 5 unique, useful fields in usersMeta. You will need to utilize joins in order to get all of that data if you ever need to, it's unnecessary in my opinion. Remember, normalization is the process for removing redundancies and ensuring all tables have the minimum amount of fields...but this comes at a cost. The cost being performance in most cases.

    Besides, the users table would only be up to 9 total fields by combining them. That is still what I consider to be a very small table. I've had tables with 20-30 fields before. In my case it made no sense to separate that data out because it was unique to the table and no other table or part of the system would have ever utilized that data anyway.