One-to-many table setup

Question

I have a table where I have to relate groups of primary keys to one another. There is no information about these groups other than that they exist. Specifically, I am storing groups of Editions of Books together, so I can know that a certain set of books are editions of each other.

I currently have a setup where I have an Edition_Group_ISBN column, where one of the ISBN's is arbitrarily chosen to group a set of Editions together.

The typical approach for this problem is to have a separate table like Book_Editions, where I would have an autoincrementing integer primary key like "Edition_Group_ID" linking ISBNs together. I have been told this method is preferable.

However, the problem with implementing that system relates to the loading in of data. How am I to dynamically load in Edition Groups? One solution might be to lock the table and do a transaction on the next ID in the autoincrement. I imagine this would be slower and more cumbersome than my current method, though.

Given the difficulty of inserting data under that system, what is the optimal system to address this problem?

Answer 1

You load in Edition Groups by having an ISBN table foreign key in your Edition Groups table, and then inner-joining your two tables together in your query, using the Primary Key of your ISBN Books table, and the Foreign Key of your Edition Groups table in the join.

ISBN Table
    ISBN_ID                                    // Auto-incrementing Primary Key
    ISBN
    Book_Title
    .etc

EDITIONS Table
    Edition_ID
    FIRST_EDITION_ISBN_ID
    ASSOCIATED_ISBN_ID                         // Foreign Key to ISBN table

Most database systems have a way to return the Primary Key ID of a newly inserted record, so:

NEW_ID = INSERT INTO ISBN (ISBN, BOOK_TITLE) 
         VALUES (12345678, "The Frog Prince"); 
         SELECT SCOPE_IDENTITY();              // Returns new ID from ISBN table.

INSERT INTO EDITIONS (FIRST_EDITION_ISBN_ID, ASSOCIATED_ISBN_ID)
    VALUES (12345678, NEW_ID);