Skip to main content
Share Your Experience: Take the 2024 Developer Survey
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 ...
whatsisname's user avatar
  • 27.6k
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 ...
Kilian Foth's user avatar
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 ...
Greg Burghardt's user avatar
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 ...
Philip Kendall's user avatar
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 ...
amon's user avatar
  • 134k
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 ...
Matthieu M.'s user avatar
70 votes
Accepted

Why does the relational model for a database matter?

The performance argument is usually the one which is most intuitive. You especially want to point out how it will be difficult to add good indexes in an incorrectly normalized database (note: there ...
Philipp's user avatar
  • 23.3k
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 ...
Paddy's user avatar
  • 2,623
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 ...
Flater's user avatar
  • 51.7k
36 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 ...
GrandmasterB's user avatar
  • 39.2k
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 ...
John Wu's user avatar
  • 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 ...
JacquesB's user avatar
  • 59.7k
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 ...
Erik Eidt's user avatar
  • 34.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-...
KeithS's user avatar
  • 22.1k
24 votes

Why does the relational model for a database matter?

I'll be having to implement a database with my boss ... Using dedicated Database Management software might be considerably easier (sorry; couldn't resist). lngStoreID | vrStoreName | ...
Phill  W.'s user avatar
  • 12.3k
24 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 ...
Christophe's user avatar
  • 77.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 ...
Tulains Córdova's user avatar
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 ...
dspeyer's user avatar
  • 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" ...
Mike Partridge's user avatar
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 ...
Alex's user avatar
  • 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 ...
Jörg W Mittag's user avatar
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 ...
Kirk Broadhurst's user avatar
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 ...
Machado's user avatar
  • 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 ...
candied_orange's user avatar
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 ...
Christophe's user avatar
  • 77.9k
14 votes

Why does the relational model for a database matter?

Why does a good relational schema improve a database? The answer is: it doesn't always improve a database. You should be aware that the what you were likely taught is called Third Normal Form. Other ...
Jim's user avatar
  • 387
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", ...
Tulains Córdova's user avatar
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 ...
Phill  W.'s user avatar
  • 12.3k

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