Working with One-To-One relationships

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex T. Martínez - 16 May 2007 16:40 GMT

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

Reply to this Message

Barry Edmund Wright - 16 May 2007 17:04 GMT

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

> 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
[quoted text clipped - 6 lines]
> Respectfully,
> A-Mart

Reply to this Message

dbahooker@hotmail.com - 17 May 2007 00:09 GMT

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

On May 16, 9:04 am, Barry Edmund Wright
<BarryEdmundWri...@discussions.microsoft.com> wrote:

> Hi Alex,
> Below is a quote from Gavin Powell's book "Beginning Database Design".
[quoted text clipped - 22 lines]
>
> - Show quoted text -

Reply to this Message

Roger Carlson - 16 May 2007 19:13 GMT

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.

Signature

--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

> 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
[quoted text clipped - 7 lines]
> Respectfully,
> A-Mart

Reply to this Message

Barry Edmund Wright - 18 May 2007 15:46 GMT

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

> Actually, security is one of the few reasons for creating one-to-one
> relationships.
[quoted text clipped - 19 lines]
> > Respectfully,
> > A-Mart

Reply to this Message