CODEWITHSUNDEEP
c#sqlazureazure-sql-databaseazure-cosmosdb
niko619
niko619

Reputation: 453

Azure SQL DB (EAV) vs Azure Cosmos DB

I'm currently at a cross road with planning a new project.

Most of the data is relational however, external companies will be sending JSON lead data to an endpoint that does not have a strict schema.

Context:

The system is to accept leads from multiple partners and then try to sell the leads to external companies. Therefore, the leads may not always be the same structure. Lead data can be different dependent on the vertical or offer. Admins will be creating new verticals through a UI so expanding a database horizontally doesn't make sense with this and it would get very messy.

There will also be event data being sent in which again may not always be in the same structure.

Requirement

The lead data needs to have a fast read/write and be queryable.

My current ideas are:

If anybody has any insight into this it would be greatly appreciated.

Upvotes: 0

Views: 894

Answers (1)

Pavel Kutakov
Pavel Kutakov

Reputation: 971

Having 3 systems in Azure working with hybrid database scenario I'm voting for the second approach: keep all financial/transactional data in Azure SQL with corresponding unstructured data in Cosmos DB or even Table Storage. Major reasons are:

  • Azure SQL is pretty expensive and limited for database size. Storage account has 100 times bigger limit. So EAV approach may ask too much for storage and DTU.
  • Any kind of aggregation query will be much easier in traditional SQL, ORM frameworks works well with such kind of queries. Any financial queries are more natural and efficient in Azure SQL. External reporting services will also love SQL database.
  • Having data in two databases you are distributing load between them. So you may use cheaper edition of both.

Upvotes: 1

Related Questions