Database structure for bonus system in MySQL

Question

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?

Answer 1

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.

• bofanda: thank you for your answer, but when I query script MySQL prints out the following error: #1215 - Cannot add foreign key constraint
• Kondybas: FK supported by InnoBD engine only, if you have used MyISAM you can use complex index but not the FKeys.
• bofanda: I have InnoDB engine on my database structure, but it still prints the above error.
• Kondybas: May be you have an error in the query syntax or semantics? Show your tables and query.
• Kondybas: Unsigned means that int used as ID can be only positive. That makes maximal positive value twice big - [0..4294967295] instead of signed [-2147483648..+2147483647]