1

I have a structure which looks like

Person

ID        int
Name      nvarchar

Phone

ID        int
PersonId  int
Number    nvarchar
Make      nvarchar
Model     nvarchar
MultiSim  bit

One person can have many Phones

At the moment, the logic I have is when ever the user clicks the save button, the application receives a copy of the Person object (which includes an array Phone).

As mentioned the relationship is 1 to many. As such, on save, there is only 1 person and so it's easy to update the person (no need to delete and re-add).

Currently, I delete all of the Phone's associated with the person and re-add them.

The negative that I can see is:

  1. Additional time is taken as it has to delete and then re-add every Phone regardless of whether there was an amendment.
  2. The phone ID, which auto increments, gets bigger and bigger every time this process occurs

I can't find anything to explain if my current approach is wrong or not. Should I care that the ID is getting bigger? Should I be somehow checking the current Phone objects with the ones I want to save?

  • Does your code work even though the phone IDs are changing? Are you having performance problems related to the extra time it takes to do the "delete all then re-add" query approach? If not, then leave it alone and find another problem to solve! (And next time keep in mind that this pattern might not work for other data structures) – Graham Apr 29 at 13:27
  • Updated, but it was just an example @Machado, sadly your comment only pollutes the question – MyDaftQuestions Apr 30 at 10:36
4

I always use the delete all and readd approach.

  1. Usually its quicker to just delete and re add than apply the logic that's required to update.

    This would probably require selects, locking while you run the checks etc.

  2. I use GUIDs for everything and thus avoid your id changing on insert problem.

    Although it has to be said that you could just change the insert to reuse the existing id if you have one.

  3. It's easy. Figure out an optimised approach when you hit a bottleneck.

  4. It is slower when you do a bulk insert, as it forces a loop.

    But this is easily avoided by deleting all the child objects with one statement (where personid in...) and then readding everything

  • This is usually a good approach. Sometimes, depending upon which table/entity I'm looking for, I add a trigger to store the previous data on a historic table, so we can track down the changes on that entity when necessary. – Machado Apr 29 at 13:30
  • Does not mean a unique ID is generated on each save?... If so it is not totally different to a new number as I am (although the id being incremented means no reuse where yours means any unique id) – MyDaftQuestions Apr 29 at 13:41
  • no you generate the id in code and keep it forever – Ewan Apr 29 at 14:40
0

If I had an issue with this approach, it wouldn't be the counter for the phone id, but rather the fact that it takes additional time. Though it may not be a big deal with a couple phone numbers (I can't imagine anybody having more than 10 in any realistic situation), keep in mind that if you're having to save all Person instances, now it is no longer a loop but a nested loop.

If you decide you want to delete and recreate Orders in a one to many relationship with Person, then that is yet again more work being done on save. It isn't very scalable and so you risk that one day your client is having wait times of 10 seconds or more for what would otherwise be a straightforward save to the database.

You ask why this shouldn't be a proper approach, but I reverse that question to you. Why wouldn't you simply keep track of which phone numbers have been added or removed and update accordingly? Is there a particular reason for doing so, or is the underlying reason to keep the code more simplistic? Since this may eventually be a problem in the long term, I would encourage you to consider simply adding and removing as necessary.

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.