Re: Relationships. Does anyone use them?

salad wrote:

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?

Hi Salad,

One of the basic precepts of relational database design is to have the
data structure do as much for you as possible. Presumably you are
doing much of this already with your table design, ie, allowing nulls,
setting indexes with no repeats and myriad other tasks.

In other database systems, the terms for these sorts of things are
generally called constraints - I believe that is the term used in the
ANSI/SQL standards as well.

One important constraint is what in Oracle is called the foreign key
constraint - this is the equivalent of relationships in Jet.

It's nothing whatsoever to do with speed of queries or SQL statements.
They are to do with ensuring records are not orphaned. There are many
examples were you don't want orphaned records - think of a table of
purchase orders and a table of individual transactions that are all
linked to a purchase order. You don't want the any transactions
standing around without a purchase order - it makes no sense.

FK constraints/relationships are meant to address this sort of thing. This is relational database 101. 8)

It's a very established concept in relational design that it's dangerous
to rely on constraints that are enforced by form design:

Many of the times when I create a new query and add 2 tables together
it creates the correct relationship between the two tables.

This is not a relationship. What you are describing is a join. It's not a constraint or a relationship and it does not prevent deletion of linked records that should not be deleted if you don't want orphaned records.

I
believe this is due to using a foreign key with the same name.

No, this is a simple join. A foreign key has absolutely nothing whatsoever to do with with what you are describing. The name of the joined fields/cloumns is immaterial.

> I
cared not about cascading deletes or cascading updates or the type of
relationship so the relationships window is clean of tables.

I think you've missed the point of what relationships are. See my ruminating above! 8)

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.

You are talking apples and oranges. I think you've mixed up what queries, ie, select sql statements, and relationships are.

The purpose of relationship is *NOT*, repeat, *NOT* to make the construction of queries easier!!! In the Access query builder design window, they are used to help you as a bonus, but again, that is *NOT* what the purpose of relationships, ie, foreign key constraints are.

My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same.

Speed is not a benefit of relationships. Here, you seem to be confusing indexes with relationships.

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

I know some here might jump on me for saying this, but an application without foreign key constraints/relationships is not worth its weight in electrons.

If you aren't having problems, then your applications are reasonably small with respect to numbers of tables and records and the "relationship" between tables.

Salad, your knowledge of VBA and various coding techniques as demonstrated by your responses given here are excellent and over the past couple of years, many folks, including me, have benefited from your posts. But given your question here, I think you really, really, really need to read up on theory and practicality of relational database design and structure.

I hope this was of some help. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
.

Relevant Pages

  • Re: Relationships. Does anyone use them?
    ... more overhead in creating an app and unnecessary...or do you believe ... One of the basic precepts of relational database design is to have ... generally called constraints - I believe that is the term used in the ... of relationship so the relationships window is clean of tables. ...
    (comp.databases.ms-access)
  • Re: Relationships. Does anyone use them?
    ... more overhead in creating an app and unnecessary...or do you believe ... One of the basic precepts of relational database design is to have the ... generally called constraints - I believe that is the term used in the ... What do I care if I didn't go in into the relationships window to set the relationship....for the most part its always done for me. ...
    (comp.databases.ms-access)
  • Re: Delphi apps using Windows Large Fonts
    ... > constraints in the dialog's constructors etc. ... Be careful not to open the app in *design* mode with large fonts. ...
    (borland.public.delphi.non-technical)
  • Re: Why all the max length constraints?
    ... implementations of the RM have taken the industry a bit astray from ... them) a lot of attributes are specified with max length constraints. ... I know nothing of how dbms products are designed internally. ... developers to specify this physical design and limit the implementation ...
    (comp.databases.theory)
  • Re: Why all the max length constraints?
    ... Do you realize that even if no DBMS ... constraints /may/ reduce performance. ... Computer Science and Mathematics at Dordt." ... mention separation between logical and physical design. ...
    (comp.databases.theory)