254
votes
Accepted
Is it ever okay to use lists in a relational database?
The key word and key concept you need to investigate is database normalization.
What you would do, is rather than adding info about the assignments to the person or tasks tables, is you add a new ...
- 27.7k
94
votes
Is denormalising a database for speed an anti-pattern?
Is denormalisation for performance reasons an anti-pattern? Not of itself - if something is required, then you have to find a way to do it, and it may well be better to denormalise your data than ...
- 26.1k
89
votes
Accepted
Is denormalising a database for speed an anti-pattern?
Performance requirements are legitimate requirements, and it's great that you have found a potential way to meet these requirements. Denormalization is a tool, not an anti-pattern. But this likely ...
- 136k
37
votes
Is it ever okay to use lists in a relational database?
You're asking two questions here.
First, you ask if its ok to store lists serialized in a column. Yes, its fine. If your project calls for it. An example might be product ingredients for a catalog ...
- 39.4k
36
votes
Accepted
Why should I use foreign keys in database?
It maintains referential integrity (yes but can be maintained without it too)
You are technically correct that if you're able to maintain referential integrity yourself, you don't need the constraint ...
- 59.5k
35
votes
How do we distinguish between "not filled in" and "unknown" in our data store?
I always prefer an explicit domain model - have an enum or similar KNOWN_DATE, UNKNOWN_DATE, NOT_FILLED and then a separate date column. The primary reason you might move away from this is for ...
- 26.1k
27
votes
Is denormalising a database for speed an anti-pattern?
Premature optimization is the root of all evil - most of it, anyway -
in computer science. ~Donald Knuth
Denormalizing aggregate data to avoid the aggregate function is not an anti-pattern. The anti-...
- 22.3k
27
votes
Does it ever make sense to have a one-to-one obligatory relationship in a relational database?
If there always is a strict 1:1 relationship between teacher and student then it is not a violation of 3rd normal form to have both in the same table.
One-to-one relationships in a single table is not ...
- 62.3k
24
votes
Does it ever make sense to have a one-to-one obligatory relationship in a relational database?
Does it ever make sense to have a one-to-one obligatory relationship in a relational database?
If both sides are exactly 1 (not zero/optional), then in my opinion no. A hard 1-1 relationship suggests ...
- 59.5k
23
votes
Is denormalising a database for speed an anti-pattern?
Most relational databases have something called Materialized Views. It basically has the database precompute a query and keep that around for quick response. The database is then responsible for ...
- 369
22
votes
Is it ever okay to use lists in a relational database?
What you're describing is known as a "many to many" relationship, in your case between Person and Task. It's typically implemented using a third table, sometimes called a "link" or "cross-reference" ...
- 6,727
19
votes
Accepted
Is storing a list of strings in single database field a bad idea? Why?
The datamodel isn't normalised; to be so it would need a separate table as you say. In that regard, it's not particularly good datamodelling practice.
Whether it was done for a good reason or not is ...
- 3,952
18
votes
Object Oriented Design and Relational Databases: Where to place behavior that pertains to more than one tables?
Very often, when you have a behavior where you cannot decide which of two objects should have it, that is because either
the two objects should actually be one object or
there is a third object ...
- 105k
16
votes
Is there any benefit to a separate table that is one-to-one with the primary data table?
There are several reasons why one want to keep two tables:
separation of concerns (e.g. if the list of items is related to inventory an status management is related to sales catalogue)
authorisations ...
- 82.2k
15
votes
Accepted
Database : Does it make sense to choose the zipcode as a primary key for an address?
The example is making a fundamental mistake: it's using data as a primary key. It should create and use unique IDs.
The comments debate how correct it is to assume that a zipcode maps to a particular ...
- 120k
14
votes
Is it ever okay to use lists in a relational database?
... it's never (or almost never) okay to store a list of IDs or the like in a field
The only time you might store more than one data item in a single field is when that field is only ever used as a ...
- 13.1k
13
votes
Accepted
Relational integrity without relations
The reasons for such design decisions are often not technical ones, but organizational ones. I have seen this happen in the real world, in situations of the following kind:
At the time when the ...
- 220k
12
votes
Use UUID on top of auto increment ID
The biggest myth when designing applications is that you are only allowed to have one key.
Have multiple keys, go ahead and do it. Your application is allowed to have a different primary key than ...
- 5,311
12
votes
Prevent analytics from crashing the production database
I will start with the simplest solution, per amon's comment - create a read replica - if you are hosting your database in a cloud provider, this is typically just a few clicks or a few changes to your ...
- 4,629
11
votes
Is storing a list of strings in single database field a bad idea? Why?
Is storing a list of strings in single database field a bad idea?
It would generally be considered a violation of normalization.
However, sometimes this is used a solution to a problem, e.g. in ...
- 34.8k
11
votes
How to solve form submission consistency in a web application?
This is a pretty common problem, and one of the reasons that web applications tend to commit right away after any change. Sometimes the right answer is do nothing. You have to decide up front how ...
- 46.5k
11
votes
How do we distinguish between "not filled in" and "unknown" in our data store?
There's no simple "do it that way and you'll be fine" solution.
This is the approach that I would probably take:
Determine the actual use cases (entering and displaying data, storing into ...
- 18.6k
10
votes
Accepted
Should I comply with SRP when designing relational tables?
The single responsibility principle is a general design heuristic, but relational database theory has something better: Normal Forms. Entity-Relationship Modelling can be used to develop these data ...
- 136k
10
votes
Application/Database design where two tables have both 1-to-many and 1-to-1 relationships
Think longer term - eventually... there will be a MegaCar 2.0 Or there will be another new car company with only the one model. Or one model companies all go out of business.
Keep it the way you ...
- 317
10
votes
Does it ever make sense to have a one-to-one obligatory relationship in a relational database?
It is true that with an obligatory one-to-one relationship, there is no structural reason to require separate tables. You can simply add all columns in one table, and that is sufficient to store the ...
- 12.6k
9
votes
Database design for fields that should be NOT NULL eventually, but may not yet have a value
Two Tables and a View
Have two tables with mirrored schemas, differing only in the nullability of the columns. Once all the data is present, you move it from the pending table to the complete table. ...
- 12.4k
9
votes
Does it make sense to place constraints in the code instead of in the database?
Of course it's more flexible to allow everything. Arguing against this is a losing game. The point of constraints, types, information hiding etc. etc. is that we've slowly and painfully realized that ...
- 111k
9
votes
Which database technology to choose for storing (post + replies) threads
Most proponents of NoSQL overstate the scaling/performance problem.
This is admittedly an oversimplified point of view, but one of the big reasons that NoSQL is popular is because Google uses it. If ...
- 201k
9
votes
Accepted
Is there any benefit in having more than one table for storing the same type of objects?
Dividing one logical table into multiple tables within the same database has zero benefits. This will complicate queries and may in fact hurt performance, because finding elements is more difficult. ...
- 136k
9
votes
Applying Edgar Codd’s 1NF normalization procedure
Both need some further normalization work if you want a fully normalized model.
However, it is either missing the notion of M:N relationship (first-class relationships), or it is insufficiently ...
- 34.8k
Only top scored, non community-wiki answers of a minimum length are eligible