Redundant Data Issues

[Graham Wickens]

Subject: Redundant Data

Table 1: LOGFILE

Identity C:12
Operator_Name C:90
Op_Unique C:8

Table 2: OPERATOR

Operator_Name C:90
Unique C:8
B_Unique C:8


Table 1 contains aircraft details including fields "Operator_Name" & "Op_Unique".

For example Operator_Name "British A/W" has around 200+ IDENTITY entries

Table 2 contains OPERATOR details including fields "Operator_Name" and "Unique".

I have a look-up defined on field LOGFILE->Operator_name which allows choice of Operator_Names, as these names can be duplicated but in different countries I also use the OPERATOR->Unique field to define which one it is, (this is included in the look-up).

This works fine for single updates, but can be cumbersome on multiple updates (ie British A/W change their name to British Underground A/W) I have to update each occurrence of "British A/W" in LOGFILE->Operator_Name as well as OPERATOR->Operator_Name. How DO I setup Table LOGFILE so that I can:

1) still do the look-up on LOGFILE.
2) When I change the entry in OPERATOR->Operator_name (ie one change) it automatically updates all entries in LOGFILE?

I've tried just using Op_unique in LOGFILE but was unable to use the look-up function.

How is it Done Boys?

[JerryBrightbill]

RE: Redundant Data Issues

Graham,

One of the early lessons I learned was to try to put changable data in only one table and link it to others with some type of id identifier if possible. Since you have the operator name in two tables, this will create a problem when the name changes.

There are a couple options. The first option would be to create a new table for operator names with 2 or more fields. One field would be the name displayed, which can change, and another would be a record identifier, which is fixed and would not change. This could be an autoincrement field since the actual value is not important other than it be unique. In each table where you have the operator name, add a field for the identifier. Now you can get the operator name by creating a set and linking the operator ntable by id number, or by creating a calculated field for the name using lookup() functions. I prefer the set, but if you have lookups to one of the tables, version 4 won't lookup to a set, so the calculated field may be needed. Ona lookup, you would only have to save the fixed identifier.

Another option which I have used is to create an update operation to change the names. This can get more complicated. I have one table where I use field rules to trap the original name on the CanChange event. On the OnSave event, I check if the name is changed. If it is, then I run an update on all other tables that use the name field to search for the old name (or name identifier) and update to the new name. This works well if the name is rarely changed since the multiple updates can take some time, but this takes some careful coding to work reliably.

Either method will work. The real questions are how often will the names change and how many tables are effected.

Jerry

[Graham Wickens]

RE: Redundant Data Issues

Thanks Jerry,
the Operator->Unique field is an autoincrement field with creation of a set in mind. I have one set with LOGFILE as Parent with OPERATOR [one-to-one link using logfile->op_unique]as Child and another with OPERATOR as the parent with LOGFILE [one-to-many] as the child. If only the Operator names are changed rather than logfile entry changing Opreator Name it works fine. It seems I will have to try something else...

I'll get back to you on this later.

[Graham Wickens]

RE: Redundant Data Issues

It seems that I need both options, as some identities change operator on a regular basis and others rarely. I've got the global update to work when I change an Operator Name.
Next Question, when a record is deleted from the Operator table is it possible to blank out the corresponding "operator" field values in the logfile table??