Rows not columns

One to many relationships

How do we model something where one record has a relationship to multiple other values.

For instance suppose we have a database of items and want to associate arbitrary tags with each item.

We could do this

create table items (
  item_id integer,
  description text,
  tag1 text,
  tag2 text,
  tag3 text
)

But what if we want more than three tags per item?

How do we know where to insert a new tag?

Better

create table items (item_id integer, description text);

create table tagged (item_id integer, tag text);

Each item can have an arbitrary number of tags attached to it.

Advantages

It's a lot easier to query for items that have a particular tag:

select * from items
join tagged using (item_id)
where tag = 'important'

Vs.

select * from items
where
  tag1 = 'important' or
  tag2 = 'important' or
  tag3 = 'important'

Maybe even better

create table items (item_id integer, description text);

create table tags (tag_id integer, tag text, description text);

create table tagged (item_id integer, tag_id integer);

Remember that anything human meaningful, such as a human-readable tag, will almost certainly end up changing.

By making tags a first class thing with their own table, we can store the human readable part exactly once.

And we can add other information about the tag.

Requires a slight change to our query

select * from items
join tagged using (item_id)
join tags using (tag_id)
where tag = 'important'

A general rule of thumb

You should not be modifying the schema of your database as part of the normal operation of your application.

Set up your schema once before you run your app.

Then, possibly, evolve the schema from time to time.

Such evolutions need to be done with some care.

Collections

Another flavor of relationship, this time, many to many:

create table flashcards (flashcard_id integer, front text, back text);

create table decks (deck_id integer, name text, created_at integer);

create table deck_contents (flashcard_id integer, deck_id integer);

This schema allows flashcards and decks to exist independently of each other.

A deck can exist even if it has no cards in it. And a flashcard can exist even if it's in no decks.

Querying deck_contents

The contents of a given deck is stored as all the rows in deck_contents with the given deck's deck_id.

Conversely, we can use the table to find out what decks contain a given card.

Or how many cards are in more than one deck.

Or zero decks.