Data Model for multiple checkboxes-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

I think you need something more normalized like the one below. This way you can also add new regions without changing the schema of the tables. In your example, you would need to add new columns in the case of a new region. In the below you need only add new lines.

CREATE TABLE regions (
  id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE suppliers (
  supplier_id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE NOT NULL,
  region_id INT NOT NULL,
  CONSTRAINT fk_regions
    FOREIGN KEY (region_id)
    REFERENCES regions(id)
);

If you are looking for a more "analytical" layer, i.e. not that normalized, the following could also work, only you would be duplicating the "region_name" heavily.

CREATE TABLE suppliers (
  supplier_id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE NOT NULL,
  region_name VARCHAR(255)
);

CodePudding user response：

Agree with romborimba, although if I got you right, your can have multiple regions assigned to a single supplier. In this case I'd use a binding table

QuickSQL

Binding_tbl
  ID
  Supplier_ID
    ID
    NAME
    EMAIL
  REGION_ID
    ID
    NAME