storing ENUM

[Jerico]

When building a normalized DB (in MySQL) - what would be the best approach to the following:

If a user can be assigned an ENUM/SET value of male/female, or Christian/Muslim/Athiest - should separate tables be created for each (gender,religion) or should these be columns within the "user" table?

thanks

[NogDog]

For something like gender, I might be inclined to make it an ENUM column in the user table, since the options probably are not likely to change.

For something like religion, I might be more inclined to make a separate 'religion' table with an integer primary key (auto-incremented), the religion name, and any other columns you might need to describe a religion. Then user table would just need an integer field for religion that would point to a primary key in the religion table (i.e. be a foreign key). This way you could add other religions to the table as needed/desired without having to redefine any table definition as you would if you used an ENUM type.

[Jerico]

I think I understand - so the user table would have a foreign key column called "religion_id" or something? I thought it would be the other way around: The Religion table would have a foreign key of "user_id"...

[NogDog]

Assuming each user would only ever be associated with one religion, then I would have it as a foreign key in the user table pointing to an entry in the religion table. (You would then support a many-to-one relationship between users and religions.) If you might need to support a user being associated to more than one religion, then you would need a third user-to-religion table (instead of the foreign key column in user) which would have two columns, user_id and religion_id, allowing you to create the many-to-many relationship.