Can someone tell me how to code sets properly

[Graham Wickens]

I have been playing about with sets for a long while but cannot get to a satisfying way of doing things.

in a set called "Testing";

I have a parent Table called "Logfile" and three child tables.

Child 1 "Aircraft_Type" link is logfile->at_unique = Aircraft_type->unique
Child 2 "Operator_details" link is logfile->op_unique = Operator_details->unique
Child 3 "Base_Details" link is logfile->B_Unique = base_details->unique

Child 2 is also a parent to Child3 with link operator_details->b_unique = base_details->unique. (This is needed as I only have the home base for a lot of entries and no owner/operator).

I want to be able to update records in the Child tables and it be then shown in the parent table. For example if "American Airways" changed its name to "American Airways International" I want to just update the relevant entry in the Operator_details table and get the 800+ entries in the Logfile table to reflect this, rather than having to go through the 800+ records on logfile (Yes, American Airlines has 800+ aircraft!!).

What is the best way to code this as the method I am using works but is not really the way to do it.

see attached form, the three fields in violet font are the 3 uniques linked to the child tables and the fields in red font are the values called by the unique values. I want to find a way of making the 3 unique fields transparent in any changes that I have to do on the data.

thanks in anticipation.

[Stan Mathews]

I have been playing about with sets for a long while but cannot get to a satisfying way of doing things.

in a set called "Testing";

I have a parent Table called "Logfile" and three child tables.

Child 1 "Aircraft_Type" link is logfile->at_unique = Aircraft_type->unique
Child 2 "Operator_details" link is logfile->op_unique = Operator_details->unique
Child 3 "Base_Details" link is logfile->B_Unique = base_details->unique

Child 2 is also a parent to Child3 with link operator_details->b_unique = base_details->unique. (This is needed as I only have the home base for a lot of entries and no owner/operator).

I want to be able to update records in the Child tables and it be then shown in the parent table. For example if "American Airways" changed its name to "American Airways International" I want to just update the relevant entry in the Operator_details table and get the 800+ entries in the Logfile table to reflect this, rather than having to go through the 800+ records on logfile (Yes, American Airlines has 800+ aircraft!!).

What is the best way to code this as the method I am using works but is not really the way to do it.

see attached form, the three fields in violet font are the 3 uniques linked to the child tables and the fields in red font are the values called by the unique values. I want to find a way of making the 3 unique fields transparent in any changes that I have to do on the data.

thanks in anticipation.

I'm certainly no expert on database normalization but if I understand the principles in any small degree, the process will solve your problem.

If you don't store a piece of data in more than one place then you only need to change it in that one place. If your aricraft_type table held a manufacturer id in each aircraft record which linked to a manufacturer table and the table of manufacturers contained fields for details for each id such as name, address, etc. then you only need change the manufacturer name in the manufacturer table as opposed to in each aircraft record.

[Graham Wickens]

You get my Drift! :)

but how do I make the unique values that link the data transparent to updating?

[Stan Mathews]

You get my Drift! :)

but how do I make the unique values that link the data transparent to updating?

Nothing gets updated with the normalization in place. The manufacturer name and other manufacturer specific details are removed from the aircraft_type table.

If you want a report or other layout that lists/displays aircraft type and manufacturer you base the report on a set that includes both tables (aircraft_type as parent linked one to one to manufacturer). If you want a report that lists manufacturer and the aircraft types they make you base the report on an inverted version of the same set (manufacturer as parent linked one to many to aircraft_type).

Normalizing the tables is quite the pain but the benefits are tremendous.

[Graham Wickens]

Sorry for being thick, but what do you mean by normalizing?

[Stan Mathews]

Sorry for being thick, but what do you mean by normalizing?

One discussion here, but there are many others.

[Graham Wickens]

Thanks Stan,

SOme bedtime reading ! :))

[Graham Wickens]

OK I've read the article on Normalization. Most of my tables adhere to the rules. My sticking point is how to setup the tables so that I only need to update one occurence of the field B_Unique.
I've come up with the following that might solve half the problem, but I am not quite sure how to code it:

In "Logfile" table remove fields "op_unique" and "b_unique", add field "pointer" to point at intermediate table called "Pointer" which has fields "Pointer", "Op-Unique" & "B_unique".

Pointer->pointer = logfile->pointer
Pointer->b_unique = operator_detail->b_unique
Pointer->op_unique = operator_detail->op_unique

If I have to change Operator->b_unique, I code the lookup using Base_details but to actually update pointer->b_unique.

This would only mean changing one field value [pointer->b_unique].

This is where I am stumped.

If I want to change logfile->pointer to change pointer->op_unique and/or pointer->B_unique [So that the current record in Logfile now points to a different operator_detail an/or base_details]

How do I code it? I will obviously have to change existing "Pointer" table values and add values as updates take place.

Also, If I only have information on the base_details for the current logfile record, how does that get done? [ie no op_unique value known].

[Stan Mathews]

I'm lost in your explanation of various linkages needed because I don't know your field and the terms are mysterious to me. Logfile, operator, b_unique etc are just so many words that I can't relate to an example.

Let's go back to the example of aircraft_type and manufacturer. Anything that you want to store that is dependent on the aircraft_type is stored in that table but only to the extent necessary. Aircraft specific details such as weight and wingspan would be stored in the aircraft_type table.

Obviously you want to be able to store information about the manufacturer for a particular type. If you want to store more than the manufacturer's name you should use a separate table, develop a manufacturer id system, code the existing entries in the aircraft_type table, remove the manufacturer name from the aircraft_type table, then build a manufacturer table with the ids and the pertinent information.

From this point on you only need to change the manufacturer's name in the manufacturer table to have it appear in its new form throughout the application.

You might perform the same exercise on the engines which propel the aircraft. Propeller or jet, number of cylinders if applicable, thrust, average rate of fuel consumption, etc are all characteristics of the engine, not the aircraft and should be stored accordingly.

I'm sorry I can't answer your question more directly but hope the principles are more clear.

[Graham Wickens]

Thanks Stan,

I'll now go and sit down and rationalise all my tables along those lines. If I get stuck I'll post another question.