0

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?

1 Answer 1

2

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.

6
  • thank you for your answer, but when I query script MySQL prints out the following error: #1215 - Cannot add foreign key constraint
    – bofanda
    Sep 13, 2014 at 23:33
  • FK supported by InnoBD engine only, if you have used MyISAM you can use complex index but not the FKeys.
    – Kondybas
    Sep 13, 2014 at 23:37
  • I have InnoDB engine on my database structure, but it still prints the above error.
    – bofanda
    Sep 14, 2014 at 9:50
  • May be you have an error in the query syntax or semantics? Show your tables and query.
    – Kondybas
    Sep 14, 2014 at 9:54
  • 1
    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]
    – Kondybas
    Sep 14, 2014 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.