no table relationships defined

[Craig Buchanan]

I've been analyzing a client's database (MDB) and noticed that the database
doesn't have any table relationships defined. The tables do, however,
include primary keys and indicies.

I've never seen a situation before and wonder what are the consequences of
such an architecture. I realize that they won't be able to enforce
referential integrity, but I am also curious about other negative affects.

If this database were to be upsized to SQL Server, what other consequences
might they experience?

Thanks in advance.

 

[Michel Walsh]

Two other inconveniencies:

- Queries plans may not be as effective (being sure a relation exists may
bring faster execution of some plans than having to make the "lookup"). As
example, a NOT IN may be removed if the child table is checking if its
parentID is in the parent table, or not... No need to make that check if the
DRI is there.


- Tools may have restricted default behavior (as example, LINQ, some ADONet
tools, etc.). As example, if you use disconnected records, you get temp
'autonumber' and at synchronization with the db, when you have DRI, most
modern tools automatically propagate the real key you get, from the db, to
the records which depends on it. If the DRI is not there (or if you didn't
make mention to your tool about that relation) that useful feature won't
work automatically for you, and the application has now SILENT bug
possibilities (which are harder to identify than bugs detected at compile
time, or producing a run time error, such as DRI would do).


But definitively, enforcing data integrity is, all by itself, more than
enough, in my very humble opinion, to ADD them, at the database design
level, even though I have met a "professional" developer who was ready to
immolate himself in protestation if we were to add any DRI 'hard coded' at
the database design level! We really meet very strange people, sometimes.
Not to mention that this way of doing stuff is definitively very poor and I
judge that as a clear lack of respect to client property (its data). There
is no way DRI at db level can be replaced by validation through code! No
way!





Vanderghast, Access MVP.