Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What about never deleting and using a structure like: taskmembers (task_ID id, member_id id, active_Since DateTime, active_until DateTime) active members would be (active_until IS NULL) or active_until>GetDate()) –  bummi Jan 7 '13 at 15:36
    
I don't really understand your process. What has the fact that your members table has an int primary key got to do with it? I would have thought that you would just be nuking out the entries from taskmembers and readding them not the members table as well? –  Martin Smith Jan 7 '13 at 15:49
    
@MartinSmith Correct, I also delete the lookup entries in taskmembers as well. My biggest concern is that I'm not following a best practice with deleting the records, rather than updating them, if necessary. –  Icono123 Jan 7 '13 at 16:55

4 Answers 4

up vote 2 down vote accepted

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.

share|improve this answer
    
You learn something every day: I thought MERGE was a feature added in SQL2012 not 2008. Be careful with such features if cross-DB compatibility is ever going to be an issue though - they may not be present or implemented the same way elsewhere. If that isn't a concern then this would indeed be the most efficient option. –  David Spillett Jan 7 '13 at 22:18
  • 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.

share|improve this answer
    
I perform most of my business logic within stored procedures. –  Icono123 Jan 7 '13 at 16:49

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.

share|improve this answer
    
Would you consider it a bad practice to delete the records instead of updating them? Asked another way, what would you do in this situation? –  Icono123 Jan 7 '13 at 17:31
    
Not if it makes your code easier to keep bug free. See the new edit with notes on this half of the question. –  David Spillett Jan 7 '13 at 18:26

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.

share|improve this answer
    
Good point. I missed that. (BTW, SQL Server doesn't have ON DELETE RESTRICT.) –  Jon Seigel Jan 7 '13 at 20:32
    
@JonSeigel: Thnx, corrected. –  ypercube Jan 7 '13 at 22:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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