Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

One-to-one Relationships

One-to-one Relationships

One-to-one Relationships

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

RE: One-to-one Relationships

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


RE: One-to-one Relationships

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close