I am new to Access and I would like to do something very simple and
probably very common:
tblTypeA
PK Type
PK CreationDate
tblTypeB
PK Type
PK CreationDate
tblTypeC
PK Type
PK CreationDate
PK TypeA -Type@tblTypeA
PK TypeB -Type@tblTypeB
TypeA and TypeB in tblTypeC should have referencial integrity and update
(not delete) while the relation is many-to-many due to the history of
each Types, that is many tblTypeC can refer to many tblTypeA and vice et
versa.
The problem is the links TypeA and TypeB in tblTypeC which do not accept
referential integrity (No unique index found). I know that many-to-many
links should be done using junction tables (putting TypeA and TypeB into
two separate tables), but since this scheme appears in all our tables,
it would require a lot of small tables to create these many-to-many
links (all tables needs to manage an "history" of the records, therefore
this would double the number of tables). I don't know if the use of APK
would be better since I am not sure that I could ensure uniqueness of
the combination of the PK fields in tblTypeC for example.
Any clue about such design?
Thanks,
ld.
