Hi,

I remember getting an answer to a question like this before but I couldn't find it in my historical posts.

Suppose I've a member's table storing information such:

Table members

Member ID
Username
Password hash
Email
Points
Date joined

Does it make sense to move Points out of the member's table into a separate Points table?

Table points

Member ID
Points

"Points" stores the points earned in a game - every user has only one Points record.

Should Points (or similar columns) all go into the table Members?

Thanks for reading and I look forward to your replies

single table, unless you have multiple games and want to track their points separately

Many thanks, r937

Now I've convinced I need to alter my faulty table designs of yesteryear.

What if only a handful of the entire membership are assigned particular value, for instance, to recognise them as special members?

Do we use a separate table in this case - one row for each of these special members - or do we have a column in the Members Table for this value (e.g. 0 for the majority, some Enum value for this small group of members)?

i truly believe it's six of one or half dozen of the other

but for goodness' sake, don't use an ENUM!!

Thank you so much

So either will do - first time I came across that idiom, so had to google

But why is ENUM bad? What is the alternative? CHAR(1)?

ENUM is the spawn of the devil

(you could google that phrase as well)

better would be a foreign key to a table of acceptable values

Ah I see.

So it's a good practice to have those values in a separate table so that if changes are needed in future, there's no need to modify the ENUM values in the main table. Someone else other than the original coder could do it...

I read in one of the results I found that contains your reply. Someone wrote that ENUM('1', '2', '3') is bad. The MySQL site says it's better to avoid numeric enums.

But if the purpose is to define a small numeric range of say 1-5, wouldn't ENUM('1', '2', '3', '4', '5') make sense?

no, it wouldn't

if the purpose of the range of numbers is to define actual numbers (e.g. the number of legs a person has), then TINYINT is more appropriate

but if the numbers represent something else, then why use numbers? assuming you were okay with using ENUM in the first place, why not use an ENUM which enumerates the actual values?

in my opinion, if they aren't numbers, then a foreign key to a table containing the actual values is still better

I think ENUM is a better choice if you are certain that the values is NOT going to change. For example YES/NO (MySQL 'user' table use this), ACTIVE/PENDING/INACTIVE etc..

select on this colum will be faster too as it will avoid join (which you have to if using the foreign-key way).

Please correct if i'm wrong

you are probably under the mistaken impression that all foreign keys have to be integers, and that's not actually true

there's nothing wrong with this --the foreign key would then use the actual status value, and so no join is required

yeah i guess you're right about the join thing.

but if the value's not going to change, ENUM way is still better in term of space consumption (again, if this is wrong, tell me).

and of course, you have less a table in your database.

if space really was a big deal -- and it isn't, but let's go there since you brought it up -- you would replace the firstname column in a personnel database with an integer foreign key to a table of first names, since there are so many Johns and Marys to be found, you would replace street names in the addresses table, since there are really only a limited number of streets in any city and these are shared by many addresses, etc. etc.

most people don't bother with such space saving techniques, and i am always surprised how vehemently they insist that you have to save a couple of bytes on a status column, which is a piddly amount of potential savings by comparison

if the number of tables was a big deal -- and it isn't, but let's go there since you brought it up -- you would denormalize right down to 1NF or maybe even 0NF

but nobody does that, right?


in my opinion, the arguments ~for~ ENUM are extremely hard to find, and the arguments ~against~ are numerous

but, hey, it's your database, knock yourself out, eh

they are mutually exclusive, you can do that AND use enum. you'll save even more space.

same as above


I'm not an enum groupie or anything, hardly even use them. Just trying to understand things better

the amount of space saved is tiny, especially in these days when disk storage is measured in terrabytes or hundreds of gigabytes.

providing you know that there are never ever going to be changes the argument for using an ENUM may seem seductive, however its relatively rare in the real world. its surprising how often seemingly closed groups such as ACTIVE/PENDING/INACTIVE can be changed by the bright sparks users. pushing it to a table with a FK devolves the responsibiulity for changing such things onto your users and doesn't require ANY developer / programmer or DBA resource. similarly if your target application is a mulitlingual application the swchema / design remains the same, the data changes as required.

their may well be a theoretical performance hit, or even marginal real world performance hit in using a join, but I've never seen a significant performance problem on using joins. unless you know better that is.

for gender I'd use M or F with a validation rule / check constraint so it can be one of three values 'M', 'F' or NULL. no need for an ENUM