General database normalization

Question

Suppose that I have a table of products that I sell to my customers.

Each record has a productID and productName.

I can sell more than 1 product to each customer, but I want to allow customers to only order certain products.

What would those tables look like?

This is what I have so far:

PRODUCTS
+------------+-------------+
| PROD_ID    |  PROD_NAME  |
+------------+-------------+

CUSTOMER
+------------+-------------+
| CUST_ID    |  CUST_NAME  |
+------------+-------------+

ORDERS
+------------+-------------+
| ORDER_ID   |  CUST_ID    |
+------------+-------------+

Answer 1

I wrote and tested this with PostgreSQL, but the principles are the same for any SQL dbms.

Tables for products and customers are straightforward.

create table products (
  prod_id integer primary key,
  prod_name varchar(35) not null
);
insert into products values 
(1, 'Product one'), (2, 'Product two'), (3, 'Product three');

create table customers (
  cust_id integer primary key,
  cust_name varchar(35) not null
);
insert into customers values 
(100, 'Customer 100'), (200, 'Customer 200'), (300, 'Customer 300');

The table "permitted_products" controls which products each customer can order.

create table permitted_products (
  cust_id integer not null references customers (cust_id),
  prod_id integer not null references products (prod_id),
  primary key (cust_id, prod_id)
);
insert into permitted_products values 
-- Cust 100 permitted to buy all three products
(100, 1), (100, 2), (100, 3),
-- Cust 200 permitted to buy only product 2.
(200, 2);

Customer 300 has no permitted products.

create table orders (
  ord_id integer primary key,
  cust_id integer not null references customers (cust_id)
);
insert into orders values
(1, 100), (2, 200), (3, 100);

The table "order_line_items" is where the "magic" happens. The foreign key constraint on {cust_id, prod_id} prevents ordering products without permissions.

create table order_line_items (
  ord_id integer not null,
  line_item integer not null check (line_item > 0),
  cust_id integer not null,
  prod_id integer not null,
  foreign key (ord_id) references orders (ord_id),
  foreign key (cust_id, prod_id) references permitted_products (cust_id, prod_id),
  primary key (ord_id, line_item)
);

insert into order_line_items values
(1, 1, 100, 1), (1, 2, 100, 2), (1, 3, 100, 3);
insert into order_line_items values
(2, 1, 200, 2);
insert into order_line_items values
(3, 1, 100, 3);

You can start an order for customer 300 . . .

insert into orders values (4, 300);

. . . but you can't insert any line items.

insert into order_line_items values (4, 1, 300, 1);

ERROR:  insert or update on table "order_line_items" violates foreign key constraint "order_line_items_cust_id_fkey"
DETAIL:  Key (cust_id, prod_id)=(300, 1) is not present in table "permitted_products".