B2B ecommerce customer special price database design

Question

I am currently building an B2B ecommerce site. Currently i am stuck at defining special price for a customer for a specific item.

I have two tables which is:

• Customer (Regular customer details, like name, password etc)
• Items (With default price listed)

A customer can have special prices for some products. Like:

Item A (costs $10 (default price)) Customer A got a discount for Item A (costs $7 for Customer A)

What i had in my mind, was just a middle table with (Customer (FK), Item (FK), Special price)

Will that be a good practice?

Thanks!

Answer 1

You have a many-to-many relationship between customers and items, where a customer may benefit a special price for 0 to N items, and an item may be offered at a special price to 0 to N customers.

As you are guessing already, the proper way to represent this relationship is to create a separate table, called a bridge table, that references both other tables through foreign keys. Customer/item tuples must be unique in the bridge table (a simple way to enforce this is to make the columns tuple the primary key of the table).

Sample ddl:

create table customers (
    customer_id int primary key,
    name varchar(50),
    -- other relevant columns here
);

create table items (
    item_id int primary key,
    price decimal(10, 3),
    -- other columns here
);

create table customer_items (
    customer_id int references customers(customer_id),
    item_id int references items(item_id),
    price decimal(10, 3),
    primary key (customer_id, item_id)
);

Now, say you want to retrieve the price of a given item for a given customer, you would do:

select coalesce(ci.price, i.price) price
from items i
left join customer_items ci 
    on  ci.item_id = i.item_id
    and ci.customer_id = :customer_id
where i.item_id = :item_id

... where :customer_id and :item_id represent the concerned customer and item.