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
32 votes
Accepted

Designing a friendships database structure: Should I use a multivalued column?

Managing an individual piece of information Assuming that, in your business domain, a User can have zero-one-or-many Friends; a Friend must first be registered as a User; and you will search for, and/...
MDCCL's user avatar
  • 8,500
22 votes
Accepted

Likes or votes for posts

The problem you face is known as "Normal forms" of databases, especially the first normal form. https://en.wikipedia.org/wiki/First_normal_form. Your databse with the concatenated user IDs (first ...
til_b's user avatar
  • 587
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

What problems are solved by splitting street addresses into individual columns?

I spent 7 years developing software for a publishing company and one of the hardest problems we ever tackled was parsing street addresses in subscription lists. It is useful to split up addresses ...
Jim Garrison'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,500
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

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

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
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,500
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

What problems are solved by splitting street addresses into individual columns?

Problems that can be solved by splitting include Validation Any one part of the name can be compared to a master list. Those which do not match can be rejected. Postcode / zipcode is an obvious ...
Michael Green'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
  • 69.9k
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
10 votes

How to remove data redundancy in product table if multiple suppliers are there for same product

Business rules Based on (a) some deliberations we had via comments and (b) the content of the question, we have defined the following characteristics of your business environment: A Product is ...
MDCCL's user avatar
  • 8,500
10 votes

Likes or votes for posts

The second way is much better because you can easily add or remove a like/dislike. But you should modify your second solution by using one table for like or dislike. The columns of the like/dislike ...
julianstark999's user avatar
9 votes

What problems are solved by splitting street addresses into individual columns?

Like all design questions, there's a hugely qualified "it depends". It depends on your data story - how the data is collected, how it is used, how it gets updated, etc. All my comments should be ...
Karen Lopez's user avatar
9 votes
Accepted

Normalizing a table with a field that generally uniquely identifies a row, but is sometimes null

Provided that Sku and ItemNumber will always imply unique values I consider that you found the answer already by discovering that, conceptually speaking, ItemNumber is an optional property; i.e., when ...
MDCCL's user avatar
  • 8,500
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,129
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,500
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,183
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
Accepted

Is splitting a CSV column into a separate table (enforcing 1NF) unnecessary complication?

The answer to this question is the same as the answers to this question. Both are really about first normal form (1NF). The linked question had to do with justification for a key column made up of ...
Todd Everett's user avatar
  • 3,538
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,500
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,500
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
  • 9,954
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,500

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