By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

table design and history

P: n/a
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.
Oct 16 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.