How do I connect 6 normalised (3NF) tables which were one table?

[micromoth]

How do I connect 6 normalized (3NF) tables which were one table?

I have split a large table into six. The main form has been reduced in
size and keeps its "mainID" autonumber. The other five tables all
relate to the original table. I kept "mainID" in the other five tables
and connected up a one to one relationship. Reading some of the
threads I think this is wrong but I am not sure that all the threads
are relevant to me.

What is the process to keep a connection when tables are split for
normalization?
Do I need to change the autonumber on the "five" tables?

 

[Jeff Boyce]

On what basis did you decide to split? If the relationship is one-to-one
between your "main" table and each one of the six, then you are dealing with
a sub-type situation, right? If you aren't, reconsider how you've split the
data.

The way to keep "child" tables connected to their "parent" is to use a
foreign key field in the child table.

If you are working with a sub-type relationship, you could use the parent id
(mainID) as the primary key of your sub-type table(s), without using an
Autonumber.

Is there a chance that you've tried to use an Autonumber field in the six
tables, hoping that it would keep the same value as the mainID? If so, it
won't! Autonumber fields are independent from one table to the next.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[micromoth]

I was advised to by tina on the forms group who gave me a reference to
a normalization pdf. I split the table as it had 50 fields most of
which were not used in most cases. Ten were used most of the time, but
the rest were mainly mutually exclusive but not actually exclusive.
With spliting in most cases, three of the six tables will be used each
time. in general I think it is the right thing to do.

I think it is a sub-type situation, going from your context, but I dont
know what you mean exactly. If the paragraph above fits with sub-type
can you just confirm this for me?

When I made the new tables I copied the existing table and deleted the
extra fields but kept the autonumber as a PK/FK. From your explanation
I see this would not work as each new table will make its own PK which
will eventually not match the original. In the 5 child tables do I
need to delete the PK/FK feild and create a new one or can I just
change from autonumber to number?

As you describe- I want my "project" to have the parent id (mainID) as
the primary key of your sub-type table(s), without using an Autonumber.

Cheers Graham

 

[Jeff Boyce]

Graham

You want to have the number of the record in your "child" tables, not an
Autonumber field. The corresponding (foreign key) field is a LongInt.

Sub-typing ... hmmm... an example...

You have People. They each have a Name and a PhoneNumber.

But for some of those people you are concerned with their academic
attributes (and are NOT concerned about the academic aspect for any of the
other people).

Some of the people are truck drivers, about whom you wish to keep
truck-driverly data (and you don't care to about the academics, or any other
'types' of people).

Some of the people are actually corporations, and you want to keep
corporate-only data about them.

If the data you keep about each 'type' of person is mutually exclusive
(completely mutually exclusive!), you can use your Person table to document
common/shared data, and the sub-type tables for the mutually exclusive data
attributes.

More or less confused?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[micromoth]

Thanks very much Jeff

It is a subtype that I very nearly have. I will take the few fields
that apply to a number of the subtypes and put them back on the parent
table. Your explanation makes perfect sense, actual examples help me a
lot. I will spend today doing this. and go back to forms and getting
started later for the next problems.

Cheers Graham, and thanks again for your help.