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 ...
- 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/...
- 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 ...
- 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 ...
- 23.3k
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 ...
- 311
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. ...
- 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 ...
- 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 ...
- 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 ...
- 93.4k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 40.7k
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 ...
- 23.3k
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 ...
- 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 ...
- 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 ...
- 236
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 ...
- 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 ...
- 336
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 ...
- 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 ...
- 490
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 ...
- 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 ...
- 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 ...
- 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 ...
- 40.7k
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 ...
- 336
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 ...
- 584
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: ...
- 9,306
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 ...
- 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 ...
- 3,518
Only top scored, non community-wiki answers of a minimum length are eligible