When storing a user's religion inside a "User Table", to ensure that should you look lower a column you'd see "Christian" many occasions, "Muslim" many occasions, etc considered failing of the normal form? Which form?

Generate an income view it:

  • 1nf: You will find no repeating posts.

  • 2nf: There's no concatenated primary key, so this doesn't apply.

  • 3nf: There's no reliance upon a nonkey attribute.

Storing user religion by doing this doesn't appear to fail any normal form, nevertheless it appears very inefficient. Comments?

Your design supports all normal forms. It's fine that the attribute includes a string value. How big the information type does not matter for normalization.

The aim of normalization isn't physical storage efficiency -- the aim would be to prevent anomalies. And also to support logical efficiency, i.e. store confirmed fact only one time. Within this situation, the truth that the consumer on the given row is Christian.

The key problem with storing the column for the reason that manner is within space for storage as the amount of rows scales up.

As opposed to a character column, you could utilize an [cde] for those who have a set group of options which will rarely, when, change, but still avoid creating one more table of religion choices to which that one includes a foreign key. However, when the options is going to be fluid, normalization rules would rather the options go to their own table having a foreign type in your user table.

You will find other advantages besides space for storage to keeping them in another table. Modifying them is really a snap. To alter ENUM() to Christian, you may make just one alternation in the religions table, instead of doing the potentially costly (for those who have plenty of rows and religion isn't indexed)

Christianity

... that you can do as well as simpler and cheaper

UPDATE users SET religion='Christianity' WHERE religion='Christian'

Obviously, additionally you enforce data integrity by typing against a religions table. It might be impossible to place an invalid value such as the misspelled UPDATE religions SET name='Christianity' WHERE id=123 .

I am presuming that there are a listing of valid religions if you have got the consumer entering their very own string, then you've to keep it within the user table which is all moot.

Think that religions are saved in their own individual table. If you are following well-established practices, this table may have a principal key that is an integer, and all sorts of references to records within the table in other tables (like the user table) is going to be foreign secrets. The string approach to storing religion does not violate any normal form (because the title of the religion is really a candidate key for that religion table), however it does violate the concept of not using strings as secrets.

(It is really an interesting distinction between the idea and exercise of relational algebra. Theoretically, a string is just like an integer they are both atomic mathematical values. Used, strings have lots of overhead leading developers not for their services as secrets.)

Obviously, you will find different ways (for example ENUM for many RDBMSes) of storing a listing of possible values, each using their own pros and cons.

Your normal forms really are a little awry. Second normal form would be that the relaxation from the row is dependent on "the entire key". Third normal form would be that the relaxation from the row is dependent on "nothing the answer.Inch (So assist me to Codd).

No, your circumstances as referred to doesn't violate the first three normal forms. (It could violate the sixth, based on additional factors).

You will find a couple of cons with this particular approach (in comparison to presenting an overseas key) that you'll want to make certain you're comfortable with. 1 - wastes storage. 2 - reduced to question by religion 3 - someone might put data inside that does not match, eg by hand place "Jedi" or something like that which you may not consider correct 4 - there is no method to have a listing of possible religions (eg if you will find nobody of the certain religion inside your table, eg, Zoroastrian) however, you still want that it is a valid possibility 5 - incorrect capital could potentially cause problems 6 - whitened area surrounding the string could potentially cause problems

The primary professional with this particular way is the information is faster to drag out (no joining on the table) which is also faster for any human to see.