Cities and States (a few data): single table or splitted? 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
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.