Is it bad practice to only use joins?

[shabbaranks]

Hi,

Ive never got on with relationships (within Access - not the one with the wife)

Is it bad practice to link tables using joins rather than making relationships between those tables? I appreciate the basics of relationships, but providing that you can access the relevant infor using queries and joins - is it doing any harm?

Thanks

 

[mdlueck]

I suppose you are interested in graphical linkages between the tables in the table designer and JOIN statements in SQL queries?

Think of the graphical links between tables in the table designer as low level enforcement rules.

SQL queries still need to perform JOIN statements in order to gather from the various tables all of the information needed to carry out your SQL question of the database. Those lower level enforcement rules do not JOIN data together when SQL queries are being executed. Those are only for enforcement of what is considered valid data within the database.

 

[pr2-eugin]

The idea of having relationship, as similar to having one with wife is bonding them forever.. When you are about to use them in Queries the JOIN will be automated for you..

Relationships form the basis of any strong structure.. With relationship defined with Referential Integrity you do not have to worry about cleaning up.. i.e. if in case you delete the Parent, the child will also be deleted.. Updating some value will also reflect in child's records.. Makes life a lot more easier..

 

[ButtonMoon]

shabbaranks,
It is supposed to be a defining feature of relational databases that navigational structures - access paths between data elements - are not pre-defined in the database. Access's Jet/Ace model compromises that principle through the redundant and rather shoddy feature it calls "relationships". Referential integrity (AKA foreign key constraints) is certainly important and useful. "Relationships" absolutely not.

 

[gemma-the-husky]

shabbaranks,
It is supposed to be a defining feature of relational databases that navigational structures - access paths between data elements - are not pre-defined in the database. Access's Jet/Ace model compromises that principle through the redundant and rather shoddy feature it calls "relationships". Referential integrity (AKA foreign key constraints) is certainly important and useful. "Relationships" absolutely not.

but the way access defines RI IS by establishing a relationship, and defining that relationship as one where RI is mandated.

The thing about RI is that it prevents orphan records, and inadvertent deletions. All of that can be prevented with judicious testing - so it depends whether you want access to help you with that, or not.

other than that, if you define a relationship, then access automatically shows the relationship if you add the related tables to a query. you can make the join manually if you prefer, though. setting relationships is a useful tool I think.

 

[ButtonMoon]

@Buttonmoon,
A "relationship" is the same thing as a foreign key constraint in other RDBMS'.

Clearly it is not so because the line drawn in the UI and the defaults for joining tables may be present irrespective of foreign keys and because the foreign keys may be defined in a database with or without such "relationships". You have already stated as much.

The Access user interface actually seems to encourage the misconception that "relationships" expressed in its extremely poor quality, defective, proprietary, non-industry standard diagrams are somehow important in the design and implementation of a database. In many ways that has done a great disservice to inexperienced users of the product, evidence of which is visible every day in these forums. This is one of the main reasons why I advocate that Access is highly unsuitable as a tool for teaching and learning about databases. It should be avoided entirely by students and those not knowledgeable and experienced in database design.

 

[RainLover]

Clearly it is not so because the line drawn in the UI and the defaults for joining tables may be present irrespective of foreign keys and because the foreign keys may be defined in a database with or without such "relationships". You have already stated as much.

The Access user interface actually seems to encourage the misconception that "relationships" expressed in its extremely poor quality, defective, proprietary, non-industry standard diagrams are somehow important in the design and implementation of a database. In many ways that has done a great disservice to inexperienced users of the product, evidence of which is visible every day in these forums. This is one of the main reasons why I advocate that Access is highly unsuitable as a tool for teaching and learning about databases. It should be avoided entirely by students and those not knowledgeable and experienced in database design.

With such a distain of the product why do you use it and why do you spend your time helping other people to use it.

Microsoft wrote Access as an alternative to other products that were on the market at that time. If you want bigger and better there is always SQL.

For what it was intended Access is an amazing product.

 

[RainLover]

I forgot to answer the OP's question.

I would always use Relationships. It is faster and simplier than not using it,

Once I have my relationships sorted I print them out and pin them to the wall. I makes a great map, and reminds me of my original intention.