Create Table Correctly?

[ptmuldoon]

I'm creating a table to eventually use for chats on my site. The table is to store the information all ALL of the individual chats on the site.

Each chat will have it own Game ID(GID) and name. And then each new input into the chat will create a new row in the table. I want the TextID (tid) to auto increment each time a new entry is made for that specific Game ID. Is the below table creation correct? Will it auto increment the TID correctly?

Also is there a way to have the table stored by GID and then by TID to make browsing the table easier when needed?

ie,
gid tid
1 1
1 2
1 3
4 1
4 2
4 3


CREATE TABLE `risk_game_chat` (
`gid` mediumint(7) NOT NULL,
`gname` text NOT NULL,
`tid` mediumint(7) NOT NULL auto_increment,
`time` bigint(11) NOT NULL default '0',
`player` tinytext NOT NULL,
`text` text NOT NULL,
`url` text NOT NULL,
UNIQUE KEY `tid` (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

[Fumigator]

Yeah that looks like how you set an auto-increment field. However you won't be able to achieve what you have further down, resetting tid back to 1 and repeating values 1, 2, and 3 over and over. You're defining tid as the primary key, and as auto-increment, which means it cannot contain duplicate values (it must be entirely unique).

It's (mostly) irrelevant what order the data is stored in. You use ORDER BY on your queries to bring back the data in the order you want (in your case ORDER BY gid, tid is what you want).

For better performance, if you mostly retrieve the rows in the table in that order, you may want to add a compound index made up of gid + tid. Or, since it seems you want to repeat values in tid, you may want to skip the auto-increment feature and build your primary key as a compound idex gid + tid, then manually set gid and tid each time a row is inserted.