在SQL中存储标记，类别和int []的最佳方法

I'm just working on putting together the table for blog posts on my site.

I know how to use SQL, and I know a bit about the different types and lengths, but I'm at a loss as to the best lengths and types for my table.

The way I currently have it setup is:

  • id - BIGINT (20)
  • author_id - BIGINT (20)
  • title - text
  • tags - VARCHAR (50)
  • categories - VARCHAR (50)
  • projects - VARCHAR (50)

Tags is a string array of tags for the post, the same with categories. Projects will be an array of ints, that are the ids for the "projects" that are associated with the post. (projects each have their own page about them, and when you go to the page you can get a list of all the posts about it).

My question is, what is the most optimized way I can setup this table?

UPDATE:

Currently I'm interesting in the best ways to store this and optimize disk usage. Right now I have a free x10hosting server I'm going to be using, which has a 512mb limit. If necessary I can pay for a larger server later on, but I want to stick with the free one till I max out my limits.

For a serious space conservation requirement:

  • Compress the title in your client and store in a BLOB.
  • Put tags (etc) in a commalist and use FIND_IN_SET() to check if a tags contains a particular tag. This will be slow, and cannot use any indexes.
  • Use MyISAM (2x-3x smaller disk footprint).
  • Minimize indexes.
  • Use TINYINT/SMALLINT/MEDIUMINT instead of INT or BIGINT.
  • Use UNSIGNED
  • Use latin1 if you don't need utf8; use latin1 on anything (eg zipcode) that does not need utf8.
  • 0.5GB is paltry; check other services.

In PHP you can use a function called "serialize" which I use in SQL storage all the time.

$foo = serialize($bar);

Now just store $foo. $bar can even be complete objects. After the fact you can just grab the value and

$bar = unserialize($foo);

$bar now contains whatever you wanted before. Array, object, et al. This makes the layout of your sql table lazy but since the beginning and end results are just variables in a php script, the table doesn't really matter. Just make the fields text (not varchar(max)) and you are gravy!

php serialize function