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:

  `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`)

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?

1 Answer 1


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.

  • 1
    There is a difference between 50 and 255 varchars. Consider indexes (especially compound) and their limitations. Nov 6, 2012 at 20:47
  • Thanks @ypercube. I was thinking more of the dereferencing costs, but you're right. Good thing the OP didn't have any indexes! :) Nov 6, 2012 at 21:08
  • @AlainCollins thank you for response, i will do some changes like you said.
    – Murat
    Nov 6, 2012 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.