473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

table design and history

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
0 1153

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Martin Mrazek | last post by:
Hi, I have tables named like ag97a027, ag98a027, ... where 97 and 98 is from year 1997, 1998 and a027 is just specification of product type. I need carry out repeated operataions over these...
47
by: Neal | last post by:
Patrick Griffiths weighs in on the CSS vs table layout debate in his blog entry "Tables my ass" - http://www.htmldog.com/ptg/archives/000049.php . A quite good article.
4
by: Fred | last post by:
Hi. What is the usual and what are the possible fields to use for the primary key of an intersecting table of a many-to-many relationship? I would think the typical, most common fields would...
5
by: Ilan Sebba | last post by:
When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid. Say I have two parent tables (eg Course, Student) and one...
15
by: kimi | last post by:
I have just started working on a project that is partially complete. It is an application that is using access to store test results. The test results are being stored in two Access 2000 databases....
3
by: John Baker | last post by:
Hi: I have a table with many months of data on it. I am attempting to create a delete transaction, based on matching a date in the table with a date in another table. The match works perfectly...
8
by: chrisoftoday | last post by:
Hi, I'm having trouble with some white space that's appearing in my table. I can't seem to spot the problem, it'd be great if someone could help. The HTML code (ignore the PHP, it shouldn't be...
3
by: Prochot | last post by:
I'm having trouble figuring out how to design my tables and forms to suite my purpose. I'm creating a crewing database to assign and track employees and jobs over multiple shifts. I would like...
5
by: orandov | last post by:
Hi, Facts: I created a database to support an application that tracks events on different objects. The two main tables are tbl_Object and tbl_EventLog. Each table has unique ID and on the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.