Skip to main content
39 votes

What is the reason to "normalize your databases"?

Your issue is that you are getting two different pieces of advice conflated into one and the justifications for each piece of advice are not being presented clearly. Recomendation 1: Normalize your ...
Joel Brown's user avatar
  • 12.5k
19 votes
Accepted

Does an excessive table violate normalization rules?

By changing to your proposed solution you lose information from the database. The existing solution says what clubs can exist in a particular school irrespective of anyone actually being in that club ...
Michael Green's user avatar
18 votes
Accepted

Designing a database for a video game business domain with multiple many-to-many relationships

Yes, the identification of many-to-many (M:N for brevity) associations or relationships is a situation that a database practitioner faces quite commonly when laying out a conceptual schema. ...
MDCCL's user avatar
  • 8,530
17 votes
Accepted

Is a table without a primary key normalized?

If a relation does not have any candidate key (and the primary key is just one of the candidate keys), then it can have duplicate rows, so in fact it is not a relation! (since relations are always ...
Renzo's user avatar
  • 4,080
15 votes
Accepted

Blockchain (Bitcoin) as a database?

Is blockchain a potentially viable database solution for modern, high transaction volume applications? The blockchain technology in general has some characteristics that make it difficult to work ...
ypercubeᵀᴹ's user avatar
15 votes

Blockchain (Bitcoin) as a database?

I'm very familiar with cryptocurrency and databases, and I can tell you it's not a great DB engine at all. Using the blockchain as a live database: Think of it as a first normalized form without ...
Ali Razeghi - AWS's user avatar
15 votes
Accepted

To Normalize or Not to Normalize

There is a difference between redundant data (bad) and coincidentally repeated data (not bad). Normalization is a technique which is used to avoid insert, update and delete anomalies. It is not ...
Joel Brown's user avatar
  • 12.5k
14 votes
Accepted

Integrity constraints in a relational database - should we overlook them?

If, as stated in your post, the intention is to create a relational database (RDB for brevity) and, therefore, it is expected that it functions as such, the short answer is: No, you should not ...
MDCCL's user avatar
  • 8,530
14 votes
Accepted

Should stored financial transactions include some data redundancy?

It's quite common to store the results of calculations like this so you record the fact of the financial transaction, not what you think should have happened. Also because storing the results allows ...
David Browne - Microsoft's user avatar
11 votes
Accepted

Is it possible to combine 7 sources without a UNION?

Without the full table definition, it is difficult to provide a perfect answer. However, in an attempt to show the differences in a limited repro, with a very small amount of data, I've created the ...
Hannah Vernon's user avatar
  • 70.2k
11 votes

Does using composite keys violate 2NF?

Your definition of 2NF is not quite correct. 2NF is when a relation is in 1NF and it has no partial dependencies, meaning there are no predicates (columns) that depend on only part of a multi-part ...
Joel Brown's user avatar
  • 12.5k
9 votes

Integrity constraints in a relational database - should we overlook them?

The basic premise of your developers is absolutely wrong. Foreign keys will impact slightly the performance of the DML of your system. They are not used at all in queries thus have no effect on their ...
TommCatt's user avatar
  • 2,139
9 votes
Accepted

Third Normal Form: Composite PRIMARY KEY vs System-Generated Surrogate (IDENTITY)

According to your description of the case under consideration, it seems that (a) appending a column to hold system-assigned surrogates* (surrogates for brevity) via an IDENTITY property to the History ...
MDCCL's user avatar
  • 8,530
9 votes
Accepted

Why is it considered that a set unnormalizes a database absolutely?

The reference for this post is an amazing book called Database System Concepts 6th Edition which I recommend you read. In the book, page 328, it states: A domain is atomic if elements of the ...
Chessbrain's user avatar
  • 1,223
9 votes
Accepted

Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated

Are there official terms for this type of scenario? Yes. This is a Symmetric Relation. And "relation" here has the same meaning as in "Relational Database". An RDBMS is a database management ...
David Browne - Microsoft's user avatar
8 votes

Blockchain (Bitcoin) as a database?

In 2014 we built ascribe.io with the premise of using Bitcoin as a database for Intellectual Property claims. Upon release, we plugged the network because it couldn't handle the throughput, latency ...
Bruce Pon's user avatar
8 votes

First Normal Form: Definitive Definition

Preliminary The definition of normal form (which from the presentation of “Further Normalization of the Data Base Relational Model” in 1971 is known as first normal form) and the definition of the ...
MDCCL's user avatar
  • 8,530
8 votes
Accepted

Enforce an "At least one" Relationship

How to do this in SQL, using only DDL (FOREIGN KEY and UNIQUE constraints): CREATE TABLE author ( author_id INT NOT NULL, author_name VARCHAR(100), CONSTRAINT author_pk PRIMARY KEY (author_id)...
ypercubeᵀᴹ's user avatar
7 votes
Accepted

Is combining independent tables called normalisation?

No, the task you are being asked to fulfill —arranging (a) columns that belong in two or more distinct base tables in (b) a single base table— is not called normalisation (nor normalization for those ...
MDCCL's user avatar
  • 8,530
7 votes
Accepted

How does normalization fix the three types of update anomalies?

1NF is basically just "don't keep too much data in a single column", so I think that 2NF and 3NF are the primary fix for all 3 database anomalies, since both 2NF and 3NF involve breaking out items ...
BradC's user avatar
  • 10k
7 votes
Accepted

Normalizing/combing mutliple tables with similar data

Functional dependencies and normalization In order to carry out a normalization exercise involving second and further normal forms —as per the relational model of data by Dr. E. F Codd—, one first has ...
MDCCL's user avatar
  • 8,530
7 votes

Is saving disk space still considered an advantage of normalization?

Normalization was never about saving disk space. In his 1971 normalization paper E.F.Codd wrote: "It is important to remember that we are not making a case for or against any physical storage ...
nvogel's user avatar
  • 3,777
7 votes

Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated

To long for a comment, so I'll just add this as a complement to David's solution. Even though the question was of theoretical nature, it may be of interest to see how that can be implemented in real ...
Lennart - Slava Ukraini's user avatar
6 votes
Accepted

Storing multiple contact addresses for a person

I wouldn't have the ContactInformation linked 1-to-1 to Person anyway; storing several addresses like that in ContactInformation violates normal form. Instead I'd have ContactInformation with its own ...
Fredric Shope's user avatar
6 votes
Accepted

What normalization rules does this table break

This is a terrible pattern, but it doesn't actually break any normalization rules. The reason is that it's actually a change in what you are modeling. Instead of your database modeling, say, ...
David Browne - Microsoft's user avatar
6 votes

Strong vs weak entity types on SQL

I've seen it mentioned in blogs and tutorials that a weak entity "doesn't have a primary key," but this is misleading. You are correct that every table needs a primary key. This is necessary ...
Bill Karwin's user avatar
  • 15.2k
5 votes
Accepted

Data discretization based on rank position

You can use the NTILE() function: select id, value, ntile(2) over (order by value) as normalized from table_name ; The 2 means that the values should be split into two buckets, after they are ...
ypercubeᵀᴹ's user avatar
5 votes
Accepted

Is this table in 1NF?

In the site that you have cited, note the parentheses at the right of the attributes in the second part of the initial column: There is no context in the picture, but the obvious interpretation of ...
Renzo's user avatar
  • 4,080
5 votes

Name for a "lookup" table with values in separate rows

Removing repeating groups (your comma delimited values) and moving this data into another table where each value is a row in that table is called normalization. In particular, you're moving from 0 ...
Joel Brown's user avatar
  • 12.5k
5 votes
Accepted

Does a computed column break 3NF (third normal form)?

Computed columns are not a construct of the relational model, so it doesn't make sense to try and evaluate them using relational model terms such as 3NF. Computed columns are a proprietary extension ...
SQLRaptor's user avatar
  • 4,098

Only top scored, non community-wiki answers of a minimum length are eligible