Skip to main content
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
129 votes
Accepted

What to do when you can't determine a boolean value?

This is mainly a requirements analysis problem, and it has nothing to do with the fact the data in stake is "boolean". If you have to initialize tables in a database, or in any other kind of data ...
Doc Brown's user avatar
  • 209k
106 votes

When would you use a long, string ID instead of a simple integer?

Youtube can't use sequentional IDs for two reasons: Its databases are almost certainly distributed, making sequential numbering complicated. It has a privacy option "Unlisted videos": those that don'...
IMil's user avatar
  • 849
99 votes
Accepted

Should entities contain information about their amount?

This is not a question of "good practice", but a question of the requirements of the system. For example: Let's say your system is for a library. If the library has several instances of the ...
Doc Brown's user avatar
  • 209k
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
90 votes

Storing a re-orderable list in a database

Same answer from here https://stackoverflow.com/a/49956113/10608 Solution: make index a string (because strings, in essence, have infinite "arbitrary precision"). Or if you use an int, increment ...
Alexander Bird'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
  • 135k
81 votes

When would you use a long, string ID instead of a simple integer?

On the form of the IDs: They're using Base64 (using the characters a-z, A-Z, 0-9, -, and _). This allows them to have 6 bits of information per character. YouTube uses 11-character video IDs, which ...
rchard2scout's user avatar
70 votes

Is there an industry standard for gender model other than male and female?

First consider why you need to collect this data. Do not collect it if it is unnecessary. For example: You would like to address the individuals properly. Then, simply ask for their preferred form of ...
amon's user avatar
  • 135k
64 votes

What is the best practice about microservice architecture for consuming many stored procedures in the same database?

I get the feeling that some decision maker in your company heard about the buzzword "micro-service" and decided that you need to have them as well, regardless of if they actually solve a ...
Bart van Ingen Schenau's user avatar
60 votes
Accepted

How to store a fixed length array in a database

"adding 24 columns to the draft table seems more efficient to me" show me all the drafts which include player 2. select * from draft where p1=2 or p2=2 or p3=2.... vs select * from draft ...
Ewan's user avatar
  • 77k
55 votes

Modeling first and lastname separately

First name and last name are not useful concepts. Names work differently in different countries. In most Asian countries, the family name is written first, but it is still used for sorting—so you may ...
Jan Hudec's user avatar
  • 18.3k
54 votes

Do multi-tenant DBs have multiple databases or shared tables?

According to Microsoft, the term has 3 potential meanings (one database for all tenants, or one databaser per tenant). To use your example, each customer would be it's own tenant. A database per ...
lmms90's user avatar
  • 669
54 votes

What's a universal way to store a geographical address/location in a database?

Google has developed a library that helps validate postal addresses for every country in the world, which you can use to design a schema to store this data. Look for the most common required fields ...
mitchdav's user avatar
  • 735
54 votes
Accepted

Are surrogate keys a known anti-pattern?

The use of surrogate keys is by itself not an anti pattern. It is a way to create a stable primary key for an entity, that will never change and not depend on any application data. But there are three ...
Christophe's user avatar
  • 78.4k
48 votes

Creating a secondary primary key in a database for some tables

Having an "alternative primary key" is a well know concept in relational database modeling, it is called "alternate key", or sometimes also "secondary key". The set of "potential primary keys" is ...
Doc Brown's user avatar
  • 209k
48 votes

Why would patient management systems not assert limits for certain biometric data?

Is it possibly just the case that many of these systems were never intended for the mass selection of patient groups? This has absolutely nothing to do with it. Even if this software was only used in ...
Flater's user avatar
  • 52.5k
48 votes
Accepted

Better solutions than joining table for Many to Many?

Quote of the day: Premature optimization is the root of all evil- C.A.R. Hoare There is no reason to assume that the join will be slow, if you made sure that the join table is indexed on its both ...
Christophe's user avatar
  • 78.4k
42 votes

What's a universal way to store a geographical address/location in a database?

The universal way to store a geographical address/location in a database is this one: [Address] nvarchar(max) not null This requires the least amount of programming code (and so cuts maintenance ...
Arseni Mourzenko's user avatar
42 votes
Accepted

Is domain driven design an anti-SQL pattern?

These days, you are likely to see reads (queries) handled differently than writes (commands). In a system with a complicated query, the query itself is unlikely to pass through the domain model (...
VoiceOfUnreason's user avatar
39 votes

What to do when you can't determine a boolean value?

This isn't a technical question; it's a business rules question. So, you need to ask "the business." Approach the product owner and/or stakeholder(s) and say something like: We have incomplete ...
svidgen's user avatar
  • 14.7k
37 votes

What's a universal way to store a geographical address/location in a database?

There isn't one. Every country has different address formats. If you are lucky, and they have a format at all! Obviously latitude/longitude will give you a point on the globe, but it's not really ...
Ewan's user avatar
  • 77k
36 votes
Accepted

Do multi-tenant DBs have multiple databases or shared tables?

Which of these paradigms constitutes a traditional "multi-tenant" DB Both concepts are called multi-tenancy, since it is just a logical concept "in which a single instance of software runs on a ...
Doc Brown's user avatar
  • 209k
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
  • 52.5k
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.4k
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.8k
32 votes

Modeling first and lastname separately

The only argument that matters is what are the requirements of your system? Do you need to deal with just one culture? If so, conform to that culture. Otherwise plan for internationalization (as ...
Becuzz's user avatar
  • 4,835
31 votes
Accepted

What are the best practices around retiring obsolete database columns?

If you want to keep the data, then it's not obsolete. Just leave it where it is. It's fine if some class mapped to a table doesn't map every column.
kevin cline's user avatar
  • 33.7k
29 votes

Are surrogate keys a known anti-pattern?

There is nothing wrong in using surrogate keys as primary keys in a consistent way throughout a whole database schema, for each table, with a defined type and a column name suffix like "_SK"....
Doc Brown's user avatar
  • 209k

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