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.
+3
A:
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.
Zachary
2010-08-10 16:52:05
+1
A:
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.
LittleBobbyTables
2010-08-10 16:52:20
A:
book
id name author id
author
id name
DATA
BOOK
1---c fundamentals--1
2---c fundamentals--2
AUTHOR
1----abc
2----def
org.life.java
2010-08-10 16:52:46
still it can be normalized. :)
org.life.java
2010-08-10 16:53:29
A:
Use natural keys where they exist - in this case ISBN numbers
Christopherous 5000
2010-08-10 16:56:30
I would assume that you still need to at least keep a unique column with the ISBN (current standard) to ensure the same book is not entered twice, how else would you enforce this. If you are just interested in building a DB that does need to change and not developing to the user's domain then sure ignore ISBN. This is a trade off between accuracy and convenience.
Christopherous 5000
2010-08-10 18:26:32
It depends on the environment you're working in - Rails advocates having "smart models, dumb database", so logic controlling uniqueness would be in the application code. You would still keep an ISBN column in the db, you just wouldn't use it as the primary key for that row.
Zachary
2010-08-11 15:00:32
Zachary - I see your point on keeping as much logic out of the DB as possible and am of the same mind. The exception that I take from this is operations that are intrinsically set based and integrity constraints (such are enforcing uniqueness on a field). I suppose a good middle ground might be use a surrogate key as the PK and the ISBN as a unique index.
Christopherous 5000
2010-08-11 15:18:33
+3
A:
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;
OMG Ponies
2010-08-10 16:58:40