table design and data history

[Laurent Deniau]

I am new to Access (2003) 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 Type@tblTypeA and
Type@tblTypeB 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/links/tutorial about such design?

Thanks,

ld.

 

[TedMi]

From the info you provide, there seems to be no reason to split Type A and
Type B over 2 tables. Instead of presenting a table structure, you should
describe your business problem, and we'll help you devise a date schema to
fit the problem.

 

[mscertified]

It looks like your TypeA and TypeB tables need an autonumber (unique) primary
key. Your TypeC table should then refer to these unique keys.

Dorian