Working with One-To-One relationships

[Guest]

In order to better secure my database I’m trying to make separate tables
joined through one-to-one relationships. I created the relationship, and
authorized cascade updates and deletes. The only problem I’m running into is
inputting new information. Since the same field is required in all the
tables, when I try to add it to one I get an error message saying that an
input is required on the other tables. How can I get an input (i.e. in a
form) to update two different objects simultaneously?

Respectfully,
A-Mart

 

[Guest]

Hi Alex,
Below is a quote from Gavin Powell's book "Beginning Database Design".
One-to-one relationships are often created to remove frequently NULL valued
fields from a table. They are hopefully rare in relational database models,
unless in exceptional circumstances because the price of storage space is
cheap. One-to-one relationships are typical of 4th Normal Form
transformations.

You are therefore obviously doing something wrong. Post your table names and
fields in each and I will try to make suggestions as to how to handle.

Regards,
Barry
www.witstoronto.ca

 

[Roger Carlson]

Actually, security is one of the few reasons for creating one-to-one
relationships.

On the few times I've done this, I've created a Form/Subform arrangement to
input new data. The subform control will take care of adding the primary
key of the main table to the foreign key field of the child table. The fact
that the child table can only have one record makes no difference. As a
result, I usually leave the subform in Single Form View, since there will
only be one record anyway.

I don't know if this will work with your security scheme, though.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[dbahooker]

I think that 'rare' can be quantified as 'Ive been writing SQL for a
decade; and I've had a half dozen legimate situations where I needed
to use a One to One relationship

for example; if you have a ton of text fields-- but you want them
stored in a different table for security reasons

but it's at the same grain-- so it makes no sense to have things at a
different grain

 

[Guest]

Roger, I could see how that would work but it can work with just one table
being the recordsource for both the form and subform without the need to add
a second table. But I'm splitting hairs trying to get the last word, forgive
my insecurity.
Cheers,
Barry