Adding records to a table when a duplicate may exist.

[AndrewMozley]

When I am running a form which lets the user browse and perhaps update a table I usually work with SET DELETED ON, because the user is not normally interested in deleted records which may well vanish when the table is re-organised.

However if he wishes to add a record for a code which already exists but has been deleted, this may cause an error, if the table does not allow two records which have the same code; I suppose that in this case I could undelete the original record and use that.

It is rather fiddly to have to include the code to change the SET DELETED status when the user wishes to add a record, check for the deleted record, use that if the deleted record exists, otherwise APPEND or INSERT a record.

How do other programmers come with this matter (or do they just design their tables better / differently).

Thanks. Andrew

 

[Olaf Doschke]

Even if you create your index with FOR !DELETED() filter, you'll need to find out if something, which must be unique is already used in a non deleted record. So the problem is not merely about codes still existing in deleted records.

Anyway, you do a LOCATE, a SEEK or best an INDEXSEEK. And if you find a deleted record you RECALL it. That sounds simple enough, doesn't it? At the end of doing what you need to check anyway you may be at the record you want back and nothing is easier at that stage.

What I typically do is work with table buffered data, so I append blank records and so the user would enter the double code value into the new record. And that is what makes it more complex, you'd need to merge the new, buffered and not yet saved record with the the deleted record, and the RECALL is simple but you also need to REVERT the new record. And before that of course copy it. Operations for all this exist, you can SCATTER the new record, remove it with TABLEREVERT of the current record, then RECALL the deleted one and GATHER the user input into it. Notice that TABLEREVERT has parameters for both single and all records, like TABLEUPDATE can save a single or all changes you can revert a single or all buffered changes.

If it's really a code you talk about, and not a (short) name needing to be unique, I'd rather generate it at that stage, this of course is easier, as the generator can check for and avoid double values.

Of course it would be good to ask the user to enter the values of fields, which need to be unique, first, so you can check for it and change from inserting new data to modifying a recalled record.

Bye, Olaf.

 

[Mike Lewis]

Andrew, when you talk about adding a record for a "code" that already exists, do you mean a primary key, such as a Customer Number or an Invoice Number? If so, then my answer is Yes, I would design the table differently.

Put simply, I wouldn't choose a primary key that has meaning to the user. The record might well have a Customer Number, but the primary key would be a simple auto-incrementing integer that has no meaning to the user. So the problem you described would never arise.

If the user deletes Customer Number 100, then later adds Customer Number 100 back into the table, they should be allowed to do just that. The new record would have the same Customer Number (and presumably the same address, phone number, etc), but it would have a different primary key.

I would never attempt to re-instate a record that the user has deleted. The main reason for that is that the application would behave entirely differently depending on whether or not the table had been packed since the deletion. That seems highly undesirable to me.

To summarise: Use a "meaningless" primary key, preferably an auto-incrementing integer, and you won't have the problem that you describe.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike is right, if the code is a primary key. I rather assume some other code, eg a code for a product test for anonymisation, which surely is visible and important.

Another easy solution about the index is to define the index FOR !EMPTY(code) and set the code to empty for deleted records. That doesn't change the situation much, though, as codes might also exist in not deleted records. And then its also a matter of overview, how many records exist and what really needs to be unique. Eg in test studies we use such codes replacing brand and product names the code only has to be unique per study, not overall.

Bye, Olaf.

 

[Dan Freeman]

Put simply, I wouldn't choose a primary key that has meaning to the user. The record might well have a Customer Number, but the primary key would be a simple auto-incrementing integer that has no meaning to the user. So the problem you described would never arise.

If the user deletes Customer Number 100, then later adds Customer Number 100 back into the table, they should be allowed to do just that. The new record would have the same Customer Number (and presumably the same address, phone number, etc), but it would have a different primary key.

I would never attempt to re-instate a record that the user has deleted. The main reason for that is that the application would behave entirely differently depending on whether or not the table had been packed since the deletion. That seems highly undesirable to me.

To summarise: Use a "meaningless" primary key, preferably an auto-incrementing integer, and you won't have the problem that you describe.

This. Always this.

 

[AndrewMozley]

To explain further. I already have a perfectly good meaningless primary key on the table in question. This is not a problem.

I do however have another key (Key2) in the table which I do not wish to have duplicated; there is an index (say Index2) on this key, and the user may make locate the record using this key; he does not wish to see deleted records.

I have followed your suggestion, Olaf, and have included a filter of .NOT. DELETED() on Index2; that does the trick.

It means that no error message is generated when the user re-creates a record with a value of Key2 which he had previously used, but is now deleted.

Thank you.

 

[Olaf Doschke]

OK, so the situation is as I guessed it, a secondary key needing to be unique. Just be aware, if you set the index FOR NOT DELETED() it will not be used by rushmore anmore. So add another normal index on the field for query optimizations.

This could also have been the name of a category in a short table categories (ID, name), where you don't want any double category name, of course.

As a side note directed to Mike and Dan: If you don't recycle the ID, RECALL is not bad. If you simply recycle the meaningful secondary key you don't reestablish old and not any more valid relations by RECALL of a record. It's possible even with an autoinc via RECALL and BLANK DEFAULT AUTOINC. So you can even recycle any deleted record and have it as if freshly inserted.

* historical state
Cd D:\temp
Close Tables All
Close Databases All
Erase Recall.Dbc
Erase productcategories.*

Create Database Recall.Dbc
Create Table productcategories (iID Int Autoinc, cName C(3), Primary Key iID Tag xID)
Index On cName Tag xName Candidate

Insert Into productcategories (cName) Values ("RAM")
Insert Into productcategories (cName) Values ("CPU")
Insert Into productcategories (cName) Values ("HDD")
Insert Into productcategories (cName) Values ("NIC")

* later RAM was deleted (eg a hardware shop decided to stay out of the RAM price fight)
Set Deleted Off
Delete From productcategories Where cName = "RAM"
? "deleted=",Deleted(),iID, cName
Set Deleted On

* even later the shop owner wants to add back RAM category
cUserInputForName = "RAM"
Set Deleted Off
If Seek(cUserInputForName,"productcategories","xName")
   Recall
   Blank Fields iID Default Autoinc
   ? "deleted=",Deleted(),iID, cName && RAM comes back as category iID=5
Endif
Set Deleted On

In this specific case you even might want to get back the RAM category as ID 1, if you still have code for case productcategories.iID = 1 to work for RAM products.

Overall, if you do record recycling via RECALL and BLANK DEFAULT AUTOINC without limiting it to just the ID field, a candidate key will guide you to choose which record to recycle (if at all), to not violate the candidate index, otherwise you could recycle any record, as you even get a new autoinc ID for it and any other default values, as if doing APPEND BLANK, the new record just isn't at the end of the file.

Bye, Olaf.

 

[AndrewMozley]

Thank you Olaf for the warning about Rushmore optimisation; Understood.

Andrew M.

 

[NasibKalsi]

When I am running a form which lets the user browse and perhaps update a table I usually work with SET DELETED ON, because the user is not normally interested in deleted records which may well vanish when the table is re-organised.

You could update the key(s) to something else before deleting and then at the 're-organised' point - will vanish.

nasib

 

[NasibKalsi]

I have a button which will Delete/UnDelete a record. I also make blue background for deleted records. Deleted record are only available until 're-organised'.

nasib

 

[Mike Lewis]

I have a button which will Delete/UnDelete a record.

Back in my very early days with dBASE and Foxbase, I used to the same. But I eventually realised it was a bad idea. It confuses the users, and it sometimes makes them mistrust the application ("I know I deleted that customer yesterday, but he seems to have come back today.")

In any case, it doesn't solve Andrew's original problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[NasibKalsi]

("I know I deleted that customer yesterday, but he seems to have come back today.")

With blue color as a background, I think it is hard to confuse with 'yesterdays customer'. But easy to recall, else may need to look up in the paper trail.

The main function of the programmer in my opinion is to make the user as lazy as possible, ie lessor key stroke.


nasib

 

[Mike Lewis]

With blue color as a background, I think it is hard to confuse with 'yesterdays customer'.

Not at all. There's nothing remotely intuitive about that. What is it about a blue background that signals "deleted" in the mind of the user?

And I repeat my earlier objection: The behaviour of the application will change according to the whether or not a PACK has been done since the record was deleted. Let's accept that the user understands that a blue background means deleted, and that they can undelete whenever they want. Then, unknown to the user, a supervisor does a PACK. Suddenly, the records with the blue background have disappeared, and the "undelete" button is no longer available. That's not what I would describe as a well-behaved application.

In any case, as I said before, it doesn't solve Andrew's original problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

There's nothing remotely intuitive about that. What is it about a blue background that signals "deleted" in the mind of the user?

I don't disagree with you. I think it's a risky design. User's will come to use the blue color as some form of coding or grouping. "Hey look, I've grouped all of today's sales as blues!" Then some admin comes along and packs.

HOWEVER, "there's nothing inherent" isn't necessarily the best argument. There's nothing inherent in an icon of a floppy disc that means save, either, except that someone somewhere decided to use it to mean save and it stuck. We're surrounded daily by conventions that mean something to us (but completely mystify users).