Which foreign key to use in a many-to-one table

Question

Suppose I have a table Books with columns Book_ID (primary key), ISBN (unique) and several others, such as Title / author / etc.

I have another Sales table, with the primary key Sale_ID, a foreign key for linking to books and other fields with sales information. Sales exist only for books with ISBN. This is the best database design to be the primary key to Book_ID or ISBN. Note. I will use LEFT JOINing Sales for books, depending on which of the foreign keys is selected.

UPDATE: Some books do not have ISBN because they are not published with them. Nevertheless, I do not foresee (at least in the next few years) users who can sell them, because I do not have a system to make sure that this book without ISBN is not repeated.

Answer 1

In general, I think for a foreign key a surrogate primary key book_id .

There are several potential problems that I can determine if you used a non-primary key ISBNas a foreign key:

• ISBN . , ?
• , ISBN. , , .
• , , ISBN. ISBN?
• book_id , ISBN.

Answer 2

Answer 3

ISBN. , -: " ISBN", ISBN NOT NULL Sales ISBN Books.

@Daniel Vassallo, , , ISBN , . : . , " " .