Mysql – What MySQL storage engine and table schema should I use

database-designinnodbMySQL

I am making a web project and I created necessary and the most important table, but I am not sure that I did it right.

In this site users will post data like text, picture, text's category, etc. I created a table like this:

CREATE TABLE `feed` (
  `f_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u_id` int(10) unsigned NOT NULL,
  `f_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `f_loc` varchar(50) NOT NULL,
  `f_lang` varchar(2) NOT NULL,
  `f_cat` smallint(2) unsigned NOT NULL,
  `f_text` text NOT NULL,
  `f_img_ids` text NOT NULL,
  `f_img_flag` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `f_type` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `f_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`f_id`),
  KEY `u_id` (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Is this table schema good or does it need to be improved?

I also want to record every feed's statistics and add some columns like f_views, f_likes, f_favorites, etc. These columns will be updated frequently as users interact with the site.

What are your suggestions?

Best Answer

If you're updating frequently, you'll want innodb, which supports row-level locking. Your stats can go in a different table, which will be more efficient since it's not as wide as your 'feed' table.

Your data types look pretty good (unsigned, small/tiny).

I'm not a fan of your column names. Why not 'feed_id', etc.? Will you have no other "noun" in the system that could be abbreviated as "f"?

If f_cat is a foreign key, it should be cat_id.

There's basically no difference between a VARCHAR(50) and a VARCHAR(255), so you might think about giving yourself more room if you might need it.

Since f_lang is NOT NULL and only two characters wide, it's a small waste to make it a VARCHAR. I'd prefer to see it as a CHAR(2), which saves the lookup. Or normalize it and make it a foreign key over to a language table.

You've only indexes f_id and u_id. Is that really all you'll be coming in with? If not, think of composite indexes that match your where clause.

Hope that helps.

Related Question