Reputation: 305
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
Reputation: 7275
I've seen this done two ways.
You create a generic "asset" table that stores all possible things that could be attached to a post. Videos, images, URLs, etc.
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