Applying a relationship retrospectively

[Kevin]

Hi,

I've inherited an Access 97 database in which approx 7 out
of the 10 tables are free standing and not related (via
foreign keys) to central main table. This main table
contains fields which are populated from records from
these remaining subsidiary tables (which contain
information such as status values for records in the main
table etc) but this is done in the form for the main table
by inserting the text from the other tables, rather than
by inserting the primary key from the correct subsidiary
table. In all cases, the subsidiary table contains only
one field which is also it's primary key.

Can I establish these relations in retrospect ( the main
table contains a considerable number of records with these
virtual relations to the subsidiary tables already
populated). If so how ? Do I simply drag the primary key
from the subsidiary table into the corresponding pre-
existing and pre-populated candidate foreign key field in
the main table ? Or, should I live with the existing
structure. I would assume that aside from it being the
correct thing to do for a relational database, the proper
establishment of the relationships would improve the
performance of any queries involving those fields or would
it not make ant difference if the subsidiary table only
contained one field ?

 

[Gina]

If it were me I would establish the relationships. However, I hope you have
time and patience (and there aren't ALOT of records), this could take some
time.

You may get error messages... data intregriety and so on but it will be
worth it in the end.

 

[Tim Ferguson]

Do I simply drag the primary key
from the subsidiary table into the corresponding pre-
existing and pre-populated candidate foreign key field in
the main table ?

You can make an update query, that will recognise the unwanted values and
insert the proper Foreign Key value instead; and then you can remove the
unwanted columns.

Or, should I live with the existing
structure. I would assume that aside from it being the
correct thing to do for a relational database,

There is a very strong argument that says, "if it ain't broke, don't fix
it", and this applies powerfully to computer software. You may introduce
some unexpected bugs if your relational analysis is off. For example, it
seems wrong to keep five lines of DeliveryAddress in an Orders table, when
it could be a FK to the Companies table... but if just once someone needs
goods delivered to their home address then you'd be stuffed. So check that
dependent tables really are dependent.

the proper
establishment of the relationships would improve the
performance

No: R theory is about guaranteeing "correctness" and is generally
antithetical to performance. That is why you'll see people sometimes
talking about "judicious denormalisation" because they are prepared to risk
some inconsistency and contradiction amongst the data, with heavy
safeguards, in order to get round some procedures that are really slow.

Hope that makes some sense
All the best


Tim F