Modeling this in a SQLite relational database

Question

Suppose I have a Citizen table (columns: rowid, name) and a Race table (columns: rowid, name). These are citizens of the world and the races are "Caucasian", "African American", etc. The rowids are the default columns provided by SQLite.

There are billions of citizens. There are very few races, say 50.

What is the SQLite way of connecting each citizen to his or her race?

In a standard programming language, I would simply attach to each race a set containing all the corresponding citizen rowids. This would allow me to do various things:

• Given a race, quickly find all its members.
• Given a citizen, quickly loop through the races (since there are few races) and look into the set and find his or her race.
• Adding a citizen is simple. I just put the new rowid into his or her race's set.
• Similarly, deleting a citizen is simple.

How can I do this in SQLite? Could this be tricky given that SQLite only has B-tree indexes? Maybe this kind of data doesn't belong in a SQLite database to begin with?

Answer 1

SQLite has support for FOREIGN KEYS, so you should model your tables as follows.

RaceId from the Race table is a FOREIGN KEY in Citizen table, if 1 Citizen can have only 1 Major Race in your application use case.

CREATE TABLE Race
(
  RowId INTEGER AUTO_INCREMENT PRIMARY KEY, 
  RaceName   TEXT
);

CREATE TABLE Citizen 
(
  RowId BIGINT AUTO_INCREMENT PRIMARY KEY, 
  CitizenName  TEXT,
  RaceId INTEGER,
  FOREIGN KEY(RaceId) REFERENCES Race(RowId)
);    

If you want to support Multiple Races (which is very possible these days), then remove the FOREIGN KEY from the Citizen table and create a new MANY-2-MANY table called CitizenRace, that will have CitizenIds and RaceIds like below.

CREATE TABLE Race
(
  RowId INTEGER AUTO_INCREMENT PRIMARY KEY, 
  RaceName   TEXT
);

CREATE TABLE Citizen 
(
  RowId BIGINT AUTO_INCREMENT PRIMARY KEY, 
  CitizenName  TEXT
);    

CREATE TABLE CitizenRace
(
      CitizenId BIGINT,
      RaceId INTEGER,
      FOREIGN KEY(CitizenId) REFERENCES Citizen(RowId),
      FOREIGN KEY(RaceId) REFERENCES Race(RowId)
);