What is the best practice to insert two similar records in sql server db table which will be sent to two different destinations?

Question

I am working on parsing a file and inserting records into SQL Server DB.The database table structure will be something like this:

A Users table which contains (id, name, address, destinationName,insertDate, updateDate, updatedBy). So I will be inserting the parsed data from file in to this table in a batch which occurs once a day. destinationName is the one which I will be using to send the data to a different destination(system) using Rest Templates. So my issue here is I need to send the same records to two different destinations which my rest template will determine using the destinationName from the Users table. I am thinking of two approaches here to do this.

1. Insert two similar records with different destinationNames so each rest template will see this records and send it to the destination.
2. As the data is same, instead of inserting two records, let rest template handle and determine based on the destinationName.

So please let me know from above both approaches, which is better or is there any other better approach to achieve this.

Any suggestions or better approach would be helpful.

I am using java 8,sql server, spring rest.

Answer 1

First, I would remove any notion of "best practice" here. You have a specific problem and are looking for an appropriate answer. There are two views on how to design the table, but the most appropriate answer depends on your requirements.

The traditional database modeling way

If you have a record that can have multiple things, then the usual response is to model 2 tables. One for the parent record, and the other for the things. In this case it would be our User and UserDestination tables.

This might be appropriate if you were populating data that was going to be used by users and maintained as a table of record. That doesn't sound like what your purpose is. You cannot guarantee first in first out semantics, but you could guarantee that all pending destinations for a user will be processed at once.

Use the table as a queue

In this scenario you have your User table as is, and put in a separate record for each destination. The point is to stage a unit of work so it can be picked up atomically by another process.

In short, you typically would have one record for each line of data you are reading in. You may have a lot of nearly duplicate data, but that's OK because you've queued up the work.

This usually has a first in first out processing approach. The consumer of the queue will either set a boolean field or populate a timestamp for when the record was processed so that you can recover. Or the consumer will simply delete the record when it is complete.

Bottom Line

If you are building a processing queue, then accept that there will be duplicate information. And make sure you clean up the already processed records periodically.