// I need to

ModernizeUpgrade without breaking it RescueFix what someone else started Build NewTurn an idea into a product Reach More PeopleGet found, get used, include everyone

// Company

Case StudiesExpertiseAbout + CareersResourcesContact Book a call →

Geopolitical Consequences of SQL Design

Is Moroccan food African or Mediterranean? A cuisine table shows what goes wrong in SQL design when you skip many-to-many relationships.

Art+Logic Jun 13, 20141 min read

From the Art+Logic archive · written in 2014. The tools may have moved on; the thinking still holds.

Map of the Barbary Coast by the French cartographers Pierre and Alexander Lapie

Or: the consequences of not using many-to-many relationships

Is Moroccan food African or Mediterranean?  We designed a simple hierarchical model of cuisines that grouped them by broad geographic area.  The purpose of the model was solely to populate a configurable, organized list for a select menu, where smaller areas were visually grouped.

Keeping things simple, we created a cuisine table with id, name, and parentcolumns:

Column     | Type     | Modifiers
-----------+----------+--------------------------
 id        | integer  | primary key
 name      | string   | not null
 parent_id | integer  | foreign key to cuisine(id)
Indexes:
 "cuisine_pkey" PRIMARY KEY, btree (id)
 "cuisine_name" UNIQUE CONSTRAINT, btree (name)
Foreign-key constraints:
"cuisine_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES cuisine(id)
Software screen capture

Cuisine names could not be duplicated, and each cuisine had zero or one parents.  Which worked great, until we realized that Morocco was in the data list twice.

We had a couple options: create a cuisine join table that allowed for many parents of a single cuisine, or allow duplicates in the name field.  The latter option seemed far more expeditious, so we changed the unique index to:

"cuisine_name_parent_id_key" UNIQUE CONSTRAINT, btree (name, parent_id)
Software screen capture

Which again worked, until we realized that SQL doesn’t apply the unique constraint if parent_id is NULL, and so duplicate names that didn’t have a parent were being allowed.

Digging in deeper, we discovered partial indexes in PostgeSQL, which can apply a unique constraint under certain conditions determined by a WHERE clause.  We now have two unique indexes applied to the name column:

"cuisine_name_parent_id_key" UNIQUE CONSTRAINT, btree (name, parent_id) "cuisine_name_key" UNIQUE, btree (value) WHERE parent_id IS NULL

And thus we avoid an internecine food fight (at the cost of reduced SQL transportability).

keep going

More to explore in Reach More People.

All Reach More People resources →
let's talk

Got a problem like this one?

Tell us what's stuck. A real person replies within one business day.

Talk to our team →

Talk to people who know software, not a sales script.

Your first call is with Aja, our VP of Sales. He'll help make sure you're solving the right problem. Here's what you'll cover together:

  1. What you're trying to accomplish

    The business goal first, then the technology.

  2. Possible approaches

    And the tradeoffs of each, in plain language.

  3. Risks you may not have considered

    Technical and business, before they cost you.

  4. Whether AI changes the math

    What's practical, and what's just hype for your project.

  5. Sensible next steps

    Even if the right next step is not us.

book-a-call.ics

Loading the scheduler…

Open the booking page →

Some problems are easier to explain in writing. Send us a few lines about what you're working on, and we'll reply within one business day. Helpful to include:

  1. What the software does, or should do
  2. What's stuck, or what's worrying you
  3. Any deadline or budget you have in mind
note.md

Loading the form…