CODEWITHSUNDEEP
web-services
mersadk
mersadk

Reputation: 337

Cross-service references in DB

I am building service oriented system, with multiple services and application. Current I am not sure how to handle DB references between resources from multiple services and databases.

For example, I have a users service, where I can define all users and their roles. Next I have, products service, where I can define my products, their prices and other information.

I also have invoicing service, which is used to create invoices. This service will use information from previous two services. It will link products and users to invoice. Now I am not sure what is the best approach for this?

Do I just save product ID and user ID that it got from other two services, without any referential integrity?

If I do this, then I will have problem when generating reports, because at time of generation I will need to send a lot of requests to products service, to get names and prices of product in invoice. Same for users.

Do I create some table products in my invoicing application, and store name and price of product at the moment of invoice creation?

If I go with this approach, then in case that price or name of product changes, I will have inconsistent data across my applications?

Is there some well-known pattern for this kind of problem, that is what is the best solution.

Upvotes: 1

Views: 63

Answers (3)

ImadT
ImadT

Reputation: 491

Cross-service references in DB is a common challenge for Data integrity between multiple web services, And specially when we are talking about Real time access.

There is two approaches for your case :

1- Databases Replication across your servers

I suppose that you have each application hosted on a separate server, So i can name your servers as Users_server, Products_server and Invoices_Server.

In your example, your Invoice web service need to grab data from Users & Products Servers, in this case you can create a Replication of your Users Database and Products Database on your Invoices_server.

This way you can run your Join queries on the same server and get data from multiple databases.

Query example :

SELECT *
FROM UsersDB.User u
JOIN InvoicesDB.Invoice i ON u.Id = i.ClientId

2- Main Database Replication

1st step you have to replicate all your databases into one main server we can call it Base_server, which basically contain all your databases from all your services.

Then you can build an internal web service for your application to provide needed data in just "One Call", this answer your question about generating reports.

In other words, you will make one call to the mane Base service instead of making 2 or 3 calls to your separate services.

Note: As a Backend developer we use this organization as a best practice while building a large bundle based application, we create a base bundle and then create service_bundle which rely on the base bundle.

If your services are already live, we may need more details about the technology and databases type you using in order to give you a more accurate solution.

Upvotes: 1

ptrk
ptrk

Reputation: 1840

You can (probably should) enforce the integrity making the productId and userId foreign keys in your invoice table. Then your DB makes sure the referenced entities exist. Reports should join tables, not query services for each item. I assume a central DB shared across the system.

Upvotes: 0

John Saunders
John Saunders

Reputation: 161791

Just because you are using SOA doesn't mean you abandon database integrity. Continue to use referential integrity where your database design requires it.

At the service level, you can have each service be responsible for returning identity information for the entities which it owns. This identity information may or may not be the actual primary key from the database, but it will be used by the clients of the service as though it were the actual primary key.

When a client wants to create an invoice, it will call the User service and receive a User entity, which will contain a User Identifier. It will call the Product service and receive a set of products, each with a product identifier. It will then call the Invoice service to create an invoice, passing the user identifier and the product identifiers. This will likely return an invoice identifier.

Upvotes: 0

Related Questions