CODEWITHSUNDEEP
mysqldatabasedatabase-design
Loolooii
Loolooii

Reputation: 9172

How to store video tags into a database (MySQL)?

In a video website every video can have more than 1 tag. I have a video table containing information about each video. How do I store tags? Can anyone show me how to insert and select the information about tags?

Thanks.

Upvotes: 2

Views: 1402

Answers (3)

John Woo
John Woo

Reputation: 263723

One design I can suggest is to create three tables: VideoList, TagList, Video_Tag. VideoList has many-to-many relationship with tagList. Their association is then listed on Video_Tag table.

CREATE TABLE VideoList
(
    ID INT,
    VideoName VARCHAR(150),
    -- other columns here,
    CONSTRAINT tb_pk PRIMARY KEY(ID),
    CONSTRAINT tb_uq UNIQUE(VideoName)
);

I added UNIQUE constraint on column VideoName so that you will have unique values.

CREATE TABLE TagList
(
    ID INT,
    TagName VARCHAR(150),
    -- other columns here,
    CONSTRAINT tb2_pk PRIMARY KEY(ID),
    CONSTRAINT tb2_uq UNIQUE(TagName)
);

I also added UNIQUE constraint on column TagName.

CREATE TABLE  Video_Tag
(
    RecordID INT,
    VideoID INT,
    TagID INT
    CONSTRAINT tb3_pk PRIMARY KEY(RecordID),
    CONSTRAINT tb3_uq UNIQUE(VideoID, TagID),
    CONSTRAINT tb3_fk1 FOREIGN KEY (VideoID) REFERENCES VideoList(ID),
    CONSTRAINT tb3_fk2 FOREIGN KEY (TagD) REFERENCES TagList(ID)
);

Upvotes: 2

David
David

Reputation: 218837

Assuming your video table has one record per video, something like:

Video
----------
ID
Title
etc.

You would then create a separate table for tags, something like:

Tag
----------
ID
Title

And to associate the two, you're create an association table:

VideoTag
----------
VideoID
TagID

The columns in that table would just be foreign keys to the Video and Tag tables. This intermediary table allows you to maintain a many-to-many relationship between the Video and Tag entities, since each video can have many tags and each tag can be on many videos.

Upvotes: 3

Michal Klouda
Michal Klouda

Reputation: 14521

The most common approach is having 3 tables:

  • Videos
  • Tags
  • VideoTags

Where VideoTags serves as association table, having basic set of columns: VideoID, TagID.

Upvotes: 1

Related Questions