Split big table with one-to-one

[John61]

We're making an Access application and one of its tables could have hundreds
of fields. I think I'll split it to a main table and a few sub-tables with
one-to-one relationship. On the entry form, I'll use a tab control and
subforms on tabs (linking with PK, e.g., MAIN_ID). I just don't know the
best way to set key fields for sub-tables.

For example: On the main table, primary key: MAIN_ID (auto number). On a
sub-table, is it better to set a primary key ? i.e. SUB1_ID(PK),
MAIN_ID(FK). Or only FK?

Thanks.

John

 

[John Vinson]

We're making an Access application and one of its tables could have hundreds
of fields.

Then it needs a logical redesign, unless you have an Entity with over
255 atomic, non-repeating, non-interdependent distinct attributes.
I've been working in databases for decades now and the only cases I
know of are intentionally DEnormalized tables, for use as data
repositories or special-purpose reporting.

Could you give an example of what kinds of fields you are using? Do
you have repeating fields with names like Widget1, Widget2, Widget3 or
January, February, March?

I think I'll split it to a main table and a few sub-tables with
one-to-one relationship. On the entry form, I'll use a tab control and
subforms on tabs (linking with PK, e.g., MAIN_ID). I just don't know the
best way to set key fields for sub-tables.

For example: On the main table, primary key: MAIN_ID (auto number). On a
sub-table, is it better to set a primary key ? i.e. SUB1_ID(PK),
MAIN_ID(FK). Or only FK?

If it really IS a one to one relationship, you'ld just have the
MainID, long integer, serving as BOTH the primary key (to make it
unique) and as the foreign key.

John W. Vinson[MVP]