Relationships & Primary Keys

[makku]

I have been using Access for quite some time and never used Relationships or Primary keys. I join tables/queries in the query design.

Does this mean that I am not using Access in the correct way?


regards,

 

[namliam]

Welcome to the forums...

Relationships are joins, no real need to use the relationship screen but for to pre-set the relationships/joins in the database. So that when you add 2 tables to a query the joins are automaticaly coppied from the Relationship window.

Primary Keys though are a vital part of any database. At the very least if you are making joins you have defined your primary keys, otherwize you cannot make a join.
Adding an official primary key to a column though will only enhance your database and I strongly suggest you start using it.

I hope that helps

Regards

Your Friendly Mailman

 

[WIS]

I have been using Access for quite some time and never used Relationships or Primary keys. I join tables/queries in the query design.

Does this mean that I am not using Access in the correct way?


regards,

Primary key (PK)- an index set to No Duplicates ie each value in the PK fld is unique, as you get when you use Autonumber for the fld.

Foreign key (FK) - an index set to Duplicates Allowed in another tbl which can be joined (relationship) to the PK tbl. Usually both PK and FK have the same name but they can be diff as long as the data type is the same in both.

This allows a 1:many relationship to be set. You can then go to the Join line in Relationships and set up Referential Integrity (RI). This means that you can't remove a PK record if there are corresponding FK records, (unless you select Cascade Deletions) and so don't end up with "orphaned" records. ie one or more records in the "many"(FK) tbl with no corresponding record in the 1 (PK) tbl.

 

[The_Doc_Man]

If you only bother to define the relationships in query design view, you are still using relationships. However, if you have a permanent relationship that is always in force when using two particular tables together, you save yourself some work and help Access to help you by pre-defining the relationships. Not to mention that you can then use various commercially available tools to document that relationship. And it is not a bad idea to capture the relationships in a single place for documentation purposes of the whole project.

Can you go forever without a formally defined relationship in the Relationship Pane? Very likely, yes. Should you? IMHO, no.

 

[namliam]

Can you go forever without a formally defined relationship in the Relationship Pane? Very likely, yes. Should you? IMHO, no.

I have build many a database without every using the Relationship screen. In a properly designed and documented database (rare I know) it is not needed.

It is a good tool, a good added incentive. But dont depend on it, because it can lead you astray as well.

 

[Rabbie]

I have build many a database without every using the Relationship screen. In a properly designed and documented database (rare I know) it is not needed.

It is a good tool, a good added incentive. But dont depend on it, because it can lead you astray as well.

As The_Doc_Man says the Relationship screen can usefully be used as part of the documentation. If the database is otherwise well designed and documented then I would agree that it is not needed but these relationships can be used to provide Relational Integrity of your data so I feel on balance it is a useful tool.

 

[WIS]

I have build many a database without every using the Relationship screen. In a properly designed and documented database (rare I know) it is not needed.

It is a good tool, a good added incentive. But dont depend on it, because it can lead you astray as well.

How do you stop users from "orphaning" records on the many side tbl.

 

[stopher]

I agree that if you are building a relational database in Access then setting PKs and relationships in the relationship view is pretty much a must. If you don't then you are really creating yourself a lot of work to enforce the integrity of your data yourself.

Of course not all databases are relational e.g. statistical data analysis , data warehouse/datamart. In such cases you may feel that integrity checks provided by PK's and relationships are unnessary.

Chris

 

[namliam]

How do you stop users from "orphaning" records on the many side tbl.

By
1) Not allowing users to touch your tables
2) Proper coding
3) Proper design
4) Delete users from existance (erm... )

Seriously, depending on Referential integrity to keep your database from creating orphins is not a good thing... If anything you shouldnt allow any of our M$ products to do any (automatic) thinking for you... Just consider the 02/04/2009 date thing, is this 2 April or 4 Feb?

A lot more things like this... Perhaps your database is DESIGNED to have orphins for what ever design reason??
Proper coding and proper design are far more important than using the relationship screen.

 

[stopher]

Seriously, depending on Referential integrity to keep your database from creating orphins is not a good thing...

I'm quite surprised by this comment. Are you saying that the Jet engine is flawed in its implementation of referential integrity? I would argue the opposite i.e. you should be implementing RI as Access intended by getting Jet to enforce. Unless you have evidence of this vunerability of course.

If anything you shouldnt allow any of our M$ products to do any (automatic) thinking for you... Just consider the 02/04/2009 date thing, is this 2 April or 4 Feb?

Sure, Access has its nuances. But I'm not sure that a date formating "bug" implies that RI in Jet is flawed and should not be used.[/QUOTE]

Chris

 

[namliam]

I'm quite surprised by this comment. Are you saying that the Jet engine is flawed in its implementation of referential integrity? I would argue the opposite i.e. you should be implementing RI as Access intended by getting Jet to enforce. Unless you have evidence of this vunerability of course.

No that is not what I am saying... I am saying your coding should not depend on it.... And users should never get to the point where they need to depend on it.

RI is nice and all, but getting the default messages and stuff...ugly.
If your design and code are good, no need to enforce RI.
RI is a nice backup to your coding maybe... but... should not be a core of your coding IMHO.

 

[gemma-the-husky]

By
02/04/2009 date thing, is this 2 April or 4 Feb?

2nd April of course. The American colonists got it wrong again, and caused a lot of problems for the old country

 

[wiklendt]

i have a question related to relationships and the relationship view in access.

i find that when i do my foreign key setup properly (i.e., not lookup values at table level, but just a number field and then make the data entry at form level) i have to set my relationships manually.

this is fine, however, i have this situation where i have added some 'audit' fields (DateCreaded, UserCreated, DateModified, UserModified) to all of my tables. The User field FKs are populated from the PK in tblUser (UserID) at form-level.

what is the appropriate way to associate these as a relationship for each table in the relationship view - do i have one tblUser on display and join up all UserCreated and UserModified fields to UserID in tblUser? or should i have many tblUser displayed in relationship view, each UserID PK individually connected to each UserCreated and UserModified FK fields?

 

[namliam]

what is the appropriate way to associate these as a relationship for each table in the relationship view - do i have one tblUser on display and join up all UserCreated and UserModified fields to UserID in tblUser? or should i have many tblUser displayed in relationship view, each UserID PK individually connected to each UserCreated and UserModified FK fields?

Opinions about this differ, but I am in the camp where you need an instance of tblUser per join. As in order to do a proper query with both User created and User Modified you need 2 instances of tblUser as well.

Your "audit" though only catches whom ever did the last change, the previous change is lost... If this is what you want fine, otherwize you need to rethink it.

 

[dbDamo]

I found this link useful when creating my audit trail - http://articles.techrepublic.com.com/5100-10878_11-6166807.html

 

[wiklendt]

Opinions about this differ, but I am in the camp where you need an instance of tblUser per join. As in order to do a proper query with both User created and User Modified you need 2 instances of tblUser as well.

Your "audit" though only catches whom ever did the last change, the previous change is lost... If this is what you want fine, otherwize you need to rethink it.

yeah, i'm not looking (yet) to capture every change, just the creation and the last modified. this in itself is already better than what the current system that department has.

what you are saying makes sense, and while i thought this would be the solution, i was hoping not, as it will look a little 'messy' - but necessary, i understand.

having said that, dbDamo's link shows a very neat way to capture all changes... i'll have to read it closer. and maybe look at a more complete solution than what i have (anything worth doing is worth doing right!)

thanks!

 

[gemma-the-husky]

one problem i do have is managing a distibuted database

lets say i set it up with relationships, but over time, i add a few extra tables, and extra fields, and need to change the relationships

now i can send the users a new front end

i tried to get a startup routine to automatically add new fields into the backend, but had some issues with that - i just coudnt get certain field properties set as i wanted them - so now i get the users (the designated data manager) to add fields/indexes directly

but i think users would struggle to maintain relationships, and i cant see a way to automate that - so i dont bother.

yes, i lose the benefits of enforced referential integrity, but i cant see an easy alternative

 

[namliam]

yes, i lose the benefits of enforced referential integrity, but i cant see an easy alternative

referential integrity should be enforced by code, not relationships.
the relationship screen as such IMHO is 'added benifit' over that which a normal database should do. It gives a simple quick overview of what is what when and where. I NEVER add referential integrity and enforce this with my coding instead, relying on access to do the "thinking"/work for you is good here or there, but overall a bad thing.
It should do what YOU as a developer tell it to, not what Access thinks is the "right thing at the time", depending on version, regional setting, checks or check marks, etc.

Also dealing with the error messages that access throws in any average situation is going to leave users confused and will need good documentation to work them thru the error. Instead custom errors should be self explaining and self enforcing. Allowing the user to move forward from the error screen without the need for looking into documentation.

 

[The_Doc_Man]

Mailman, RI should indeed be enforced by code. However, when you are in a Rapid Application Development environment, the answer to "rolling your own RI" is decidedly NO for version 1.

Instead, you design the basic functionality to validate the design concepts. You define relationships early on in the project because the sub-form and sub-report wizards READ those relationships to automatically link parent/child tables for you (or at least make a strong suggestion of what you should use for linkage.) If you have relationships defined for tables, your JOIN queries automatically know which JOIN to use for those tables.

When you are ready to get rid of the ugly Access messages (say, in version 2), THEN is when you go back to implement your own pre-update checking to verify maintenance of RI. Everything in its own time. But there is also this to consider - if your users can be educated on what Access is telling you when it pops up those ugly messages, you can build a product with RI protection in far less time if you let Access manage RI for you rather than trying to roll your own.

I am not sure why you are down on RI from a practical sense, because if there was any significant problem with RI, like maybe it doesn't work correctly, then all sorts of small-business financial types could sue the pants off of Microsoft. (Yeah, I know about the disclaimer and liability limitations in the EULA - but that's why you hire good lawyers.)

Per the OP's original question, the reasons you use relationships have to do with what doors that opens up for you within Access. RI is only one of the doors. Informing the wizards of how things are related? Another big benefit of explicit relationships.

 

[Galaxiom]

I agree that Referential Integrity should be enforced by form design and code but see no harm in setting it up as a backstop behind the code. It can protect the design against coding oversights.

I have even used RI with cascading edits and deletes. Surely this is potentially the worst possible case for Jet getting it wrong. Yet I have never experienced a record being wrongly deleted nor had an orphaned record left behind.

I expect that RI is essentially an automatic query little different to those we would use in code. And I dare say that Jet is particularly rigourous about such transactions.

I really don't see why namliam is so down on them. I would like to see an example of how a regional setting could lead to RI making a mistake that would not also occur in code.