1

Let's say I have a database with a table called "invoices".

Then I have a web application where users can manually create invoices against said table. I also have a desktop application that can create invoices programatically. I may have other software modules that can all create invoices.

To manage the operations against the database I'm using an ORM framework (Entity Framework specifically). I want to do this for all the modules.

But, if each module has its own DbContext, the problem I face is that when two modules want to create invoices at the same time, the IDs of the new records will be the same, and then one of the CREATE operations will fail.

To prevent this, my approach is to develop a web API that will act as the only entry point to write new data to the database. The only DbContext I will use for writing data will live inside this API, so colliding IDs will not happen.

Is this solution right? Does this have any possible drawbacks?

Improve this question
5
  • Another option would be to use Guid as the primary key in order to handle the same primary keys problem. Commented May 29, 2019 at 10:11
  • All databases are designed to be transactional - they cannot create identical IDs, unless there is a fault in your approach to generating the IDs. And if IDs are required to be strictly sequential at all times, then it follows logically that your transactions must be strictly sequential (one transaction cannot begin, until the previous has either been committed in full or cancelled). If IDs are not required to be sequential, then each transaction is free to seize a range of IDs concurrently, and if the transaction fails the IDs are abandoned and not further used. Commented May 29, 2019 at 11:48
  • @BohdanStupak, Guids can be used, but they are less efficient overall unless it is an utmost requirement that the IDs be generated independently. In particular, they are not suitable for human use, whereas invoices often require an ID that can be handled by humans outside the computer system (thus the essential problem of how to generate one has not been solved). Laws also often mandate that business records such as invoices be numbered strictly sequentially. Commented May 29, 2019 at 11:56
  • @Steve I think that the problem is that the IDs are not generated by the database in this case, but by the DbContext instead, and are kept in memory until the changes are saved: then the transaction is executed. When I try to write a record into the DB and the ID is already taken, I get an Exception. This is the behavior I want to prevent. Commented May 29, 2019 at 15:06
  • @Pesi442, if that be the case, then you will have to stop the DbContext generating the IDs locally, and instead implement a mechanism for allocating them centrally on the database. It may be a case of having to query the database for a range of available IDs, setting these manually onto the client-side objects, and then finally inserting. Commented May 29, 2019 at 20:43

1 Answer 1

Reset to default
3

Options I see:

Improve this answer
1
  • Thank you for the answer. I will look into it. I've been using incremental IDs for so long that I had forgotten about UUIDs, or candidate keys. Tunnel vision, I guess. Commented May 29, 2019 at 14:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.