[AccessD] Design Question

Bryan,

This brings to mind Robert Stewart's idea of database unique ID's. 
Instead of having separate autonumber keys for each table, you implement 
one autonumber key for the whole database. You would have a single table 
with a single counter field that all tables used for their next primary 
key. A function would increment the counter, and return the value to the 
form doing the insert.

This way, you could simplify your many-to-many table even further:

LeftID
RightID
LeftTypeID
RightTypeID

Primary key is LeftID, RightID, and LeftTypeID, RightTypeID are also 
indexed.

Left is the ID of the table on the left side of the relationship, Right 
is the ID of the other table. LeftTypeID and RightTypeID key the type of 
relationship (hardware => facility, hardware => person, software => 
hardware, service agreement => software, etc...). The LeftTypeID and 
RightTypeID values come from one lookup table which lists the entity 
types (software, hardware, etc...) you are relating.

There would be a  third table that defines the possible relationships 
between Left and Right. This ensures that there are only one-way 
relationships, in other words, (software => hardware) is allowed, but 
(hardware => software) isn't (that combination would not exist in the 
table).

You can join any table (People, Facility, Hardware, Software, 
ServiceAgreements) to either LeftID or RightID, and they will match only 
their IDs, because each Primary Key ID is unique not only to that table, 
but to every table. Because they are indexed joins, these queries will 
work fast, and will be easy to optimize.

The beauty of this approach is that adding another table to the mix 
causes no redesign of the schema. The new table takes its DB unique IDs 
from the same source as all others. You would just to add one record to 
the entity table for your new table, and records to relationship table 
to define the allowable relationships between the new entity and the 
others. That's it -- you have achieved a purely data-driven extension of 
your schema.

You would wind up with Left and Right hand queries to answer all 
possible relationship questions. For example, the (hardware => software) 
relationship isn't defined (left => right), but it clearly exists (right 
=> left). You would query the relationship table to determine if that 
relationship exists, and if so, use a Left hand query. If not, use the 
Right hand query.

Limits? In Access, this would limit you to 4 billion records total in 
the database, but you'd overrun the maximum file size for the mdb well 
before that! With 64 bit integer keys in a modern enterprise DB, you 
would have no practical limit on the records you could key this way. Of 
course, Robert has ways of overcoming these limits now, but I will leave 
it to him to describe that.

-Ken

> Subject:
> Re: [AccessD] Design Question
> From:
> "JWColby" <jwcolby at colbyconsulting.com>
> Date:
> Tue, 6 Feb 2007 13:30:06 -0500
> To:
> "'Access Developers discussion and problem solving'" 
> <accessd at databaseadvisors.com>
>
> To:
> "'Access Developers discussion and problem solving'" 
> <accessd at databaseadvisors.com>
>
>
> Given the possibility of having multiples, I would probably have the
> individual object tables, then one big m-m "relationship" table where I
> placed:
>
> PEID
> FACID
> HDWRID
> SFTWRID
> SVCAGRID 
>
>
> Probably you would want other info about the relationship such as when the
> relationship was established and when the relationship terminated:
>
> DteEst
> DteTerm
>
> "multiples" come from new records in the relationship table.
>
> A person assigned two pieces of software would be two records, with just the
> peid and softwareid, plus the DteEst.  When that person "turned in" the
> software, the record's DteTerm would be updated.
>
> The only issue with this kind of relationship is that one relationship could
> be terminated while another was not.  If that is possible, then each
> relationship needs to be established individually (separate records in the
> relationship table), so that you can record when each relationship was
> established and terminated.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com