A little mysql question

[ramoneguru]

OK, I have a quick question, let's say I have a table w/ 8 million rows (this would be in mySQL), and each row is indexed by userid and "type", and let's say each user can have 8 "types".

I've had 2 people suggest 2 different answers. One person uses sql server a lot and the other is a mysql guy.

SQL Server response:
Use 2 tables a USER and USER_TYPE (remove "type" index)
USERS: user_id, type_id. etc...
TYPES: type_id and type_name
Then just join on the type_id to get then type name

MySQL response:
Use 3 tables (maintain type index)
USERS: user_id, etc...
USER_TYPES: user_id, type_id
TYPES: type_id, type_name
Let User_Types create the relation between the 2 tables.

I was wondering why the mysql guy suggest this route as opposed to only 2 tables like the sql server guy? Would both work fine in mysql or is there something I missed?
--Nick

[phranque]

if you want each user to have more than one type you would have multiple rows describing each user.
also, each row in a db table must be unique.
you want 1 row per user, 1 row per type, and 1 row per user/type relationship.
the mysql guy is right...

[phranque]

check out this post on normalization from the databases forum library:
[webmasterworld.com...]

[leadegroot]

Database design is really implementation independent (although once you have it nice you may go back and optimise it for a specific implementation)
You have solutions to 2 different problems in these layouts.
If one of your users can have more than one type against them then you will need the solution suggested by the MySQL guy.
If a user can only have one type then you want the solution suggested by the SQL Server person.
The proposed solutions aren't database-dependant, they have evolved from different understandings of the problem.

(and note - if you don't have any further details to record about the Types than its name then you don't actually need a table for it)