Update many-to-many relationship table

Question

I have three tables: task, taskmembers, members. Each task may have up to five people associated to it. Currently, when someone updates a task I delete all the current members and insert them as a new record. Otherwise, I have to figure out if the person is still a member of the task and, if not, delete them. Also, I have to delete the people no longer associated with the task and update the people still associated to the task.

My concern is that I'm currently using an int as my primary key within the members table. I know MS SQL int values can go up to 2,147,483,647 before overflowing, but it's still a concern of mine. Also, is deleting all the records a good practice? Thank you in advance.

Answer 1

The first thing you'll want to do is make sure there is an alternate key (usually via a unique constraint) defined in the taskmembers table, that consists of the task id and member id. This key will drive all data manipulation, and the surrogate key won't be involved at all.

Once you have that key in place, I suggest using the MERGE statement to perform data manipulation. This will avoid having to delete a subset of data and re-insert it. Instead, by matching the source and target data based on the alternate key, incremental insert/updates can take place. The MERGE statement also allows you to delete from the target where records don't exist in the source (i.e., a member of the task was removed).

The DELETE/INSERT pattern is really wasteful of resources because of the extra amount of logging involved, and the unnecessary locks that are required while the data manipulation occurs. If this is a "hot" table, this kind of pattern can easily lead to deadlocks (if not using snapshot isolation), and as you're finding out, wasted key space usage. With the MERGE pattern, new keys would only be required when members are actually added to tasks.

Answer 2

• Question 1) Is using INT as a primary key for your members table a good idea ? You're good with INT as a primary key for your table. I do not know how you generate your ids, but you could use a UNIQUEIDENTIFIER (GUID) instead to ensure uniqueness when you want to insert a new member (to avoir errors you could have with primary key constraints if you happen to have an INT already existing in the members table). However, using GUID is a bit less performant than INT, but not a big issue. You could have a look at the discussion here.

• Question 2) Is deleting all members when updating task/members association a good idea ? You should define a bit more the 'business' here. From what you said I understand that every time someone update a task, you need to check/update members associated with it, correct ? Where is your business intelligence located, is it in a stored procedure or is it located in your sourceCode ? How do you get your members id ? Being blunt with a delete-all /insert is simpler to implement but depending how/where you do it, it may be more costly than a select that check already existing IDs associated with the tasks.

Answer 3

With an average of 5 relationship rows per task per update if you have 1000 tasks created or updated each hour, you will have enough space in an unsigned integer for 49 years.

You could use a BITINT instead (a 64-bit numeric rather than a 32-bit one), though if you are passing the value into any other code that treats it as a numeric type rather than a string or similar you need to ensure that your target language(s) can handle larger numbers natively.

Or as Melvin suggests, use a UUID type (128 bit keyspace, and generally (if you always generate them properly) portable between systems without keyspace conflict).

As a further option: do you actually need a surrogate key on that table? You could not use task+member as a composite key instead if a person will not be linked to the same task twice?

Edit: As for the delete-before-insert (instead of check-and-insert-or-delete-as-needed): there is no significant problem with this if it makes your code simpler and therefore easier to maintain.

It is less efficient as you are removing some rows that will then immediately get put back as they were, so there will be more I/O activity generally and more actions against rows being logged (relevant if you are using differential backups, log shipping, or so forth) and potentially greater fragmentation within the data files (though this latter effect should be small enough to be safely ignored unless there are many updates per second), and you are using more of your keyspace in that INT PK, but often having simpler code is valuable enough to more than be worth these side effects.

Do make sure that your actions are wrapped in a transaction so that if something goes wrong any changes that were made before the problem are all rolled out together instead of leaving your data in an inconsistent state - though you should do this for the other methods of updating multiple rows in different tables anyway.

Answer 4

Just a comment about the DELETE all - INSERT new pattern vs. the MERGE or check-then-INSERT and/or DELETE patterns. And not regarding performance or simplicity of code.

If your model/requirements change later and you have a FOREIGN KEY relationship from another (new) table to the TaskMember table, you will have a serious issue with the first pattern.

The foreign key (either referencing the (TaskID, MemberID) compound or a surrogate (TaskMemberID) column) constraint, with the default ON DELETE NO ACTION option, means that you will not be able to delete some rows from the TaskMember table (those that are referenced from that new table).

Or with ON DELETE CASCADE option, which means that the referencing rows (in the new table) will be deleted during the operation, even though the TaskMember rows you deleted are inserted again - which may not be what you want.