MySQL - Multiple tables vs. single table

Question

I have been working on saving/loading, and i came across that i have to load/save alot of tables, cause i split my tables into categories eg. characters, character_appearance, character_preferences, character_session, etc (i use the LEFT JOIN clause to load)

Would it be better if i just put all the columns into a single table instead of splitting it into multiple tables?

I generally split them into multiple threads because i thought i would have too much columns inside the table which could cause issues

Any opinions whether i should use a single table / multiple tables. Please ask if you require more information

Thanks in advanced

Answer 1

Multiple tables: It's generally cleaner to have a table per domain class, both from a modelling and maintenance perspective.

Answer 2

It all depends on your design. Does a character have more than one appearance? If so then character_appearance would belong in its own table. If however a character only has one appearance then it would belong in the character table. The number of columns that you have in a table is not an issue as long as each column actually depends directly on your primary key.

I would imagine that a character can have several preferences, in which case you will need to create a spearate preferences table. What about sessions? One per character or multiple? The question is what does a session actually depend on? The character id or something else?

Maybe a single character can have several sessions at different times. If that is the case then a session does not depend on the character's primary key but on the time as well as the character's primary key. In which case it does not belong in the character table but in a table by itself.