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?

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.

  • thank you for your answer, but when I query script MySQL prints out the following error: #1215 - Cannot add foreign key constraint – Dozent Sep 13 '14 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 '14 at 23:37
  • I have InnoDB engine on my database structure, but it still prints the above error. – Dozent Sep 14 '14 at 9:50
  • May be you have an error in the query syntax or semantics? Show your tables and query. – Kondybas Sep 14 '14 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 '14 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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