• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

DB or Code...

 
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Sheriff
Posts: 26792
82
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Is this the real life? Is this just fantasy? Is this a tiny ad?
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic