Mysql – Cities and States (a few data): single table or splitted

foreign keyMySQLprimary-keyselect

I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example:

id  | city           | state
1   | Rio de Janeiro | RJ
2   | Niteroi        | RJ
3   | Cabo Frio      | RJ
4   | Nova Friburgo  | RJ
5   | Campos         | RJ
6   | São Paulo      | SP
7   | Santos         | SP
8   | Santo André    | SP

As I said, less than 20 cities and only 2 or 3 states ( CHAR(2) ). Regarding performance, which is the best: A single table like the above example, or 2 tables with relationship between both?

PS: I'm not expecting any SELECT … WHERE 'state'… I'll only select by city and then display which state it belongs to.

Thanks

Best Answer

CHAR(2) CHARACTER SET latin1 takes only 2 bytes. You are not going to improve significantly on that by using an id TINYINT UNSIGNED (1 byte) to 'normalize' it.

Splitting into 2 tables is something I call "over-normalizing".

JOINs are pretty cheap, but they are not free. This is a disadvantage of the 2-table approach.

While you are asking this question, I suggest you implement it both ways, see how big the tables are, see how fast the queries are, etc. Use it as a learning exercise.

Yes, there will be other cases where 2 tables is better.

Related Question