Re: Relationships. Does anyone use them?

Correction:

3.) Use VBA code such as the DAO.Relationship object to create the
relationship between the two tables.

Should read:

3.) Use VBA code such as the DAO.Relation object to create the relationship
between the two tables.

Sorry for any confusion.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:4sWdnVHJKYBgMc7ZnZ2dnUVZ_t-dnZ2d@xxxxxxxxxxxxxxx
Hi, Salad.

Over time I'd go into the window and see relationship
spaghetti....tables/queries all overthe place with lots of relationship
lines between here and there.

It's up to you to organize the positions of the tables in that window. If
it looks like a mess of spaghetti, then take a few minutes to organize it
better. As the database designer, you're in charge of this.

After that first app I didn't do relationships. If I had a query, I
defined the relationship.

If you dragged a field from one table to another in the QBE Design Grid,
then you defined a relationship. Otherwise, Jet defined the relationship
whenever the name of the foreign key in the child table matched the name
of the primary key in the parent table. If you planned your database
well, then Jet did all of this defining for you.

But defining a relationship doesn't mean that referential integrity will
be enforced. For referential integrity to be enforced between two tables,
one must:

1.) Create a foreign key constraint between the two tables, or
2.) Define a relationship between the two tables and ensure that the
"Enforce Referential Integrity" checkbox is marked, or
3.) Use VBA code such as the DAO.Relationship object to create the
relationship between the two tables.

Without referential integrity, orphan records can, and probably will, be
created.

so the relationships window is clean of tables.

I don't mean this disparagingly, but this tells me that you are not
building complex databases. One can get away with a lot of bad habits in
simple databases that would cause immense problems in larger, complex
databases. You haven't encountered the ill effects of a lack of
referential integrity because:

1.) You have designed your applications so that tables with one-to-many
relationships have forms/subforms for data input that prevent child
records from being created without a parent, and

2.) Either your code and queries never create records in the child
tables, or if they do, then they don't create the child records without
first checking that there's a matching record in the parent table, and

3.) Either your code and queries never delete parent records, or if they
do, then they don't delete the parent records without first checking that
there are a matching records in the child table and deleting them first,
and

4.) If you don't have Table Data*** Views locked, then your users are
behaving in that they don't edit the tables in Data*** View.

This scenario can be successful in a small IT environment if the database
developer keeps a tight lid on things, but it requires extra work. And
when this application becomes complex, or is handed off to another
database developer for application maintenence, the lack of referential
integrity on your tables is going to become obvious, due to anomolies in
the data and the time-consuming difficulties to code for operations that
the database engine should be handling.

And when there are 25 to hundreds of tables in a database, it's very
helpful to have a diagram of the relationships between the tables. If you
aren't using diagrams, then either you have an excellent memory, or you're
building simple databases without very many tables.

My apps don't appear to suffer from no relationships.

You have relationships. What you don't have are relationships with
referential integrity enforced, and there's a huge difference between the
two in their effects on data integrity.

So is setting relationships just more overhead in creating an app and
unnecessary...or do you believe the app should have all relationships
defined?

Professional database designers define the relationships and enforce
referential integrity on tables in all of the relational databases they
design.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"salad" <oil@xxxxxxxxxxx> wrote in message
news:Sow4g.7230$BS2.6977@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm curious about your opinion on setting relationships.

When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.

After that first app I didn't do relationships. If I had a query, I
defined the relationship. Many of the times when I create a new query
and add 2 tables together it creates the correct relationship between the
two tables. I believe this is due to using a foreign key with the same
name. I cared not about cascading deletes or cascading updates or the
type of relationship so the relationships window is clean of tables.

And if I need to, I'll create a query on the fly via code. Again, I set
the relationships. I know these queries aren't compiled for optimacy
like a querydef but operate well.

My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just more
overhead in creating an app and unnecessary...or do you believe the app
should have all relationships defined?


.