4

A (relatively) simple CRUD application on which I work has a lookup table which holds key-value pairs, some of which have child key-value pairs. These are used mainly in drop-down lists on the application front end.

Currently there is a RowStatus field to specify a key-value pair is active/inactive and is not to be included in drop-down lists. This is fine for creating records however it is an issue when updating historical records.

I plan on introducing a ValidFrom and ValidTo field in the lookup table for which 'Create' screens will use, along with the existing RowStatus field, to ensure that any items in the drop-down lists are valid for the current date-time. These would also be nullable in which case the underlying sql would ignore the field.

'Update' screens will continue to use just the RowStatus field, a bonus would be that the Update screens could also only display items which were valid at the time of creation, although business rules would determine this.

In practice, a user control loads key-value pairs from the lookup table and uses different criteria dependent on the page the user control is on. ie.

Create:

select * from lookuplist where RowStatus=1 and ValidFrom < sysdate and ValidTo > sysdate

Update:

select * from lookuplist where RowStatus=1

or

select * from lookuplist where RowStatus=1 and ValidFrom < CreatedOn and ValidTo > CreatedOn

Are there any issues with this approach that I've not considered?

Any ways in which this could be improved?

Code is in c#/ASP.Net/Oracle 12C but not hugely relevant.

atamata
  • 141
  • 4
  • What happens if the valid dates don't form a simple interval? Should it be impossible for an item to be valid but inactive, or inactive but valid (i.e. does RowStatus have a functional dependency on ValidFrom, ValidTo and the current date-time)? You write that it's updating that has an issue, but it reads as though you're changing the creation process, not update. – outis Oct 25 '15 at 18:25
  • Yeah there would be date validation eg. ValidTo is not before ValidFrom, also considered storing this in a separate table so that more than one can be specified per record. This change is to benefit the Update process and the Creation process will not be materially affected but it will need to handle the change. In reality a user control handles the populating of drop-down lists, so the control would load differently depending on whatever page it happens to be on: Create: RowStatus=1 and ValidFrom < sysdate and ValidTo > sysdate. Update: "RowStatus=1"; If RowStatus=0 then it's not displayed – atamata Oct 26 '15 at 09:00

3 Answers3

1

Either ignore the flag when updating OR flag the update as in error if the flag switched from active to inactive.
Which you choose depends on the business logic you're implementing. In some cases the first is appropriate, in others the second.
For example we've a field that determines the inspection intervals for machinery. This field is linked to legal requirements and determines the expiration date of a certificate which is a legal document. If the value in that field becomes inactive, we skip the check on active in the validation of the record because it's of course not a good idea to change the expiration date of the certificate on the fly just because an inspection interval is no longer allowed but a machine was inspected last before that change was made. Of course when a new inspection is entered only the then valid intervals can be selected.

In other cases you may want to flag the record for needing to be updated with a new value. Think of a field that holds the title of a customer. If you decide to disallow a possible value in that list (maybe because of a typo) you would want every next update of the customer record to require the value to be changed.

jwenting
  • 9,783
  • 3
  • 28
  • 45
  • Thanks but I think you've misunderstood the question. These values are not often updated, they are lookup values used for drop-down lists/picklists on the front end. The gist of the question is how to handle entries which are no longer valid eg. Products which may be out of stock – atamata Oct 25 '15 at 17:02
  • @atamata which is exactly what I mean... Some of those you may never want to update, some you may. – jwenting Oct 26 '15 at 14:44
  • That's true, some key-value pairs will never be deactivated, some will. If one is deactivated, there is not always the requirement to update existing records to change that value as in some cases it was valid for the specific point in time at which it was used, if that isn't the case then a batch job would update records. The issue is if fields _other_ than the key-value pair field require updating. Sorry, I think I'm not getting the thrust of what you're saying – atamata Oct 26 '15 at 15:05
1

There is a factor you may not consider - default times should be blank or you will end up with people putting "some time in the future" as defaults and one day, you'll get a support call asking why all your dropdowns have stopped working. (it happened to a colleague at the worst time - they expired at noon, after the sales guys ran through their demo in the morning that was to be shown to customers in the afternoon).

A default of 3000 years in the future might work but a blank for 'not set' would be easier to read - and you can easily see which ones are 'live' as the expiry date would not be set. Similarly you will have to put a created date on all which could cause problems for your historical records unless you get it perfectly right.

gbjbaanb
  • 48,354
  • 6
  • 102
  • 172
  • The values I'm talking about are lookup key-value pairs, no user is editing these. They would be maintained by a sys-admin. – atamata Oct 26 '15 at 09:44
  • ...although I've updated the question to specify that the ValidFrom and ValidTo fields are nullable – atamata Oct 26 '15 at 09:44
  • 1
    Yep - same with ours, but you have to have some default. Think what its going to be. NULLs are probably best. – gbjbaanb Oct 26 '15 at 09:45
0

One consideration could be the row which is invalid, then valid, then invalid again.

To counter this and other complexities I would recommend you force the creation of a new row for a 're-enable' action.

I've done similar systems before and found that the users find the 'historical' menu items confusing, especially in a hierarchical structure.

Another approach is to have 'versions' of the whole tree, which you then switch between when publishing/rolling back.

Once a version is live, you are unable to edit it. This forces some good practice on the users who maintain the structure and reduces the complexity of maintenance.

Ewan
  • 70,664
  • 5
  • 76
  • 161