Best way to create DB from external source-CodePudding

I am creating an API, let's say my DB has authors and books tables.

Authors and Books came from external service API and they have their own id on that external service (i need to keep track of it for future external calls).

Which scenario would be the best approach for my DB?

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 would be ok but then realized then when i insert books i need to find author id using id_author_from_external from my db.

What's your thoughts?

CodePudding user response：

If the external service API was written by you or your team, or you are 100% sure there won't be any chance of duplicates, or data loss (removal of data), I would say go with B.

However, if there's a chance of any of those, better to maintain your own ID. which would be your scenario A.

I would go with scenario A though, in any case. To not have any external dependency.

CodePudding user response：

Once I had a programming task to import some external data. External in this case was data sent by another employee within the same company. It wasn't delivered via an API, it was delivered by sending me an Excel spreadsheet via FTP. My task was to automate parsing the spreadsheet, extract the data, and load it into MySQL. Fortunately there's a nice Perl module for parsing Excel.

The larger problem was that the person who prepared the spreadsheet would change its format every week. New columns would appear. The identifier in the first column would change format. He even started using formatting like color and bold type as significant information.

I emailed him every week and said, "please settle on one format for the spreadsheet. I have to change my import code every time you change it." But I never got an acknowledgement, and the spreadsheet kept changing every time.

Eventually, my contract was up and I left that job. I specifically warned my manager about the risk of the data import job needing to be re-coded every week, unless he could convince the person who send the spreadsheet to stick to one format. The next week after I left, my former manager emailed me and said the weekly data import job failed, could I come back and fix it (without pay)? I ignored his email.

The point of this anecdote is: you shouldn't depend on external formats. Other people can't help themselves. They change anything they feel like, because it's convenient for them, and they don't think about the consequences.