CODEWITHSUNDEEP
databasesqlitedatabase-design
rushd
rushd

Reputation: 363

How can I design a better or functional database for a video site with what I have?

First time designing a database after perusing some text on the topic.

I'm trying to design a database for a video/image website with only a few contributors. I've looked over a dozen examples of other people have similar problems, and I attempted to design my own.

There are users. The users have roles that dictates what permission they have. The user can upload files (video or image) and add category/tags to them.

This is what I have so far before I begin adding a comment system which SO has a few great examples out there already.

    USER PROFILE
    email
    username
    password
    gender
    date_of_birth
    date_created
    date_modified

    ROLES
    name
    user_profile_id

    FILE
    title
    size
    date_created
    date_modified
    description
    uploaded_by
    category_id
    tag_id

    CATEGORY
    name

    TAG
    name

    IMAGE
    width
    height
    file_id

    VIDEO
    length
    quality
    file_id

Are these tables appropriate for what I'm doing? Can you suggest some alteration for better performance, or a more efficient design to prepare for future changes?

Here's a visual: http://dbpatterns.com/documents/52eddecc9785db06b8f20775/

Upvotes: 0

Views: 48

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

It appears you forget to put the user_profile_id column in the User Profile table, as well as primary key fields for all the others? Also, given that the movie to tag relationship is many to many, you need a bridge table with movie_id and tag_id in the middle. With your current design a movie can have one tag only.

Upvotes: 1

Related Questions