If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Setting Table Relationships- Why?
When I first started working in Access, I would set
relationships for all the tables. THen when I got "clever" and began writing lots of queries, I noticed that I would usually need to tweak the pre-set relationships depending on the query. For example, I might change the type of join from what I'd set in the Relationships view. ANd in some cases, a table might link to two other tables so I would just not set the relationship at all until I wrote a query involving the related tables. SO... What is the purpose of setting relationships in the relationships view of the tables? Is it just a conveinient way of setting the default so the join lines will show up automatically when working in QUery by Design? ALthough I still link the tables, I usually make the links all one-to-one, then adjust them as required by individual queries. Should I be doing something differently? |
#2
|
|||
|
|||
To me, the major reason for relationships is to have the database enforce
referential integrity. I seldom use them for any other reason. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "el zorro" wrote in message ... When I first started working in Access, I would set relationships for all the tables. THen when I got "clever" and began writing lots of queries, I noticed that I would usually need to tweak the pre-set relationships depending on the query. For example, I might change the type of join from what I'd set in the Relationships view. ANd in some cases, a table might link to two other tables so I would just not set the relationship at all until I wrote a query involving the related tables. SO... What is the purpose of setting relationships in the relationships view of the tables? Is it just a conveinient way of setting the default so the join lines will show up automatically when working in QUery by Design? ALthough I still link the tables, I usually make the links all one-to-one, then adjust them as required by individual queries. Should I be doing something differently? |
#3
|
|||
|
|||
On Fri, 5 Nov 2004 16:04:29 -0800, "el zorro"
wrote: When I first started working in Access, I would set relationships for all the tables. THen when I got "clever" and began writing lots of queries, I noticed that I would usually need to tweak the pre-set relationships depending on the query. For example, I might change the type of join from what I'd set in the Relationships view. ANd in some cases, a table might link to two other tables so I would just not set the relationship at all until I wrote a query involving the related tables. Ummm... that's not the case. You can create a Query and set the relationships there; it is not necessary to define the relationships in the Relationships window first. And there is nothing to stop you from creating relationships to two - or any number - of other tables in the relationships window; it's not necessary to create a Query first. SO... What is the purpose of setting relationships in the relationships view of the tables? Is it just a conveinient way of setting the default so the join lines will show up automatically when working in QUery by Design? That's just a side effect. The main purpose of relationships is to enforce relational integrity, so that you cannot (for instance) put a record in a ClassEnrollment table for a nonexistant student or a nonexistant class. ALthough I still link the tables, I usually make the links all one-to-one, then adjust them as required by individual queries. Should I be doing something differently? One to one relationships are VERY VERY RARE, and usually wrong. If you're not using "Subclassing", or using one to one relationships to provide field-level security, you *are* doing something wrong. If you have a one to one relationship between two tables, with the exception of the special cases above, you're better off just having one table with more fields - *if* the tables are properly normalized. Step back a bit and think about the concepts of "database normalization". If the term isn't familiar - read about it. Each Table should represent a particular class of Entities - real-life persons, things, or events; Entities have real-life Relationships. To continue the school enrollment example, a Student is a type of entity (with attributes - fields - such as LastName, FirstName, DateOfBirth, etc.); a Class is another entity; since each student can enroll in multiple classes, an Enrollment is another type of entity, with the Student entity and the Class entity related one-to-many to the Enrollment entity. Perhaps you could post a description of the entities important to your application. My guess is that you SHOULD have relationships, all of them one-to-many. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#4
|
|||
|
|||
Thanks for your response. I understand what you're saying
about one-to-one relationships. I'm just wondering about the feature of defining the table relationships in the table layout view. I'm not enforcing referential integrity, but I felt that I should probably define SOME kind of relationship in the Relationships window (not sure why)so I made them all one-to-one. Then I define the type of join I actually want when I write the query (such as one to many). And, depending on what I want to look at, the join between two tables may change from query to query. Maybe I should set the joins in the Relationships view to the type I would normally use-- or does it make any difference? -----Original Message----- On Fri, 5 Nov 2004 16:04:29 -0800, "el zorro" wrote: When I first started working in Access, I would set relationships for all the tables. THen when I got "clever" and began writing lots of queries, I noticed that I would usually need to tweak the pre-set relationships depending on the query. For example, I might change the type of join from what I'd set in the Relationships view. ANd in some cases, a table might link to two other tables so I would just not set the relationship at all until I wrote a query involving the related tables. Ummm... that's not the case. You can create a Query and set the relationships there; it is not necessary to define the relationships in the Relationships window first. And there is nothing to stop you from creating relationships to two - or any number - of other tables in the relationships window; it's not necessary to create a Query first. SO... What is the purpose of setting relationships in the relationships view of the tables? Is it just a conveinient way of setting the default so the join lines will show up automatically when working in QUery by Design? That's just a side effect. The main purpose of relationships is to enforce relational integrity, so that you cannot (for instance) put a record in a ClassEnrollment table for a nonexistant student or a nonexistant class. ALthough I still link the tables, I usually make the links all one-to-one, then adjust them as required by individual queries. Should I be doing something differently? One to one relationships are VERY VERY RARE, and usually wrong. If you're not using "Subclassing", or using one to one relationships to provide field-level security, you *are* doing something wrong. If you have a one to one relationship between two tables, with the exception of the special cases above, you're better off just having one table with more fields - *if* the tables are properly normalized. Step back a bit and think about the concepts of "database normalization". If the term isn't familiar - read about it. Each Table should represent a particular class of Entities - real- life persons, things, or events; Entities have real-life Relationships. To continue the school enrollment example, a Student is a type of entity (with attributes - fields - such as LastName, FirstName, DateOfBirth, etc.); a Class is another entity; since each student can enroll in multiple classes, an Enrollment is another type of entity, with the Student entity and the Class entity related one-to-many to the Enrollment entity. Perhaps you could post a description of the entities important to your application. My guess is that you SHOULD have relationships, all of them one-to-many. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps . |
#5
|
|||
|
|||
On Mon, 8 Nov 2004 10:27:15 -0800, "el zorro"
wrote: Thanks for your response. I understand what you're saying about one-to-one relationships. I'm just wondering about the feature of defining the table relationships in the table layout view. I'm not enforcing referential integrity, Then you're not creating relationships, you're not protecting your data, and you're misusing Access. All that an unenforced relationship provides is an overridable default relationship type for new Queries. If by "defining table relationships in the table layout view" you mean using the yuck, PTOOIE! Lookup Wizard... DON'T. See http://www.mvps.org/access/lookupfields.htm for a critique of this misfeature. but I felt that I should probably define SOME kind of relationship in the Relationships window (not sure why)so I made them all one-to-one. If you're defining Lookups, and also defining relationships in the relationships window, then Access will create two redundant relationships, and extra redundant indexes; these will bloat your database and slow performance. JUST use the relationships window, and enforce relational integrity - LOGICALLY. If - in real life - there is a one to many relationship, create a Primary Key in each table, and a Foreign Key field in the "many" side table, linked to the Primary Key of the "one" side table. Join the Primary Key to the Foreign Key in the relationships window, and check Enforce Relational Integrity. Access will correctly (and automatically) define the relationship as one to many, since there can only be one record with that Primary Key, and (unless you've specified a unique index on the foreign key field) many records in the other table. Then I define the type of join I actually want when I write the query (such as one to many). And, depending on what I want to look at, the join between two tables may change from query to query. Maybe I should set the joins in the Relationships view to the type I would normally use-- or does it make any difference? Again... the feature whereby the relationship window sets the default join type is a handy minor spinoff. That's NOT the reason that relationships exist; they exist to protect the integrity of your data. If you don't care about the integrity of your data, that's ok by me... but don't ask me to use your database, or invest in any business that relies upon it! John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access & OleDb - generating schema changes, problem with identity/counter fields. | Thomas Tomiczek [MVP] | Database Design | 9 | November 5th, 2004 10:32 AM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
"automatically resize to fit contents" grayed out | T | Tables | 5 | July 26th, 2004 12:44 PM |
Help with table design and relationships | Richard Wright | Database Design | 3 | June 20th, 2004 03:49 PM |
Cannot join 1:M table into M:M tables | Tom | Database Design | 4 | May 19th, 2004 10:16 PM |