Results 1 to 5 of 5
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Relationship vs. Query Joins (2000 and 2002)

    If there is a fundamental link between data, this should be expressed in the form of a relationship between tables, with referential integrity enforced. In the standard example with orders and customers, the link between them is not arbitrary, it expresses something fundamental about the data structure. So you need a relationship, and to ensure that the user can't enter a non-existent customer for an order, you need referential integrity.
    A relationship does involve overhead, for it needs an index, which can be visible in the Indexes window, or invisible. But you need that index anyhow. In the above example, you will want to present an ordered list of customers.
    If you need a join between tables only for some kind of summary calculation, you can create the join in a query, there is no need to create a relationship between the tables if it doesn't express a fundamental link in the data structure.
    A complete course in normalization, however useful, is probably too much for a user group session, and will probably make some participants fall asleep, but I would certainly pay some attention to it. Relationships are an essential part of normalized data structures. When you start to work out the data structure for a database, working through the steps for normalization forces you to think through the relationships.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating Relationship vs. Query Joins (2000 and 2002)

    Developers who come from backgrounds like SQL Server, Oracle, etc. , often feel that explicit relationships are unnecessary because they can be handled through triggers and constraints in database server products. Unfortunately, Access doesn't have triggers and that sort of constraints , so we're left with relationships. Defining relationships just so the joins will show up automatically in the query builder is lazy and unnecessary, but not everyone realizes that the relationship window isn't simply a drawing tool. The relationships created there do use resources, even without referential integrity. Creating relationships to enforce referential integrity, however, is absolutely necessary, and is a vital part of relational design.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Creating Relationship vs. Query Joins (2000 and 2002)

    <P ID="edit" class=small>(Edited by charlotte on 11-Mar-04 13:10. )</P>Edited by Charlotte for <!rule=10>Rule 10<!/rule>

    I am preparing a presentation for a monthly Super User Group session and this month's topic is Access Relationships and Report Hints & Tips. I'm a newbie to this forum, but after reading numerous posted decided to post an opinion question! Perhaps some of you "experts" will be willing to share your ideas with me so I can share them with my end user group!

    The question, when should one define relationships and/or when should we depend on query joins to accomplish our tasks? I understand referential integrity for the most part and personally think relationships are good. However, while talking with one of our in-house programmers about relationships, I've gotten a different opinion. He believes that relationships are unnecessary, add overhead to a database, and that one can accomplish everything you need by using query joins. How much overhead does relationships actually add to any database and is there noticeable degradation when using relationships? Your thoughts?

    Can some of you give me your opinions about both, when I should tell my users to use or not use relationships, when joins would be better, etc. Should I add a discussion on normalization to the session because I'm a believer that most casual users don't even understand what it is!

    Your ideas and suggestions for this session are most welcome. I'm going to add another post re report hints! Thanks again.
    Thanks much, Judy Crawford

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Creating Relationship vs. Query Joins (2000 and 2002)

    Thanks Charlotte. That gives me some prespective to take along with me! Appreciate your response. Judy
    Thanks much, Judy Crawford

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Creating Relationship vs. Query Joins (2000 and 2002)

    Thanks Hans, this give me more to think about. Indeed I see the value of relationships for referential integrity. Appreciate you taking time to write. Judy
    Thanks much, Judy Crawford

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •