Linking tables

[bamf]

I'm quite new to this so any help will be very much appreciated!

I have a 'master' table that holds the core data on pupils (Each pupil has a
unique idetnifying number). I want to use this data to form the basis of
further sub tables. The sub tables will need to have additional columns but
will have all the data from the master table in the same format. These will
be viewed and updated in form view.

I would like info that is updated in the sub tables to update the master
table and vice versa. I'm not at all sure how to go about this, and am aware
that the overall design may need changing.

thanking you in advance!

 

[Duane Hookom]

You should not save multiple copies of the same information. Your master
table contains the unique ID as well as information about the pupil. Other,
related tables only need to store the unique ID from the master table and
not other information contained in the master table.

 

[bamf]

Thank you, this does make a lot more sense!

I'm afraid I may still need a little more help tho. When setting up the sub
tables how do i create the link back to the master tables? The relationship
between the master table ID and any of the sub tables would be a one to one
relationship.

 

[Duane Hookom]

Many one to one relationships generally isn't ideal. Using forms with
subforms and the Link Master/Child properties are used to "carry over" the
primary key value from a main table to a related table.

 

[George Akers]

Typically two tables with a one to one relationship should be combined
into one table.

 

[Craig Alexander Morrison]

Typically two tables with a one to one relationship should be combined

into one table.

If one performs a full RDA (Relational Data Analysis) all fields (without
repeating data) that can be identified by the same (Primary) Key are
generally designed as a single relation during the optimisation step. It
should be noted that one of the recognised dangers of rigid optimisation
based solely on the (Primary) Key is that subsets of the general population
of that relation may not be properly recognised.

There are sometimes physical implications as to why the data may be split,
however that is another matter.

There are also good reasons to use the 1 to 1 relationship, modelling
subtypes, eliminating nulls and for security.

In brief

Subtypes, when fields may only apply if the relation/table can have several
roles/responsibilites they can be moved to a separate table. For example an
Employee tables could have a 1 to 1 relationship with the Engineer table.
The Employee table contains information about all employes, the Engineer
table contains information about all Engineers who are a subset/subtype of
Employees. There could be other tables for other types of Employess.


Elimination of Nulls, Nulls cause problems in a database because they are
not a value as such. When a field has an unknown value it can only contain a
Null or some database engine managed Default (not seen in Jet). Many posts
here have been concerned with things not working as expected and many times
this is down to Nulls, many problems are resolved using functions such as
Nz() or setting the value to an empty string. The problem is that in some
databases we need to know that the Name is Unknown as opposed to known to be
nothing. If Nz() is used unwisely to update the fields in the database as
opposed to converting them temporarily in a query for the purpose of a
report or form you would not know whether the value was unknown or known as
nothing. If you do not know the Annual Turnover of a firm your database
should reflect that rather than saying it is zero. Of course you could
manage the empty strings and zero value fields, however this would require
an RDBMS such as DB2 or SQL Server etc.

To avoid some of these problems you could move some data from a table to a 1
to 1 relationship say a table of Company and one for CompanyFinancialStatus
now when you run a report using the FinancialStatus fields you could have
already set the fields in that table to required so that the table cannot be
partially completed if you need all fields to have known values.


Security, fields that contain sensitive data may be implemented in a
separate table as part of a 1 to 1 relationship and much tighter
security/encryption can be applied to that table and its fields.

Solid Database Design helps develop Solid Applications.

--
Slainte

Craig Alexander Morrison

Crawbridge Data (Scotland) Limited