Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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.

share|improve this answer
1  
There is a difference between 50 and 255 varchars. Consider indexes (especially compound) and their limitations. –  ypercube Nov 6 '12 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! :) –  Alain Collins Nov 6 '12 at 21:08
    
@AlainCollins thank you for response, i will do some changes like you said. –  Murat Nov 6 '12 at 22:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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