Re: Relationships. Does anyone use them?

salad wrote:

If I have EmpId in Employees, and EmpID in table Orders, if I add those 2 tables in the QB, a relationship line is automatically drawn. If it does that, why do I need to add 2 tables in the Relationship window and set relationships? What do I care if I didn't go in into the relationships window to set the relationship....for the most part its always done for me.

Salad, with all due respect,

You're still not clear on what relationships in Jet are. Yes, it helps with query design just as the intelligence of the query design window (see note) "detects" the fields to be linked if they have the same name.

But that IS NOT WHAT RELATIONSHIPS ARE!

If you do not care to "go in into the relationships window to set the relationship" you are, quite simply, going to fail badly any relational database course. It sounds as if you have fallen into the sort of trap ironically caused by the cushion provided by the excellent database design tools in Access.

Relationships are NOT for providing an easy way to set up queries. Relationships are the constraints that are needed to be set up to ensure database structural integrity.

Ensuring integrity of database structure through coding only is again grounds for failing database 101 and I've personally fired a designer for doing this sort of thing in a critical application. When a database application gets large and widely used, the following is an example of what happens that simply screams for the lowest level constraints to be used.

Frequently, the data in a successful application may need to be tweaked from outside the application. This could be because of corporate changes, or, commonly, a transaction has been mistakenly completed with the wrong debit account codes and incorrect credit account codes. The application forms might not let posted transactions (I'm talking about G.A.A.P. stuff here) be changed once posted. Thus, when the mistake is detected, an administrator has to go in through the back door, ie, not through the application, through the tables themselves, and apply insert, delete, and/or update statements to correct the data.

Note that when I'm talking about an admin here, I'm not talking about the original developer or a normal user. I'm talking about someone who has inherited the application and data for looking after it. This happens when you sell your app to more than one customer.

Getting back to the admin changing data: This can be a very dangerous activity when there are DBAs who are several times removed from the original developer. Constraints will help such DBAs learn about the app and help prevent disastrous things happening.

A good database designer sets his application up so that vital constraints are at the database level.

This is what the relationship window is about. The setting up of queries is NOT RELATIONSHIPS. It's a bit of chrome that helps, but it's about constraining your data.

I guess the graphical view of relationships is unnecessary for me. It may be a necessity for others like you.

Again, you have completely missed the purpose of relationships. They are necessary for all database designs, whether or not you prefer to have a graphical view (the Jet approach) or have to remember them or look at them in text (Oracle as shown in Enterprise Schema Manager).

This last sentence shows you have completely missed what relationships/constraints are about. You really need to go over this in detail, because right now I can tell you, no offence intended and with the greatest of respect, that you are NOT DOING RELATIONAL DATABASE DESIGN WORK at present.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
.

Relevant Pages

  • Re: Relationships. Does anyone use them?
    ... If you do not care to "go in into the relationships window to set the relationship" you are, quite simply, going to fail badly any relational database course. ... Relationships are the constraints that are needed to be set up to ensure database structural integrity. ... You really need to go over this in detail, because right now I can tell you, no offence intended and with the greatest of respect, that you are NOT DOING RELATIONAL DATABASE DESIGN WORK at present. ...
    (comp.databases.ms-access)
  • Re: Table design - reducing number of entities
    ... I've looked at a lot of crappy code written against databases. ... lot of that code is crappy, because the database design itself was crappy. ... > look at all the constraints on the putative entities--not forgetting the ...
    (comp.databases.theory)
  • Re: Basic Database Design
    ... the Client has a Insurance Company ... you'll need some further knowledge on the subject of database design. ... Books: General: Beginner ...
    (microsoft.public.access.tablesdbdesign)
  • Re: speed
    ... > Hi Nicolaas ... > To make a long story short, a well designed database is a good foundation for proper application that helps the users to maintain ... So learning about database design may in time bring you more then 'just' statisfied end ... list of about 15 facilities. ...
    (alt.php)
  • Re: A real world example
    ... If a constraint is defined in terms of successive states of a database, ... Yes, that's all facts are, though constraints mean that your facts are ... value of a candidate key determines the values of all other attributes, ... The frame of reference for a candidate key is a ...
    (comp.databases.theory)
Quantcast