I want to ask about the best database design for integrating with multiple providers for a specific service — for example, Stripe, Paymob, and PayPal for payments. Should I create a separate table for each provider, or just create one table with common data and store provider-specific data in a metadata column, for example ?
#Best database design for services integration
silk fox
humble yew
I would always go with a common "provider" table, just from a basic db design perspective. I don't really know many details but as a quick rule I would say if you plan on doing any type of joins or anything on that provider specific data, I would put that in dedicated tables. if not a generic metadata json column should be fine.
limber wren
You can make a specific table per provider, to store provider data, and create one table with your business rules in relationship with these tables.
For example, I have a products and prices tables that are synced automatically with Stripe. And I have a features table, that contains every business related features. My features and products tables are in a belong to many relationship product_has_features.