• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB or Code...

 
Chris Montgomery
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a database that holds users.
Additional data in the database has a user_id associated to it.

Now we want to delete that user.

My data model won't let me physically delete the row due to FK constraints. this is a good thing. So I have an expired_date column and a status column (overkill) to indicate this user is "deleted".

Along comes a new user. This person�s login id happens to be the same login id as the old deleted user. But because I have a UNIQUE index on the login and the old user still physically exists in the database, it can't be done.

I have a category table (for grouping of information) that has the same issue. If someone deletes a category, it's not physically deleted. So in the future if they wanted to re-add that category, they couldn't due to a UNIQUE index on the category name column.

I could code to check for all of this, but I'd rather let the database manage this stuff - less trips to the db etc. I just can't figure out how to allow duplicate data only if all others are expired...

Any suggestions would be appreciated.

Thanks!
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I just can't figure out how to allow duplicate data only if all others are expired...

You can't allow this. Your data model is quite correctly stopping you from doing this. The only ways round this would be to change the constraints (bad - they must be there for a reason) or change the requirement.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chris, yes this is a common problem.

You could have the unique constraint be on that field and the "delete" field, but that only helps for the first duplicate. If they add it once, delete, then add it again, it is fine, but if you then "delete" the second one, and a third is tried to be added, then it fails again.

What really needs to be implemented is on the client side, a way for the user to search and see the "deleted" records, and "reactivate" them, instead of them trying to add a "duplicate". That is the approach we have to take at my work. We have a field called inactive.

Mark
 
Chris Montgomery
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this is exactly the issue!

I actually thought I had it too - I have an expired_date column.
If I put a unique constraint on the login AND the expired_date, this would allow for multiple deletes, however (and unfortunately), the UNIQUE index doesn't apply to nulls. When you are active this is null. If I add another login that's the same, it takes it (eventhough the new addition has null too for the expired date). You can have duplicate null values on a unique index constraint.

Unless this is a db setting (MySQL)

kinda doesn't make sense to me though...
 
Paul Clapham
Sheriff
Posts: 21565
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Mark Spritzler:
What really needs to be implemented is on the client side, a way for the user to search and see the "deleted" records, and "reactivate" them, instead of them trying to add a "duplicate".
You have to be careful with this design; you need a way to ensure that the "dbrown" user you are trying to add is really the same as the "dbrown" user that was active a couple of years ago and was "deleted". If there's a possibility that the old one was Dennis and the new one is Dorothy then you would need some more complex design.
 
Chris Montgomery
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think for the login id, a simple message saying "that user id is taken" will suffice. I mean, who here got the gmail account prefix they wanted anyway - in other words, I think there is a tolerance for not getting the id you wanted.

Regarding the category issue. I like the "reinstate" approach. The only part I need to be careful of is when expiring a category, I need to move all the associated rows to a different category called "unassigned" or at least ask the question if they want it moved otherwise all the associated rows go with it!

fun topic.
[ April 27, 2006: Message edited by: Chris Montgomery ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic