Best Practices

[dfass]

I have some questions regarding the best way to construct a database. Most
of my issues are textbook simple, but some require advise from a more
experienced hand.

The main items in my database are documents, each a legal agreement. Call
my central table tblDocs. It has fields like dtDate, tblFund_ID (Party 1 to
the agmt., crossreferenced to a table of all my funds), tblCntrprty_ID (Party
2, cross referenced to a table of my counterparties), txtTitle,
tblAgmtType_ID (crossreferenced to a table of agmt. types, which itself has a
field connecting each type to a member of a table of more general
categories), tblStatus_ID (status of the agmt.), blah blah blah. It gets a
little more complex because I have to track versions of the agmts; but,
still, the work of the database is pretty simple, mostly retrieving groups of
agreements by combinations of the criteria stored in the fields of the
tblDocs table.

First issue:

Some documents are actually amendments to other existing documents--not
versions, mind you, but distinct agreements for which I will need to record
the same info as for original agmts; nonetheless this "amendment"
relationship needs to be refected in my datebase, and will need to be
utilized down the road to show that a particular doc has such amendments. I
expect that I will need to return any and all amendments with requests that
would return the original document. I will also have to distinguish types
for these amendments--some are complete replacements of the original, others
simply modify the original slightly, some fall into predictable categories,
like name changes.

Should I:

Include the amendments in the same table as other agreements? (Would it work
to include a field, only for those agmts. that are also amendments, that
pointed to another record in the same table--that is, the doc amended?)
Create a separate table for amendments, including the same fields as the
tblDocs (or at least most of them) plus a field containing the key to the
record in the tblDocs of the doc being amended? Any wisdom or comparisons to
solutions used in similar situations would be greatly appreciated.

Second issue:

While they are not common, there are some agmt. types that actually involve
a third party (always a second member of the tblCntrprtys). It generally
makes very good sense to just have my party1 (tblFunds_ID) and party2
(tblCntrprty_ID) fields in my tblDocs, since each field corresponds to a
distinct group of members, each of stored in their own tables (see tblFunds
and tblCntrprtys above). More sense than changing to an intermediary table
to resolve a many-to-many relationship between parties and agmts., yes? The
only thing I can think of is to include an optional field for thirdparty in
my tblDocs, but would that not mean sacrificing--or at least greatly
compromising--the ability to query for, say, all docs for a given
counterparty? Or is there a way to create an intermediary query(s) on which
such queries would be run? Hopefully the jist of this issue is clear and
familiar to one of you database experts out there, and you will be able to
make some best-practice suggestion?

Respondents: thank you so much, in advance, for any help you can provide.

dfass

 

[Brian]

I can't get into all the specific complexities of your project, but here are
a couple of hints:

First issue: I would store all the documents in a single table but add a
"ParentDocumentID" field. When making an amendment or other copy of one
document from another, record the original document ID in this field, then
proceed with the changes to the "child" document. Later, when you run a
report, you can easily tie them together by finding the ParentDocumentID and
then listing all of its child documents. You might have a "ChildType" field
also that specifies the type of relationship to the parent: replacement,
amendment, etc.

Of course, you will also need to provide some logic in VBA that prevents a
child document from being its own parent, etc.

Second issue (or at least one small part): You can always run a query where
Party1 = "CompanyABC" or Party3 = "CompanyABC" so that you can find all
entries where CompanyABC is either Party1 or Party3 for the document. Also,
yes, you can create an "intermediary" Query1, then use this query as the
source of Query2 instead of using a table as the source of Query2.