Is this a good way to use mysql?

Question

I am building a web app, and I am thinking about how I should build the database.

The app will be feed by keywords, then it will retrieve info for those keywords and save it into the database with a datestamp. The info will be from different source like, num of results from yahoo, diggs from the last month that contains that keyword, etc.

So I was thinking the a simple way to do it would be to have a table with an id and keyword column where the keywords would be stored, and another table for ALL the data with a id(same as keyword), datestamp, data_name, data_content.

Is this a good way to use mysql or could this in someway make queries slower or sometihng? should I build tables for each type of data I want to use? I am mostly looking for a good performance on the application.

Another reason I would like to use only one table for the data is that I can easly add more data_name(s) without touching the db.

Answer 1

The second table i.e, the table which contains various information about keywords ,id column in this table can be used as a foreign key to the first table id column

Answer 2

Is this a good way to use mysql or could this in someway make queries slower or sometihng? should I build tables for each type of data I want to use? I am mostly looking for a good performance on the application.

A lot of big MySQL players(like for example flickr) use MySQL as a simple KV(key-value) store.

Furthermore if you are concerned with performance you should cache your data in memcached/redis(there is nothing which can beat memory).

Answer 3

I have another recommendation: index your content. If you plan on storing content and be able to search using keywords, then use mysql to store the details of your document, like author, text and some other info and use Lucene to create an index. Lucene is originally for Java, but has ports to many languages, and PHP is no exception.

You can use Zend framework to manage Lucene indexes, with very little effort, browse thru the documentation or look for a tutorial online. The thing of this recommendation is simple: - You'll improve your search time drastically - Your keyword acceptance will be higher, Lucene will give power to search

I hope I can help! Best luck!