Best setup for one to one design?

I would like advice on the best way to implement a one-one design. In this
example, I have one person with multiple (over 200) details about them.
Instead of using one table, I setup a table with main info, another with
medical info, another with education info, another with private info, etc.
each table has a fk to the main table primary key. Questions: Would you
just make one big table instead of splitting them up? What's the recommended
way to link the tables? If I relate the fk in the related table to the prikey
in the main, it treats it as a one-many, no matter what. I setup the forms
using a main form with subforms for each linking table but it treated it as a
one-many, even tho I didn't specify it as such. I'm going to be designing
several dbs using one-one (as well as one-many) and I need to get the linkage
right. I've seen many examples in books of one-one using the prikey of the
main and a fk of a related table...

don't u love it when you figure out some of the answer for yourself? I
changed the fk in the related tables to show the index as 'no duplicates'
then changed the subform property for AllowAdditions to 'No'....any other
ideas or ways to simplify the one to ones?

"Maarkr" wrote:

I would like advice on the best way to implement a one-one design. In this
example, I have one person with multiple (over 200) details about them.
Instead of using one table, I setup a table with main info, another with
medical info, another with education info, another with private info, etc.
each table has a fk to the main table primary key. Questions: Would you
just make one big table instead of splitting them up? What's the recommended
way to link the tables? If I relate the fk in the related table to the prikey
in the main, it treats it as a one-many, no matter what. I setup the forms
using a main form with subforms for each linking table but it treated it as a
one-many, even tho I didn't specify it as such. I'm going to be designing
several dbs using one-one (as well as one-many) and I need to get the linkage
right. I've seen many examples in books of one-one using the prikey of the
main and a fk of a related table...

Although that many fields in a table is likely to be un-normalised there are
still certain cases where you may need that many fields (as is yours!).

Instead of using foreign keys in your tables why not make them primary keys
then you will also get a one-to-one relationship?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


"Maarkr" wrote:

don't u love it when you figure out some of the answer for yourself? I
changed the fk in the related tables to show the index as 'no duplicates'
then changed the subform property for AllowAdditions to 'No'....any other
ideas or ways to simplify the one to ones?

"Maarkr" wrote:

I would like advice on the best way to implement a one-one design. In this
example, I have one person with multiple (over 200) details about them.
Instead of using one table, I setup a table with main info, another with
medical info, another with education info, another with private info, etc.
each table has a fk to the main table primary key. Questions: Would you
just make one big table instead of splitting them up? What's the recommended
way to link the tables? If I relate the fk in the related table to the prikey
in the main, it treats it as a one-many, no matter what. I setup the forms
using a main form with subforms for each linking table but it treated it as a
one-many, even tho I didn't specify it as such. I'm going to be designing
several dbs using one-one (as well as one-many) and I need to get the linkage
right. I've seen many examples in books of one-one using the prikey of the
main and a fk of a related table...