Need help with setting up a database structure for a Video Site (Comments, Views, ETC

vbman213

Ok so far I have one database with 3 tables:

Table1: Videos

Table2: Comments

Table3: Views

Videos is structured like this:

[ID] [YouTube ID] [Date] [Rating] [User]

Comments is structured like this

[ID] [VideosID] [Comment] [DATE] [IP]

Views is structured like this

[ID] [VideoID] [IP] [DATE]

So basicly when a user goes to view a video, it checks to see if that IP address has viewed the video, and if it has it doesn't insert a new row, but if it hasn't it inserts a new row

I need some help on what is the best way to do all three of these things in a fast and efficient way...

Thanks

peytonrm

I would just make [videoID, IP] a compound primary key on the views table. Then you would do an INSERT IGNORE INTO to put your data into the views table and the database will handle the logic of only inserting if the record is new for you. The database will be much faster at it than you could be with PHP code.

vbman213

Good idea, thanks 🙂

Anybody else have any suggestions?

And will my method above in post 1 scale easily?

vbman213

How do I create a compound primary key?

I get an error:

"SQL query: Edit

ALTER TABLE views DROP PRIMARY KEY ,
ADD PRIMARY KEY ( VidID , RemoteIP ) ;

MySQL said: Documentation
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key "