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.

  • 3
    If a user can have many destinations, then a UsersDestinations (userId, destinatonName) mapping table might be helpful. – Dan Wilson Dec 31 '19 at 17:27
  • Dan's comment above should be posted as an answer, as it seems to be the correct choice in my eyes. – Graham Dec 31 '19 at 17:52
  • Thanks for your response.But the same table will have another column which will have a jsonData needed for both the systems/destinations which are required in different format. – Brooklynn99 Dec 31 '19 at 19:54
  • If i understand correctly, both the destination adres and the json are stored in the table, and the data is different for each destination. If this is the case, I would go for 2 records, and an adapter pattern in your code to select the correct component for sending it. (assuming it is not possible to store the data in a non-json way) – Pienterekaak Jan 13 '20 at 15:12
  • @Pienterekaak, you are correct. The destinations and json data are different. And the data will be stored only in json format. – Brooklynn99 Jan 13 '20 at 19:20

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.