CODEWITHSUNDEEP
sqlmysql
user416384
user416384

Reputation: 27

SQL problem if two diffrent author have same name in books database

Just wanted to know what would happen if in my book database i had two different authors which have the same name. How could i redesign my database to sort this problem out? Do i have to assign primary and secondary keys or something? By the way this question is related to my previous one.

Upvotes: 1

Views: 864

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332591

An AUTHORS table would help your book database - you could store the author info once, but associate it with multiple books:

DROP TABLE IF EXISTS `example`.`authors`;
CREATE TABLE  `example`.`authors` (
  `author_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) NOT NULL,
  `lastname` varchar(45) NOT NULL,
   PRIMARY KEY (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Books can have multiple authors, so you'd need a many-to-many table to relate authors to books:

DROP TABLE IF EXISTS `example`.`book_authors_map`;
CREATE TABLE  `example`.`book_authors_map` (
  `book_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`book_id`,`author_id`),
  KEY `FK_authors` (`author_id`),
  CONSTRAINT `FK_books` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`),
  CONSTRAINT `FK_authors` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 3

Christoph
Christoph

Reputation: 4401

Use natural keys where they exist - in this case ISBN numbers

Upvotes: -2

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

If you have two different authors with the exact same name, each author should have some sort of unique ID to differntiate them, either a GUID or an autonumber.

Upvotes: 1

Zachary Wright
Zachary Wright

Reputation: 24070

You should almost always use your own in-house ID system, even if it's never displayed to your users. In your database each book will have it's own 'id' attribute, which you can just auto-increment by 1 each time.

The reason for doing this, other than the example in your question, is that even if you use a seemingly unique identifier (like an ISBN), this standard could (and has) change at some point in time, leaving you with a lot of work to do to update your database.

Upvotes: 3

Related Questions