Data Model for multiple checkboxes

I currently have a supplier table and now I want to store the regions served by each supplier.

<form method="post" action="/Tests/Post/">      
    <fieldset>      
        <legend>What is Your Served Region ?</legend>      
        <input type="checkbox" name="served_region" value="Europe">Europe<br>      
        <input type="checkbox" name="served_region" value="Asia">Asia<br>      
        <input type="checkbox" name="served_region" value="Africa">Africa<br>      
        <br>      
        <input type="submit" value="Submit now" />      
    </fieldset>      
</form>

As a first thought, I made my supplier model like this :

CREATE TABLE supliers (
  supplier_id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE NOT NULL,
  servesAfrica BOOLEAN NOT NULL DEFAULT false,
  servesEurope BOOLEAN NOT NULL DEFAULT false,
  servesAsia BOOLEAN NOT NULL DEFAULT false
);

Does this seem to be a good pattern? Especially for queries to filter suppliers by region served etc. Is this solution still reliable if in the future I want to add more regions?

Answer

Is this solution still reliable if in the future I want to add more regions?

No. Adding another region entails:

• adding a new column to the database table
• amending your application code to reference that new column

Changing the data model and application code in a live application is always a pain point. (Precisely how much pain depends on your change process.)

A better design would be to have a table of REGIONS and an intersection table of SUPPLIER_REGIONS. Build your application around that structure and adding a new region is merely a matter of a some insert statements.

CREATE TABLE suppliers (
  supplier_id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE regions (
  region_id SERIAL PRIMARY KEY NOT NULL,
  region_name VARCHAR(255)
);
CREATE TABLE supplier_regions (
  supplier_id SERIAL NOT NULL,
  region_id SERIAL PRIMARY KEY NOT NULL,
  constraint supplier_regions_pk primary key (supplier_id, region_id),
  constraint supplier_regions_supplier_fk foreign key (supplier_id) references suppliers (supplier_id),
  constraint supplier_regions_region_fk foreign key (region_id) references regions (region_id)
);

---

Is it optimal to create an additional table for just 3 values: Europe, Africa and Asia?

Optimality is in the eye of the beholder.

Consider another change: let’s add CUSTOMERS into the mix. We want to know which region our customers belong to. Also we add WAREHOUSES, and want to know in which region they are located. Now we want to know: which is the best Warehouse to store products from Supplier X? Which is the best Warehouse for deliveries to Customer Y? Having a single REGIONS table to join multiple dependent tables makes answering such questions a lot easier than joining flag columns on entity tables.

---

your proposed solution will make it possible to do operations on the region table like adding more fields (translate name etc)

That’s a good insight. One of the purposes on data modelling is to drive out all the core entities in the domain. Things for which we want to store multiple attributes need to be independent tables.