Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Are Relationships Necessary?

    I almost wrote, in the tradition of James Thurber,
    "Is Sex Necessary?"
    It has crossed my mind more than once that there is something superfluous about defining relationships between tables in an Access database because it is queries that really handle all the linking up work anyway.
    Can someone tell me if a pre-defined TABLE relationship serves any other purpose than providing a default join when you create a query involving those tables?
    I mean, you can delete the join when you create the query anyway and replace it with another more pertinent one. What's more I usually do. All the books, including Woody's, talk about table relationships as if they were God but none is especially clear about their true function. <img src=/S/oink.gif border=0 width=15 height=15>

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

    Re: Are Relationships Necessary?

    Defined relationships are the essence of relational design. You could have a bunch of independent tables and call it a database, but until you start defining the relationships between tables, you haven't really addressed the issues of how the data in one table affects the data in another. If you haven't explored the subject of referential integrity, look up the subject in on-line help and on the MS site, or anywhere else that takes your fancy.

    The discipline of defining relationships also leads you to a better designed database. Once you start trying to create the relationships between the tables, you may discover that your keys leave something to be desired, and you'll go back and modify your design.

    Using referential integrity, which is only available in pre-defined relationships in Access, you can prevent a record from being entered into one table if no parent record exists in another. You can also prevent a parent record from being deleted if it would orphan child records. With cascading updates, you can even insert foreign keys automatically into child tables. With cascading deletes, you can ensure that if a parent record is deleted, all its children are deleted automatically.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Are Relationships Necessary?

    Thanks Charlotte. A nice summary - and much needed!
    In fact I was aware of all you spoke of but needed to see it clearly stated. I can't remember reading a simple summary as to the need for database relationships which really drives the point home. Discussions are of it are common but rather woolly.
    In my current work I have a table called TRANSACTIONS which details transfers of money from one account to another, all the accounts being in a separate table called, funnily enough, ACCOUNTS. But I can't really define a useful relationship between the two tables (even though you'd think you would) because there is a source account and there is a target account and which of these two fields would you link? Instead I've used a query grid to connect the TRANSACTIONS table twice to the ACCOUNTS table. But how could you ensure that no account was referred to that wasn't in the "parent" table ACCOUNTS? I've used a combo box to make my EDP person choose from a list.
    If this seems a dumb arrangement please tell me!
    Thanks again.
    <img src=/S/bow.gif border=0 width=15 height=15>

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

    Re: Are Relationships Necessary?

    In your relationships diagram, add the Accounts table once and the Transactions table twice. Define a join between the primary key of the accounts table to the FromAccount in the first Transactions table instance. Define a join between the primary key of the accounts table and the ToAccount in the second Transactions table instance. You should be able to enforce referential integrity on both joins.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Are Relationships Necessary?

    Of course.
    Since I turned 50 by brain is definitely going!
    Thanks, it's a good suggestion.
    David

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Are Relationships Necessary?

    Hmm.. I did it and it worked except when I re-opened the relationships table the relationship to the second Transactions table disappeared and was replaced by a new instance of the Accounts table with a relationship to the Transactions table "targetAccount" field. How weird.
    There are other magically appearing intances of tables as well but I had previously ignored these. Access must be putting something it prefers in place of what the user wants.
    David

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

    Re: Are Relationships Necessary?

    It looks weird, but the relationships diagram is only a way of describing the relationships, not the real thing. What you set up should work properly, regardless of the way Access chooses to display it.

    Oh, by the way, watch it with the over 50 remarks! <img src=/S/granny.gif border=0 width=20 height=20> <img src=/S/grin.gif border=0 width=15 height=15>
    Charlotte

  8. #8
    New Lounger
    Join Date
    Mar 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Are Relationships Necessary?

    For simple databases, no. I don't use them, don't have them, don't need them. If I needed something like a relationship, joins work.

    "Simple" meaning no need to... Well, I write complex single-user databases without relationships. Course, I'm a one-step-at-a-time fellow with no intense complicated data entry work. I keep data clean and if I want an update I write me a query to update.

    But, I imagine they make it easier for some people. And for some database designs, I guarantee they are a must.

    Just, I haven't found any use for them in the last 5 years...

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Are Relationships Necessary?

    I tend to agree with you and that's why I posed the question in the first place. My database is moderately complex in that it performs a variety of integrated functions using VBA but all my data entry is through forms with subforms and combo boxes etc. So although Charlotte said that a relationship can prevent data entry errors
    (using referential integrity) the occasion never arises because of my rigid methods of data entry. You would have to open the tables directly to enter data that didn't relate properly.
    thanks for your comments

  10. #10
    DAW
    Guest

    Re: Are Relationships Necessary?

    As well as all the benefits that Charlotte described - I believe that explicitly creating relationships improves database performance because table indexes are created. (please correct me if I am wrong someone).

    You should therefor always define relationships - even if you are bothered about enforcing referential integrity (which you should really do anyway).

    Dave.

  11. #11
    zxis
    Guest

    Re: Are Relationships Necessary?

    Relationships and referential integrity (RI) are necessary to mitigate data corruption, whether you are designing a single user or multiuser database. When used with simple datasource tables, you get a flexible point of data entry control that minimizes data corruption. Most of my projects are 60%+ data fixing and migration and 40% programming and customization.

    It takes quite a multitude of custom routines ran on a regular basis to duplicate the simple functionality of relationships and RI in order to minimize orphaned records and corrupt key fields. One has to be quite aggressive in overall database management in order to mitigate these problems. Even then, you always have some level of data error creeping into your database. It is easy to break out simple data source tables that can be used to automate and control data entry.

    If you are tainted on RI due to problems batch updates, use a temp database (easier to compact and not put your main database at risk of corruption) along with a stacked set of queries to load the data in sequential order. That is load and update the master records and then the dependent records. I often build in scan routines that check the master record keys before uploading the dependent data. With the right indexing it takes negligible time, even on large datasets.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Are Relationships Necessary?

    Well! That is quite tough nut to chew for me. You obviously have an extensive knowledge of and experience with Access databases. I get the general idea but have a few questions.
    1.)Is the data corruption you are talking about due to errors creeping in through inconsistent data entry or is it plain software errors that actually alter and adulterate correctly entered data?
    2.) How does a key field become corrupt?
    3.) The sentence: " It is easy to break out simple data source tables that can be used to automate and control data entry. " implies there is another method of entering linked data other than the method I'm using presently. At the moment I am entering data into several related tables by putting it into a form with several embedded sub-forms.
    Is there some more efficient way?
    4.)"along with a stacked set of queries to load the data in sequential order. That is load and update the master records and then the dependent records. " Could you please explain this in a little more detail or perhaps even provide an example?
    5.)If you can't answer my rather demanding questions could you point me to a well written resource or on-line expert who can?
    Thanks for your ideas so far.

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

    Re: Are Relationships Necessary?

    The data corruption that is preventable through referential integrity is strictly of the orphan record or invalid key variety. Actual physical corruption of data is a whole different issue, and it's one that I have rarely encountered with Access. I generally avoid the use of memo fields, which may be the reason I so rarely see true data corruption.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Are Relationships Necessary?

    That's what I thought (re: data corruption).
    Gee Charlotte, there seems to be an amazingly different
    spectrum of opinion on this topic ranging from "relationships are essential" to "relationships aren't really needed at all and I haven't bothered with them"
    Doesn't this underscore my original remark that no one has clearly explained their true function and their pros AND cons?

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

    Re: Are Relationships Necessary?

    It also underscores the differences in programming styles and experiences among Loungers. Something that is absolutely critical in one application may be of negligible importance in another. In the Lounge, we get a mix of viewpoints and you're free to select what suits you the best.

    We all tend to develop our styles based on instruction and/or experience. If we don't encounter a problem, we tend to ignore possible solutions to it. That's human nature, and programmers are only human (with the possible exception of one or two I know who definitely aren't! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>).

    I'm a lazy programmer. I don't want to have to write functions to do things Access will do for me, so I use relationships and referential integrity and primary and unique keys. I also hate writing the same code over and over, so I write reusable code. They both take a little more effort up front to save me a lot of work down the line. For me, that's an acceptable investment of my time.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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