MySQL – Database Structure for Bonus System

database-designMySQL

I have a problem with creating the structure for my database in MySQL as I'm new in creating databases. The problem is in this: there are three tables sellers, clients and bonus. Table sellers stores list of sellers with some additional info and table clients stores list of clients also with additional info. Last table bonus should store bonus accounts of clients regarding sellers, i.e. each client can have multiple bonus account (but not more than one bonus account for a seller):

Sellers                    Clients

id    name                 id    name
----------------           ------------
1     McDonalds            1     John
2     Starbucks            2     Bob
3     IKEA                 3     Anna
4     Apple

For example, Bob have two bonus accounts, one for IKEA and one for McDonalds and Anna have four bonus accounts, i.e. for all sellers and so on. All bonus accounts stored in the table bonus.

How to design table bonus in order to apply it as in above mentioned example?

Best Answer

You have to make complex primary index that refers to the other tables:

CREATE TABLE `bonuses` (
    `c_id` INT(10) UNSIGNED NOT NULL,
    `s_id` INT(10) UNSIGNED NOT NULL,
    `bonus` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`c_id`, `s_id`),
    CONSTRAINT `FK_sellers` FOREIGN KEY (`s_id`) REFERENCES `sellers` (`id`),
    CONSTRAINT `FK_customers` FOREIGN KEY (`c_id`) REFERENCES `customers` (`id`)
)
ENGINE=InnoDB;

Primary keys are always unique thus you can't create the pair seller-customer that already exists. Foreign keys with restrictions prevents creation of pair with nonexistent seller/customer/both.

Related Question