When storing a user's religion in a "User Table", so that if you look down a column you would see "Christian" many times, "Muslim" many times, etc considered a failure of a normal form? Which form?
The way I see it:
1nf: There are no repeating columns.
2nf: There is no concatenated primary key, so this does not apply.
3nf: There is no dependency on a nonkey attribute.
Storing user religion this way does not seem to fail any normal form, however it seems very inefficient. Comments?
Your design supports all normal forms. It's fine that your attribute has a string value. The size of the data type is irrelevant for normalization.
The goal of normalization is not physical storage efficiency -- the goal is to prevent anomalies. And to support logical efficiency, i.e. store a given fact only once. In this case, the fact that the user on a given row is Christian.
The principle disadvantage to storing the column in that manner is in storage space as the number of rows scales up.
Rather than a character column, you could use an ENUM() if you have a fixed set of choices that will rarely, if ever, change, and still avoid creating an additional table of religion options to which this one has a foreign key. However, if the choices will be fluid, normalization rules would prefer that the choices be placed into their own table with a foreign key in your user table.
There are other advantages besides storage space to keeping them in another table. Modifying them is a snap. To change Christian to Christianity, you can make a single change in the religions table, rather than doing the potentially expensive (if you have lots of rows and religion is not indexed)
UPDATE users SET religion='Christianity' WHERE religion='Christian'
... you can do the much simpler and cheaper
UPDATE religions SET name='Christianity' WHERE id=123
Of course, you also enforce data integrity by keying against a religions table. It becomes impossible to insert an invalid value like the misspelled Christain.
I'm assuming that there's a list of valid religions; if you've just got the user entering their own string, then you have to store it in the user table and this is all moot.
Assume that religions are stored in their own table. If you're following well-established practices, this table will have a primary key which is an integer, and all references to entries in the table in other tables (such as the user table) will be foreign keys. The string method of storing religion doesn't violate any normal form (since the name of a religion is a candidate key for the religion table), but it does violate the practice of not using strings as keys.
(This is an interesting difference between the theory and practice of relational algebra. In theory, a string is no different from an integer; they're both atomic mathematical values. In practice, strings have a lot of overhead that leads programmers not to use them as keys.)
Of course, there are other ways (such as ENUM for some RDBMSes) of storing a list of possible values, each with their own advantages and disadvantages.
Your normal forms are a little awry. Second normal form is that the rest of the row depends on "the whole key". Third normal form is that the rest of the row depends on "nothing but the key." (So help me Codd).
No, your situation as described does not violate any of the first three normal forms. (It might violate the sixth, depending on other factors).
There are a few cons with this approach (compared to using a foreign key) that you will need to make sure you are ok with. 1 - wastes storage. 2 - slower to query by religion 3 - someone might put data in there that doesn't match, eg manually insert "Jedi" or something that you might not consider correct 4 - there's no way to have a list of possible religions (eg if there are no one of a certain religion in your table, eg, Zoroastrian) but you still want it to be a valid possibility 5 - incorrect capitalization might cause problems 6 - white space around the string might cause problems
The main pro with this technique is the data is quicker to pull out (no joining on a table) and it is also quicker for a human to read.
