How to manage external ids in my database
I'm storing Facebook ads in my database and I'm struggling to figure out whether to use external ids or internal ids.
Relevant Stack: Using postgres, drizzle-orm, supabase
Background: A facebook ad breaks down into several components: Facebook page, adCreative, ad, locations,
1) On a high level, I could ingest the facebook ad, break it down into it's component parts, add my own internal ids to each and reference each component with the internal ids; OR
2) I could rely mainly on the external ids as indexes (maybe primary key, maybe not), and link them based on these external ids
Internal IDs:
- Pros: My own data is protected from changes from Facebook
- Cons: This makes inserting data in bulk way more sophisticated. Any ad will have the external ids, but I'll need to query for all the fields based on the external id in my database, check if they exist, if not, appending the corresponding internal id to the referencing table for each component. For example, if an ad references a page based on an internal id, I'll need to query for if that external pageId exists, if so, use that page's internalId and add it to the ad so that it is linked properly.
External IDs:
- Pros: Makes inserting into my database so much easier
- Cons: Subject to changes from Facebook (realistically, it will be fine) as my ids are dependent on a third party's ids.
I started using internal Ids because chatgpt said internal is better for more control, but the insertion process is really sophisticated and difficult to reason about. Can someone give me some insight, opinion, or recommendation on what they would do?
0 Replies