One-to-one Relationships 1

[JohnHarkins]

Am I correct that if I have a main table that has sparse data, one technique to handle the situation is to break up the table into base and optional data and set the relationship to one-to-one. This assumes that the "-to-one" portion is optional. Correct?

For example, I track voting information and maintain the most current information in the first set of fields and prior information in the second set of fields. (Not every voter has "prior" data.) I would then specify that the second set of fields (in table #2) has an "optional" relationship to the first set of fields (in table #1). That way, for most queries, forms and reports I would specify only table #1. John Harkins

 

[Amiel]

Hmmmm.
I think that you have said that:

[Table1] contains data that is collected for every voter.

[Table2] contains data that may exist for each individual voter.

[Table1] and [Table2] are logically joined by a unique 'Key'.


Using MS Access. Both tables would have a 'rule' which will allow that 'Key' to appear only once (a unique key) in a table. This 'rule' is enforced by Access (Jet actually) itself and is guaranteed to be enforced under all circumstances. In this case, the tables are said to have a "One to One" Relationship.

The reasons (your reasons) for having the data in two seperate tables may for example be, "the data in table1 is completely static but the data in table2 changes often", Or;

for implementation reasons, the data in table1 resides in a seperate module or on a seperate machine, and is available to all users while the data in table2 resides elsewhere and is available to only a single user or department; The list of reasons for using seperate tables may be related to performance, or not ....

From what you describe you will be maintaining historical information and should, it appears, store each change or addition for a given voter as a new record; this implies a "One to Many", relationship.

This information (data) becomes available for as long as you have the database, and usefull historical information can then be retrieved from your tables using queries and functions used to summarize it's contents.

And so..., For each voter that exists, there is the required entry in table1. And Zero (0) or more entries in table2.

The tables are logically joined by a unique "Key" and rules enforced by the DBMS will allow only one "Key' value to appear in Table1 and will allow multiple (duplicate) "key" values in Table2.

This is probably just 'food for thought' during your design stage Amiel
amielzz@netscape.net

 

[JerryDennison]

Let's explore this scenario. If you have "current" voter data and "prior" voter data then what happens when they vote again this year? Do you "move" the current data to prior discarding the previous prior and then add the new current into the current field?

If so, this violates every Form of Data Normalization. Wouldn't it be much better, more efficient, and more meaningful to create a one-to-many relationship between voter and election? You could then not only know the results of that voter for the last election but for every election they vote or don't vote in.