separate user table with user settings?

[Gozil]

I'm wondering if its a good idea to separate the user information (name, pass, age ...) and the users settings (notifications, themes ...)?

I guess the real question is if its bad practice to have a lot of columns in one table or if its actually faster? One obvious reason not to have them in the same table is if you want to have dynamic settings which require one insert for each setting but I'm not gonna do that right now.

[Atli]

Hey.

As long as each column remains unique to the user, and remains dependent only on the ID of the user, then I don't see a problem with putting them all in one table.

By that I mean: if your fields are dependent on each other, rather than the user, then they should be split up. For example: if you list a home address, creating "country", "city" and "address" fields would be "wrong". They depend on each other, rather than the user. (A city is dependent on the country, and an address is dependent on the city.) - The correct way here would be to create a separate table for each of the parts and link them to the user using a "cascading" 1:N relationship.

The only other factor would be, as you pointed out: performance. If your extra columns are heavy on rarely used variable-length columns (text, varchar, blob) then your performance might suffer if you include them in a table with other frequently used data (especially if they are all fixed-length). - Reading through a table that contains only fixed-length columns is faster than to search through a table containing variable-length columns. You can often speed up your queries by separating them.

For example, in a forum database, you are likely to get a lot more queries asking for forum overviews, rather than forum posts. In those cases, including the topic's text/description in the same table as the other topic data may not be ideal. By separating the text into another table, leaving only fixed-length data, the queries for the forum overviews may be faster, even though the queries for the topic itself may be a bit slower (by having to include a join).

[Gozil]

Thanks for the reply, this clarified a lot for me.