38 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 ...
user avatar
  • 12.3k
31 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/...
user avatar
  • 8,250
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 ...
user avatar
  • 567
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 ...
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 ...
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. ...
user avatar
  • 8,250
16 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 ...
user avatar
  • 7,373
16 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 ...
user avatar
  • 3,965
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 ...
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 ...
user avatar
  • 12.3k
14 votes

Storing created/retired dates for a large amount of different entities

It seems that you are involved in a project that requires the creation of a temporal database. You may as well find related information by searching for the terms auditable databases and database ...
user avatar
  • 8,250
13 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 ...
user avatar
  • 8,250
13 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 ...
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 ...
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 ...
user avatar
  • 67.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 ...
user avatar
  • 8,250
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 ...
user avatar
10 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 ...
user avatar
  • 12.3k
9 votes
Accepted

What exactly is overlapping candidate key?

You're right on the money with the possible candidate keys, vikkyhacks. Overlapping candidate keys are composite (consist of more than one attribute) candidate keys with at least one attribute in ...
user avatar
9 votes

Explaining 2NF vs 3NF with an example

Your relation is in 3NF, (and not only in 2NF), since as you say the only non prime attribute is Grade, which only appears on the right hand side of your FDs. The relation is not in BCNF, because the ...
user avatar
  • 1,620
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 ...
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 ...
user avatar
  • 8,250
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 ...
user avatar
  • 2,099
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 ...
user avatar
  • 1,074
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 ...
user avatar
8 votes

What normal form does a surrogate key violate?

The accepted answer is incorrect; the answers given by @sqlvogel and @gbn are correct. Surrogate keys are non-domain-driven keys that stand-in for natural keys (those with functional dependencies ...
user avatar
8 votes
Accepted

What and How is the ID antipattern?

The id antipattern is having a unique ID column in each table without requiring (or often discouraging) the application of alternate unique keys. The ID is generated for each new record. Why is this ...
user avatar
8 votes
Accepted

Modeling invoices and orders

Your Question is much too broad to provide any specific table design suggestions. Just this, "Which one has Employee", could mean any of dozens of things as mentioned in the Answer by Neil McGuigan: ...
user avatar
8 votes
Accepted

Is it possible to get rid of so-called 'Spurious Tuples' completely?

This is a great question. Normalization beyond BCNF is extremely hard to understand. Hopefully I can provide an answer that makes sense. I struggled with these concepts for over 20 years before ...
user avatar
  • 3,518
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 ...
user avatar
  • 3,518

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