CODEWITHSUNDEEP
sqldatabasesqlite
lwood
lwood

Reputation: 275

Modeling this in a SQLite relational database

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:

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?

Upvotes: 9

Views: 10536

Answers (1)

Shiva
Shiva

Reputation: 20935

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)
);

Upvotes: 16

Related Questions