CODEWITHSUNDEEP
mysqldatabase-design
user2302288
user2302288

Reputation: 305

table creating regarding social networking post

I want to design structure for posting image,video,url, audio,text etc for users.

I have create

CREATE TABLE users (
    userID INT NOT NULL AUTO_INCREMENT,
    firstName VARCHAR(50),
    lastName VARCHAR(50),
    password CHAR(32),
    PRIMARY KEY (userID)
);


 CREATE TABLE post(
           postID INT NOT NULL AUTO_INCREMENT,
           message VARCHAR(200)
        PRIMARY KEY (postID));

MY question is should I design different tables for storing image,video,url etc

like Image table

CREATE TABLE post_image(
               imageID INT NOT NULL AUTO_INCREMENT,
               imgPATH VARCHAR(200)
            PRIMARY KEY (imageID));

same of video,link etc.

Or it can be done only in single table.

Upvotes: 1

Views: 97

Answers (1)

ryan1234
ryan1234

Reputation: 7275

I've seen this done two ways.

  1. You create a generic "asset" table that stores all possible things that could be attached to a post. Videos, images, URLs, etc.

  2. Create a separate table for each asset type. A post_image table, post_comment, post_video, etc.

I prefer (2) only because the asset types will be pretty different from each other. Think of storing information about a video. You have the video duration, whether or not to auto-play, etc. Images will never have those properties.

So even though you could put all those things into one table, I would strongly recommend not to do it.

Upvotes: 1

Related Questions