Need some tips on create table.

Question

Hi I plan to create a tale to store the race result like this,

Place    RaceNumber       Gender    　　Name             　Result    
12       0112              Male     Mike　Ｌｅｅ            1:32:40 
16       0117              Female   Rose Ｍａｒｒｙ           2:20:40 

Ｉ　ｃｏｎｆｕｓｅｄ　ａｔ　ｔｈｅ　ｉｔｅｍｓ　ｔｙｐｅ　ｄｅｆｉｎｉａｔｉｏｎ．

Ｑ１．I am not sure the result can be set to varchar(32) or other type?

Ｑ２．　and for racenumber, between int(11) and varchar(11), which one is better?

Ｑ３．　Can I　ｕｓｅ　｀UNIQUE KEY｀ ｌｉｋｅ　ｍｙ　ｗａｙ？

Ｑ４．　Ｄｏ　Ｉ　ｎｅｅｄ　ｓｐｌｉｔ　ｎａｍｅ　ｔｏ　ｆｉｒｓｔＮａｍｅ　ａｎｄ　ｌａｓｔＮａｍｅ　ｉｎ　ｍｙ　ＤＢ table?

DROP TABLE IF EXISTS `race_result`;
CREATE TABLE IF NOT EXISTS `race_result` (
  `id` int(11) NOT NULL auto_increment,
  `place` int(11) NOT NULL,
  `racenumber` int(11) NOT NULL,
  `gender` enum('male','female') NOT NULL,
  `name` varchar(16) NOT NULL,
  `result` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `racenumber` (`racenumber`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

Answer 1

Some advice/opinions regarding datatypes.

Result - This is a time, you may want to do some calculations on this time, therefore you should store it as a time type.

RaceNumber - This is a reference, whilst it is a number, you will be performing no calculations on this number. Therefore you should store it as a varchar rather than an int. This will avoid confusion as to its usage and avoid accidently manipulation of it as a number.

Name - Look at the length of string you allow for the Name. Be careful about limiting this value by so much. 16 characters may be too small for some names in the future.

Place - Is this required storage? Can you calculate the place of a runner based on their Result alone? However, you should keep a good primary key for your table.

Answer 2

In answer to your specific questions:

Result: I would just set the result to an integer number of seconds. My opinion is that data should be stored in databases, not formatting. Since the likely things you're going to want to do with this is sort by it and return rows less than or greater than specific values of it, an integer seems better to me.

Race number: Same for race number. If it's always going to be numeric, use an integer and worry about the formatting in the application. If it can be non-numeric then by all means make it varchar but, for a numeric value, I can't see enough gain in making it so.

Unique key: I don't really see the point in having a unique index on race number and ID. ID is, by definition, already unique as a primary key. Perhaps you meant race number and place although even that is risky in the event of two people drawing for a place.

Split names: If you're ever going to treat them as individual values, then yes. Otherwise no. In other words, avoid things like where fullname like 'Mike %'.

Answer 3

For the name, if you ever want to sort on lastname, while you display it as "firstname lastname", then you will need to use separate columns.

In general: think about what you want to do with the data. Leave formatting to the application that is displaying the data. Avoid situations where you need string manipulation or complicated maths to get at the values you need.