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
119
votes
Should I define the relations between tables in the database or just in code?
The database doesn't have to check for data integrity every time application modify data.
This is a deeply misguided point. Databases were created for precisely this purpose. If you need data ...
- 110k
94
votes
Accepted
Why is using MySQL for a dictionary website a bad idea?
I can't tell you why it's a bad idea. I can tell you a bunch of reasons why a relational database is a good idea though.
Remember that not everyone consults a dictionary for a definition. More times ...
- 40.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 ...
- 25k
88
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 ...
- 135k
71
votes
Accepted
Should I define the relations between tables in the database or just in code?
TL;DR: Relationship constraints should go in the database.
Your application ain't big enough.
You are correct, indeed, that enforcing relationships across databases may require enforcing them in the ...
- 15.1k
52
votes
Should I define the relations between tables in the database or just in code?
The constraints should lie within your database, as (with the best will in the world), your application will not be the only thing to ever access this database.
At some point, there may need to be a ...
- 2,633
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 ...
- 54.8k
35
votes
Accepted
Does it ever make sense NOT to condense one to one relationships?
Yes, there are tons of reasons why this may be the better design.
You may have an inheritence/extension relationship, e.g. you might have a User table and then an Administrator table which has more ...
- 26.6k
33
votes
Accepted
What happened to database constraints?
It is important to distinguish between different use cases for databases.
The traditional business database is accessed by multiple independent applications and services and perhaps directly by ...
- 60k
27
votes
Why is using MySQL for a dictionary website a bad idea?
If you go with the key-value store (which offers you a more impoverished programming model) and it turns out you need more structure (in your case, say, adding a third language), or you need to do ...
- 34.3k
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.2k
25
votes
ERD: "many" vs "zero or many"/"one or many" crowfoot notation?
The 2 first relations, One and Many, have an unspecified lower bound. So when using them you leave an ambiguity of whether they are mandatory or optional.
This ambiguity is useful in modeling, in ...
- 79.9k
24
votes
What do relational databases gain by setting a predefined data type for each column?
First: plain text is binary (it's not even the UTF8 or ASCII characters "0" and "1" but actual on/off bits)
That said, some of the reasons are:
Business/design constraints: allowing the number ...
- 39.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,677
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,912
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 ...
- 104k
17
votes
Should I define the relations between tables in the database or just in code?
You should have relations in the database.
As the other answer notes, performance of constraint checking will be far better inside that database than inside your application. Database constraint ...
- 4,219
17
votes
What happened to database constraints?
More and more systems nowadays are running in distributed environments, on the cloud and adopting the technique to "scale out", instead of "scale up". That's even more important if you're dealing with ...
- 4,120
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 ...
- 112k
15
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 ...
- 79.9k
14
votes
Reason to prefer RIGHT JOIN over LEFT JOIN
That depends on what requirement you are trying to fulfill.
It's not the same to say: "gimme all persons and their corresponding orders" that "I want all orders with their corresponding persons", ...
- 39.5k
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 ...
- 13k
13
votes
Should I define the relations between tables in the database or just in code?
We no longer live in one back-end <-> one front-end world.
Most solutions involve a web front-end, a mobile front-end, a batch-front-end, and iPad front-end, etc.
Database engines already have ...
- 39.5k
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 ...
- 211k
11
votes
Disadvantages of using a nullable foreign key instead of creating an intersection table
You wrote in a comment above:
the book "Fundamentals of Database Systems" [...] says [...] that it is recommended to use an intersection table if there are a lot of NULL values in the foreign key ...
- 211k
Only top scored, non community-wiki answers of a minimum length are eligible