I am making program that searches youtube for given keyword and need to get links of active videos. Term "active" means that video must have 1000 views per day. Youtube API doesn't give such statistics, only total view count. Now I want to run Java program every day to search for given term and to save data into database. I made database in SQLite and want to ask you if my database model is ok. So, I have just one table VIDEO. Primary key is "saying key", based on date. For example: 201108040001, 201108040002, 201108040003, 201108050001, 201108050002, 201108050003. Second attribute is Address, for example: http://www.youtube.com/watch?v=pqha-F3KYHk&feature=youtube_gdata_player, http://www.youtube.com/watch?v=FAIgWGCYGJU&feature=youtube_gdata_player, etc. Third attribute is Date. Forth attrubute is TotalViewCount. Fifth attribute is SearchTerm.
Now I wonder if this database model is good for getting statistical data. For example, I want to get videos that have more than 1000 view per day. To do this I have to subtract TotalViewCounts from 2 dates, and so for every row in a table.
Here is table DDL:
CREATE TABLE VIDEO ( VideoID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Address VARCHAR( 100 ) NOT NULL, Date DATE NOT NULL, TotalViewCount INT NOT NULL, SearchTerm VARCHAR( 100 ) NOT NULL );