Dealing with revisions

[ryan_eng]

The pressure is on people, need some help!

My database has a table of "activities" to store drawings, documents and
miscellaneous activities. I put all three in one table to simplify the design
and also because the types of data required for each is almost identical.
Anyways, drawings and documents can have revisions, and I need the ability to
treat the drawing/document number + revision as the "primary key" to enforce
referential integrity. I need some advice on how to set up the primary key
for the Activities table such that the combination of drawing/document
numbers + revision number aren't duplicated.

Thanks
RYAN

 

[Steve Schapel]

Ryan,

If a drawing or a document can have more than one revision, it sounds
like you might need to consider a second table for Revisions, related
many-to-one to the Activities table.

 

[Sprinks]

Ryan,

As there is a one-to-many relationship between a Document and its Revisions,
I would use a main form based on Activities, and a subform based on a new
table, ActivityRevisions. You can set up a multiple-field primary key for it
(the ActivityID and the RevisionNumber), however, most developers prefer a
single autonumber primary key, and adding a multiple field index to prevent
duplicates.

ActivityRevisions
--------------------------
ActivityRevisionID AutoNumber (PK)
ActivityID Integer (Foreign Key to Activities)
RevisionNumber Integer or Text, depending on your naming system
..... any other revision-specific fields

To add the multiple-field index, load ActivityRevisions in table design view
and select View, Indexes. Cursor down to a blank row, and add a new Index
name, such as "Unique Activity Revision". In the field name column, enter
the ActivityID, then cursor down one row and add the RevisionNumber, leaving
the IndexName column blank.

In the Index Properties area, change Unique to Yes, and exit and save your
changes.

Hope that helps.

Sprinks

 

[ryan_eng]

Steve, thanks for the idea. It made me think of another way to go about this,
but it pushes my databse into a "configuration control" tool which wasn't the
intention. So, I think I'm going to take a step back.
Is it normal for a simple database to snowball into an unmanageable complex
mess???

RYAN

 

[Steve Schapel]

Ryan,

Is it normal for a simple database to snowball into an unmanageable complex
mess???

No, I have never experienced this.

The complexity of a database is pretty much determined by a combination
of the real-life nature of the data you need to manage, and the
considerations of what you want to do with it. If you are suggesting
that it is adding complexity to a database to represent data which has a
one-to-many relationship into two related tables, then this is a
misapprehension. This is not complexity, it is just the way databases
work. In the end, it is more complex to try and manage related data
within a single table.

 

[Jeff Boyce]

Assuming your question wasn't rhetorical, what I've found often enough is
that a simple understanding of a situation becomes much more complicated as
the details get uncovered.

The "mess" you are trying to model may be the reason that the database is
starting to look "messy" <g>!

Regards

Jeff Boyce
<Access MVP>