Why use relationships?

[MSherfey]

Why should you declare a relationship in relationship view when you can do it in the query?

Let me explain first. I don't enter any data into my tables. All my tables are dumps from a main database and I'm just slicing-n-dicing the data into more usable pieces. How many customers or x type did y? Etc... What I noticed is if I don't setup the relationship correctly in the relationship view, it directly impacts my query (duh). For that reason there are times I need to change the relationship type in the query itself. That got me to thinking. Why am I even risking a bad query by declaring a relationship type outside the query? If I ever forget to confirm the relationship in the query I'm screwed.

Why not just declare the relationship between the needed tables in the query and not in the relationship view?

Does this make sense?

 

[boblarson]

Does this make sense?

For your situation (not doing data entry) this makes fine sense if you wish. Setting up the relationships will just let you have auto joins when you create your queries, but you can delete those joins in your queries and make your own. You don't have to use the ones defined.

But, if you don't want them - for this purpose it is fine to not have them.

 

[Vassago]

Relationships are more important in complex systems where many tables must be joined often. For example, if you are setting up a database of orders by sites, locations, order numbers, etc, and all data is stored in separate tables as a one to many and you have to access the data quite often, then a relationship makes sense.

 

[Thinh]

the role of relationship is for data integrity. With relationship you get some nice feature such as cascading update and cascading delete. If you design your application in smart manner you wont need relationships, But it is recommend to set up relationships because it tells everyone how the data a related. In your case you are not concern with data integrity so it wont be important to you. but consider cascading delete if you delete data. use it with cautious.

 

[MSherfey]

Thank you, this makes me feel better. When I get into the actual maintenance of the data I'll look more into it, but for now I think I'll just stick to declaring the needed relationships in the queries themselves.

Thanks again!!

 

[Banana]

Whoa, whoa, I should make it clear that there's no such thing as "relationships in the queries"

Relationships and Joins are not the same thing, even though they may look similar and act on the same fields.

Think of it that way:

Relationship defines a constraint- e.g. you can't add a record in that table unless there's a matching key in the one-side table entered in that record's foreign key.

Join defines how we should search the table- e.g. we will compare the table using that column as our basis for comparison.


As you've already noticed, you can join a table on another table with a non-related column; they only need to be of similar data types.

Likewise, you can define relationship but never ever join those tables in a query.

As other posters alluded to, relationships is more important when we're doing a data entry or need to maintain data integrity, which is their main function. In your situations, it's not that important and you can get by just with joins.

Hope that helps.

 

[datAdrenaline]

My summary ... and possibly an important point ...

Relationships with Referential Integrity: control data going into the database (data integrity, as has been eluded to)

JOINS: Control how we get information OUT of the data (ie: how we view/present/extract the data)

Relationships WITH OUT Referential Integrity: To me are nothing more than a helper for Accesses AutoJOIN feature when creating queries ...

....

Much of what has been spoken about Relationships in this thread is spoken with an assumption/understanding that the Relationship is defined with Referential Integrity (RI for short), so I wanted to remind whoever happens to be reading that often developers will use the term "Relationships" and 99.99% of the time, the meaning is "Relationships with Referential Integrity"

 

[Banana]

On a bit of technical note- is it really fair to call Access's Relationship without RI a relationship? In other databases I know about, it is usually described as a constraint defined in either CREATE TABLE or ALTER TABLE. To prove this point, if we executed the following SQL statement in Access:

CREATE TABLE i (ID COUNTER);
CREATE TABLE t (tID COUNTER, ID LONG CONSTRAINT fki REFERENCES i (ID));

Will create a relationship with RI in Access and can be verified to be so by going to the Reference Windows and selecting the i and t tables.

I don't know of any parallels to Access's relationships without RI. Does anyone else?

 

[datAdrenaline]

>> On a bit of technical note- is it really fair to call Access's Relationship without RI a relationship? <<

Yes .. is it not only fair, but it is correct. A "Relationship", by definition, is used to show (or define) how column(s) in one table are linked to column(s) in another table. So a relationship simply defines a rule for your data. Another example of a rule is an INDEX.

Now that you have the rules for your data. A "Constraint" simply enforces the rules that you have defined. The constraints for a db can typically be categorized into 3 major parts: row integrity (ie: the PRIMARY KEY constraint); referential integrity (ie: the FOREIGN KEY constraint); and overall or database integrity (ie: CHECK constraints, UNIQUE constraints, DEFAULT constraints).

In Access, and other databases, a Relationship rule defined using the FOREIGN KEY constraint has a couple of options, meaning that you can enable the constraint (ie: enforce referential integrity) or not. In JET/ACE the DDL SQL language is not rich enough (If I recall correctly) to have to power to enable/disable the FOREIGN KEY constraint (note disable is not the same as delete!). In SQL Server, you can create a FK constraint with the syntax WITH NOCHECK.

So ultimately, it is my assertation, that a Relationship is a FOREIGN KEY constraint that is not enabled.

Despite the fact that the JET/ACE SQL language is not rich enough to disable a FOREIGN KEY constraint, the DAO object model is perfectly fine with creating such constraints....

Public Function fCreateRelationship_DAO(strRelationship As String _
                                        , strPrimaryTable As String _
                                        , strPrimaryField As String _
                                        , strChildTable As String _
                                        , strChildField As String _
                                        , lngDAORelationAttribute) As Byte
    
    Dim dbs As DAO.Database
    Dim rel As DAO.Relation
    
    Set dbs = CurrentDb
    
    Set rel = dbs.CreateRelation(strRelationship _
                                 , strPrimaryTable _
                                 , strChildTable _
                                 , lngDAORelationAttribute)
    
    rel.Fields.Append rel.CreateField(strPrimaryField)
    rel.Fields(strPrimaryField).ForeignName = strChildField
    
    dbs.Relations.Append rel
    dbs.Relations.Refresh
    
End Function

The value of lngDAORelationAttribute would be set to DAO.RelationAttributeEnum.dbRelationDontEnforce, which has a literal value of 2.

You're SQL statement, to me, does not prove your claim, it mere shows us how to implement an enabled FOREIGN KEY constraint, which is synonomous with a Relationship with enforced referential integrity, which is what you got.

... That's my take on this at 3:30 am! ... Now its off to bed my friend! ....

 

[MSherfey]

You guys had me at "Don't worry about them for your type of data. Use Joins."

Now I'm lost

 

[Banana]

You guys had me at "Don't worry about them for your type of data. Use Joins."

Now I'm lost

I think this may be in jest but should mention that when I said earlier-

On a bit of technical note-

It should have been re-written to say:

"I would like to hijack the thread with a tangential question that has no bearing whatsoever with OP's question."

Mind you, nothing has changed between then and now, namely that you'll be fine just doing joins and not defining relationships for your specific situation. In case where we may do data entry, relationship (with RI!) is valuable in ensure that we don't place our database in an inconsistent state, with orphaned records and the likes.

My question basically had to do with the definition of the word 'relationship' and whether it was the 'standard' as such. We have SQL standard, usually handed down by ANSI, which is theoretically includes the definition of what we can do and can't do with SQL, generally speaking. But I say 'theoretically' because there is no single RDBMS vendor that adhere to the SQL standard exactly, usually for two reasons; 1) they want to provide extensions to support their signature features not available in other RDBMS and 2) SQL standard, if you actually can read the document from ANSI (great for insomnia, so I hear), can be open to interpretation and thus they've implemented as such. So the crux of my question was basically 'Does Access's allowing us to define relationship without referential integrity derive from the standard as recognized by several other vendors and programmers?'

You see, that wasn't first time that I said "Don't confuse relationships with joins. Relationship constraints and Join extracts." while datAdrenaline leaps in and point out that "Mind that when Banana say relationships, he means relationship with RI" (and he's right). So not only I had a bit of deja vu, but start wondering about how come experienced developers has come to expect relationships as one with RI enforced (and thus assume as such), which only confuses the novices, because of the unspoken assumption prevalent that when we have a relationship defined, RI is presumably the part of the package.

Anyway, I hope that clears things up and I should apologize for hijacking the thread.

 

[Banana]

Yes .. is it not only fair, but it is correct. A "Relationship", by definition, is used to show (or define) how column(s) in one table are linked to column(s) in another table. So a relationship simply defines a rule for your data. Another example of a rule is an INDEX.

To be honest, I do kind of have an issue with that. I think, "What good is a rule that can't be enforced?" Seems pretty much toothless. But I should also acknowledge that my puny little opinion doesn't really make the definition any more or less correct...

In Access, and other databases, a Relationship rule defined using the FOREIGN KEY constraint has a couple of options, meaning that you can enable the constraint (ie: enforce referential integrity) or not. In JET/ACE the DDL SQL language is not rich enough (If I recall correctly) to have to power to enable/disable the FOREIGN KEY constraint (note disable is not the same as delete!). In SQL Server, you can create a FK constraint with the syntax WITH NOCHECK.

I also checked with MySQL and found that it does not support such option, BUT the MySQL documentations does indeed make references to other databases supporting 'deferred check', which lead me to suppose that while 99% of time relationship would be defined with FOREIGN KEY constraints, it's not unheard of to have a constraint disabled or deferred, something I didn't know until just now.

**I should hasten to add for benefits of others- Microsoft doesn't recommend using DDL statements upon Jet, and you're correct that Jet's DDL statements aren't as rich and featured compared to DAO. I suppose my point of showing the DDL statement was in reference to ANSI SQL standard, and over the keyword 'CONSTRAINT' being used to define a relationship.

Anyway, thanks for sharing about that WITH NOCHECK option! I hope it's safe to say that 'most of time relationships are constraints though there are cases where we may temporarily disable the constraints' (a prime example being doing bulk inserts which is doable with constraints but in a complex system, it could be much more pain in ass and require too much time and effort *especially* if the data's origin comes from another database that already enforces the constraints. Flat file inputs, OTOH, may require some sanitizing...)

 

[datAdrenaline]

Hey Banana! ...

My quote:
"So a relationship simply defines a rule for your data.."

"Data" does not mean the fields values in that statement its meant to be the "data" as a whole, as in the structure of the framework in which the data values are stored. Just like an INDEX really has nothing to due with the data values, just the way they are ordered. Then we follow up that overall stuff with CONSTRAINTS to ensure that our database concepts are adhered to at the field level.

I personally don't create a Relationship unless I intend to enforce referential integrity! ..

...

>> Microsoft doesn't recommend using DDL statements upon Jet, <<

So ... where did you here that, I have never heard of, or read about such a recommendation. Not that I am the 'All Knowing' of Access/JET|ACE, but I would think something like that would sorta "get around" ...

 

[Banana]

So ... where did you here that, I have never heard of, or read about such a recommendation. Not that I am the 'All Knowing' of Access/JET|ACE, but I would think something like that would sorta "get around" ...

Hmm now I'm questioning my sanity. I thought it was in every help file. From 2007 help files:

The Microsoft Access database engine does not support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Microsoft Access databases. Use the DAO Create methods instead.

I looked in 2003 just in case to see if they were different- I only found that the information on DDL statements were very sparse- there were no files specifically for CREATE TABLE, ALTER TABLE and so such, but nothing that support my earlier assertion.

I was pretty sure that I saw that statement when I was reading a help file or possibly a KB article detailing the DDL statement and it came with the note that Microsoft recommend using DAO over DDL, which made to me and didn't take too careful note of it.

Sorry, but I'm afraid it's just a ghost after all.

 

[datAdrenaline]

>> non-Microsoft Access Databases <<

The "non" seems to become invisible with the lead in of "does not suppot" ... I had to read it twice to make sure my brain absorbed it...

SQL help ...
JET SQL Reference
ACE SQL Reference

The are both probably the same information ... but I thought I would post them both.