I'm creating an API, assuming my database has authors and books tables.
The authors and books come from an external service API, and they have their own id on that external service (I need to track it for future external calls).
Which scenario is best for my database?
Scenario A
authors -id (PK auto increment) -id_author_from_external -name books -id (PK auto increment) -id_book_from_external -id_author (foreign key) -name
Scenario B
authors -id_author_from_external (PK) -name books -id_book_from_external (PK) -id_author_from_external (foreign key) -name
I actually thought scenario A should be fine, but then realized that when I insert a book, I need to look up the author id from my database using id_author_from_external .
Do you have any idea?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
If the external service API is written by you or your team, or you are 100% sure that there won't be any possibility of duplication or data loss (data deletion), I would say option B. p>
However, if any of these scenarios are possible, it's best to keep your ID. This will be your scenario A.
In any case, I will choose scenario A. Does not have any external dependencies.