Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Please forgive my title; if you have a suggestion for it, feel free to comment.

I have a database:

DROP TABLE IF EXISTS `books`;

CREATE TABLE `books` (
  `isbn` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`isbn`)
) COMMENT 'Books used at this school';

DROP TABLE IF EXISTS `classes`;

CREATE TABLE `classes` (
  `class_id` INT(10) NOT NULL AUTO_INCREMENT,
  `teacher_id` SMALLINT(5) NULL DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) COMMENT 'Classes at the school';

DROP TABLE IF EXISTS `b_c`;

CREATE TABLE `b_c` (
  `isbn` VARCHAR(255) NOT NULL,
  `class_id` INT(10) NOT NULL,
  PRIMARY KEY (`isbn`)
) COMMENT 'Books to classes';

ALTER TABLE `b_c` ADD FOREIGN KEY (isbn) REFERENCES `books` (`isbn`) ON UPDATE CASCADE;
ALTER TABLE `b_c` ADD FOREIGN KEY (class_id) REFERENCES `classes` (`class_id`) ON UPDATE CASCADE;

The issue I'm having is that I would like to normalize data as much as possible (I don't want multiple entries for the same relationship to be entered into the table b_c), but I would like to only store what data is absolutely pertinent.

My first idea to deal with this is to just create a compound primary key for the b_c table consisting of the fields isbn and class_id which would solve the issue of having duplicate relationships in the table, however, I have heard strong opinions on having a unique identifier for every row in a table like this. The justification for having a unique identifier for every row seems to be that it's useful to be able to specify a specific row, though I don't see a situation in which this would become useful. Can someone offer an example?

Another criticism I've heard is that using compound PKs in this way can make JOINs extremely taxing. Can someone comment on the performance of these two different methods?

The question boils down to "Is it worth it to add an id field to the b_c table or is the use of compound PKs enough to properly represent the relationship between the books and classes tables?

If you have any other comments about the design not directly pertaining to the question, I would love to hear them and thank you in advanced for you help.

share|improve this question
1  
Never call your data "meaningless". The data doesn't like that! –  Max Vernon Mar 27 '13 at 19:39
4  
Oh noes! Someone said "performant"... –  Aaron Bertrand Mar 27 '13 at 19:42
1  
In SQL Server I would just add a unique constraint/index to the b_c table on (isbn, classid) which would enforce uniqueness –  JNK Mar 27 '13 at 19:47
2  
@Dylan varchar will pretty much perform worse than an int at EVERYTHING. It has variable storage, the working set for memory is larger, and usually more than 4 bytes, plus you have collation rules to be used when comparing strings etc. That's not to say it's a bad choice for data which is actually character data varying in length. Whether you should replace isbn with a surrogate key depends upon a few things. The main thing would be if you have a lot of foreign keys referencing it and so a lot of joins. –  Cade Roux Mar 27 '13 at 20:06
3  
@Dylan Also whether it is actually static. Static primary keys are best, also if its your clustered index (InnoDB does not allow a separate clustered index to be defined if you have a primary key - they are effectively one and the same). dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html –  Cade Roux Mar 27 '13 at 20:08
show 8 more comments

2 Answers

up vote 4 down vote accepted

Well, it seems like all your data is meaningful, since you don't have a lot of it, and it all seems to have a role as a key or useful attribute.

If you have PK (by definition unique) on isbn in b_c, then this restricts a book to one class. Is that true? At that point you could argue the design that the class_id should simply then be an attribute of the book table and you don't even need the b_c table.

Since you already have a PK on b_c, I don't see the need for a surrogate key. Even if you were to expand to compound primary key on isbn, class_id to be able to allow multiple classes for a book, I don't really see a need for an additional surrogate unique key. In any case, it would only be an alternative unique key, I probably wouldn't make it the primary key and probably wouldn't use it in joins (joining to link tables is not terribly common as a foreign key, since they are usually identified by their parent or child as being part of a collection based on that relationship)

This is what I would do:

DROP TABLE IF EXISTS `books`;

CREATE TABLE `books` (
  `book_id` INT(10) NOT NULL AUTO_INCREMENT,
  `isbn` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`book_id`)
) COMMENT 'Books used at this school';

/* 
  Also consider a unique constraint here on isbn
  Also consider whether to allow NULL isbn when it isn't yet known
*/

DROP TABLE IF EXISTS `classes`;

CREATE TABLE `classes` (
  `class_id` INT(10) NOT NULL AUTO_INCREMENT,
  `teacher_id` SMALLINT(5) NULL DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) COMMENT 'Classes at the school';

DROP TABLE IF EXISTS `b_c`;

CREATE TABLE `b_c` (
  `book_id` INT(10) NOT NULL,
  `class_id` INT(10) NOT NULL,
  PRIMARY KEY (`book_id`, `class_id`) -- note that book is no longer unique by itself
) COMMENT 'Books to classes';

ALTER TABLE `b_c` ADD FOREIGN KEY (book_id) REFERENCES `books` (`book_id`) ON UPDATE CASCADE;
ALTER TABLE `b_c` ADD FOREIGN KEY (class_id) REFERENCES `classes` (`class_id`) ON UPDATE CASCADE;
share|improve this answer
    
Thank you. I too see no reason to have a surrogate key despite being advised to have them. I believe a compound primary key(isbn,class_id) is enough to allow for a many-to-many relationship and normalize the data. I will except your answer if I don't receive contrary views. –  Dylan Mar 27 '13 at 20:06
3  
@Dylan My personal design would use a surrogate primary key for all your tables because MySQL does not allow a separate clustered index definition and I'd hate to have such a wide clustered index. I also would not trust that the isbn wouldn't change, and wouldn't want to have to update any related rows in other tables. I would add a separate unique index on isbn to enforce uniqueness if that is likely and have b_c have a primary key on class_id, book_id. It's also possible that an ISBN might change over time and being able to change without re-writing related rows might be desirable. –  Cade Roux Mar 27 '13 at 20:14
    
It seems that you're suggesting I use two PKs on the same table. One for the surrogate and one for the isbn/class_id fields to enforce uniqueness. Is it possible to have two primary keys? Also, if I have a PK(isbn,class_id), why would I need an index on isbn to enforce uniqueness? –  Dylan Mar 27 '13 at 20:31
1  
@Dylan It is suggested to use the surrogate as primary key for all the reasons mentioned in answers and comments: in InnoDB, the PK is the clustered index, varchar is horrible as primary key, and ISBN in particular is a bad choice as PK. A separate unique index on (isbn/class_id) enforces the relationship you want. All primary keys are unique indexes. Not all Unique Indexes are primary keys. –  Derek Downey Mar 27 '13 at 20:42
    
@DerekDowney Thank you, Derek. That makes things very clear. –  Dylan Mar 27 '13 at 20:50
show 1 more comment

FWIW ISBN is terrible as a primary key. For one, what happens if you get a book you want to put up for pre-order, but the ISBN hasn't been assigned yet? What happens when the ISBN changes (yes, this happens!)? What happens when they change the ISBN format yet again? I would say make that a candidate key but use a surrogate for the PK.

Adding to that the other issues with using strings as keys, and then on top of that a compound key that you require, I just don't understand why you want to fight surrogates when you're getting the suggestion from so many angles. You know that many of the people who are pushing you that way have a lot of experience, right?

share|improve this answer
    
No, I don't know that they have a lot of experience; they are simply words on a back-lit screen. The argument against using the ISBN is solid. Perhaps that's reason enough to use a surrogate key. –  Dylan Mar 27 '13 at 20:22
    
Perhaps I should have said "You know, many..." instead of "You know that many..." –  Aaron Bertrand Mar 27 '13 at 20:34
    
More issues with ISBN as primary key: Errors from sloppy publishers may result in 2 different books having identical ISBNs. Old books do not have ISBN. Private or limited publications may not have either. –  ypercube Mar 27 '13 at 23:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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