[AccessD] One-to-one relationships

Gustav Brock gustav at cactus.dk
Fri Apr 4 07:09:58 CST 2003


Hi Joe

This is an example where a compound index may come in handy. Combine
this with a simple field validation rule and you're off.

Consider this schematic table design:

Main
  ID
  TypeFK

Type
  ID

Category1
  ID
  MainFK
  TypeFK

Category2
  ID
  MainFK
  TypeFK

Category3
  ID
  MainFK
  TypeFK

All ID-fields are autonumbers. Type.ID may be a normal long.

Table Type contains three records, one for each category.

A compound unique index is created on ID and TypeFK in table Main.

Type.ID has a 1-M relation to Main.TypeFK.

Main.ID and Main.TypeFK has a 1-1 relation to Categoryx.MainFK and
Categoryx.TypeFK

Now, add to the three fields Categoryx.TypeFK a validation rule:

  =x

where x is the ID (1, 2 or 3) of the type of that category.

In this way you can add any or no entry in a category table for an ID
of the main table.
If you cannot add the TypeFK field to the main table then you can move
this to a link table, say tblCategory, with the fields

  MainFK
  TypeFK

fitted with a compound index and a 1-1 relation to table Main.

If anyone could come up with a solution without the compound index I
would like to see it.

/gustav


> I have four tables, one is the "main" table and the other three are category
> or type tables with a one-to-one relationship to the "main" table.

> I want to limit a record that is in the "main" table to exist in only one of
> the category tables with the flexibility of the record moving, if you will,
> from one category table to another. For example, if during the creation of a
> new record the user picks category1 from a combo box, he/she is presented
> with the fields that are found in the category1 table. If at a later point
> he/she decides to change the category to category2, the information from
> category1 would be removed and the user would fill in the information for
> category2.

> I have coded this before and everything worked out well, but what I was
> wondering is if the ability to limiting to one category table could be
> achieved using some form a relationship rules in Access. Or can this only be
> accomplished with coding?



More information about the AccessD mailing list