Stack Overflow
Stack Overflow

Questions tagged [database-normalization]

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. It removes insertion, deletion and update anomalies. Normalization involves decomposing a table into less redundant, smaller tables without losing information.

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. It is a procedure in relational database design, that presumably precludes certain undesirable properties from the database. The goal is to remove redundancy in the relations and the anomalies for insertion, deletion and update. Normalization involves decomposing a table into less redundant, smaller tables without losing information. Defining foreign keys in the old tables referencing the primary keys of the new ones.

The Normal forms progress toward obtaining an optimal design. Normalization is step-wise process, where each step transforms the relational schemas into a higher normal form. Each Normal form contains all the previous normal forms and some additional optimization over them.

First Normal Form (1NF)

A relation is considered to be in 1NF if all of its attributes have domains that is indivisible or atomic. This means no repeating groups. First normal form requires a separate line item with its own key.

Second Normal Form (2NF)

A relation is in 2NF when is in 1NF and requiring that non-key attributes be dependent on "the whole key". In other words create separate tables for sets of values that apply to multiple records and relate these tables with a foreign key.

Third Normal Form (3NF)

A relation is in 3NF when it is in 2NF and requiring that non-key attributes be dependent on "nothing but the key". Values in a record that are not part of that record's key do not belong in the table.

Boyce Codd Normal Form (BCNF) Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anamoly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:

R must be in 3rd Normal Form
and, for each functional dependency ( X -> Y ), X should be a super Key.

Reference

Related Tags

1435 questions
414
votes
10 answers

Is storing a delimited list in a database column really that bad?

Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table. Now, I know that the correct solution would be to create a second…
Mad Scientist
  • 18,090
  • 12
  • 83
  • 109
292
votes
4 answers

What are database normal forms and can you give examples?

In relational database design, there is a concept of database normalization or simply normalization, which is a process of organizing columns (attributes) and tables (relations) to reduce data redundancy and improve data integrity. Wikipedia What…
barfoon
  • 27,481
  • 26
  • 92
  • 138
250
votes
11 answers

First-time database design: am I overengineering?

Background I'm a first year CS student and I work part time for my dad's small business. I don't have any experience in real world application development. I have written scripts in Python, some coursework in C, but nothing like this. My dad has a…
bob esponja
  • 4,093
  • 3
  • 31
  • 29
184
votes
26 answers

Is there ever a time where using a database 1:1 relationship makes sense?

I was thinking the other day on normalization, and it occurred to me, I cannot think of a time where there should be a 1:1 relationship in a database. Name:SSN? I'd have them in the same table. PersonID:AddressID? Again, same table. I can come…
Pulsehead
  • 5,050
  • 9
  • 33
  • 37
183
votes
6 answers

Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)

I have read the quote : data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF]. However, I am having trouble understanding 3.5NF or BCNF as it's called. Here is what I understand : BCNF is stricter than 3NF left side of…
Arnab Datta
  • 5,356
  • 10
  • 41
  • 67
144
votes
11 answers

Facebook database design?

I have always wondered how Facebook designed the friend <-> user relation. I figure the user table is something like this: user_email PK user_id PK password I figure the table with user's data (sex, age etc connected via user email I would…
Marin
  • 12,531
  • 17
  • 56
  • 80
139
votes
5 answers

Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?

In MySQL 5.7 a new data type for storing JSON data in MySQL tables has been added. It will obviously be a great change in MySQL. They listed some benefits Document Validation - Only valid JSON documents can be stored in a JSON column, so you get…
Imran
  • 3,031
  • 4
  • 25
  • 41
115
votes
7 answers

What is Normalisation (or Normalization)?

Why do database guys go on about normalisation? What is it? How does it help? Does it apply to anything outside of databases?
AJ.
  • 13,461
  • 19
  • 51
  • 63
98
votes
18 answers

What's the better database design: more tables or more columns?

A former coworker insisted that a database with more tables with fewer columns each is better than one with fewer tables with more columns each. For example rather than a customer table with name, address, city, state, zip, etc. columns, you would…
raven
  • 18,004
  • 16
  • 81
  • 112
98
votes
7 answers

Postgres returns [null] instead of [] for array_agg of join table

I'm selecting some objects and their tags in Postgres. The schema is fairly simple, three tables: objects id taggings id | object_id | tag_id tags id | tag I'm joining the tables like this, using array_agg to aggregate the tags into one…
Andy Ray
  • 30,372
  • 14
  • 101
  • 138
79
votes
9 answers

What is the best way to implement Polymorphic Association in SQL Server?

I have tons of instances where I need to implement some sort of Polymorphic Association in my database. I always waste tons of time thinking through all the options all over again. Here are the 3 I can think of. I'm hoping there is a best practice…
Mark
  • 5,223
  • 11
  • 51
  • 81
74
votes
8 answers

Convert JSON array in MySQL to rows

UPDATE: This is now possible in MySQL 8 via the JSON_TABLE function: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html I'm loving the new JSON functions in MySQL 5.7, but running into a block trying to merge values from JSON into a…
Chris Hynes
  • 9,999
  • 2
  • 44
  • 54
50
votes
9 answers

When to Denormalize a Database Design

I know that normalization has been extensively discussed on Stack Overflow. I've read many of the previous discussions. I've got some additional questions though. I'm working on a legacy system with at least 100 tables. The database is has some…
Mark Evans
  • 1,343
  • 1
  • 14
  • 28
50
votes
14 answers

In terms of databases, is "Normalize for correctness, denormalize for performance" a right mantra?

Normalization leads to many essential and desirable characteristics, including aesthetic pleasure. Besides it is also theoretically "correct". In this context, denormalization is applied as a compromise, a correction to achieve performance. Is there…
Aydya
  • 1,867
  • 3
  • 20
  • 22
43
votes
5 answers

Normalization in MYSQL

What is normalization in MySQL and in which case and how we need to use it?
Fero
  • 12,969
  • 46
  • 116
  • 157
1
2 3
…
95 96