Relational Database Usefulness

[roystreet]

I have a question about Access database relationships that I've never really understood the benefit. I know we can go into database tools & select relationships to create one between 2 tables. I've never really put this function to use. When I create databases, when I need the data that is related between 2 tables, I usually use a query that I add the appropriate fields from those tables. At the query design I drag over the related field between those tables. When the query is run, it pulls the data from both that I want. I can retrieve data from those tables by that query by (basically) any criteria I needed.

What is the benefit in using the the "Relationships" function in Access over how I usually create relationships?

Thanks

 

[pbaldy]

If you set up relationships the tables will already be related when you add them to the query design grid. Also relationships let you set up referential integrity.

 

[stopher]

They have quite different purposes.

The purpose of relationships between tables is to enforce relational rules on the tables thus providing intergity to the data. Suppose you have two tables, Player and Team. You create a relationship between the two tables such that a team can have many players. In doing this you are enforcing that a players can only be members of valid teams i.e. only valid foreign key values. Without such a relationship, a player could be a member of a ficticious team i.e. a foreign key could be entered that doesn't have a corresponding primary key in team.

A "relationship" in a query is really just a join - a way of joining tables in a query so you can use information from both tables.

 

[roystreet]

stopher, in my case I usually use a type of "Generic" table that has all of the specific values that are available. So, say were to pick a team name, all of the team names available in that drop down menu would come from the generic table. That table could easily be updated by an admin, say from a "Updater" form only available to the admins. That way, no one ever needs to see the table. Or, the drop down menu could query all of the teams listed in "teams" table & restrict the available values to what is found in that table. And the query would show only 'distinct' values.

I often create a generics table that has fields that many different drop down menus, combos, etc can pull from. For example in a database the provides a log of correspondences. It allows a manager type user to create subject lines that would be available in a correspondence. Since many users could describe the same type of correspondence many different ways, like Aircraft Application & someone else may state App for New Aircraft the generics table would force users to only have specific subject lines available to them.

I hope I'm not confusing you.

 

[pbaldy]

Ah, the oft-debated "one lookup table to rule them all".

 

[roystreet]

If you set up relationships the tables will already be related when you add them to the query design grid. Also relationships let you set up referential integrity.

So, pbaldy, does this mean that I possibly won't have to make as many queries or use less coding would be the benefit? I think how I enforce some of the data integrity is a combination of mostly VB & queries. For example, when someone clicks on "Delete Item", it will run code that deletes from both tables that match the unique ID for that item using a delete query. Maybe I need to be more efficient in some ways.

 

[roystreet]

Ah, the oft-debated "one lookup table to rule them all".

HaHa!! I like your comment.
I take it that you don't prefer to use that method?

 

[pbaldy]

I don't think it would affect how many queries you have, it would make them a little easier to create (the join line(s) would already be there). From the referential integrity standpoint, I wouldn't say you'd want to get rid of your existing processes, more that it would be a backstop. For instance, if a user is on a form for customers, they could just hit the delete key and possibly delete a customer. Referential integrity would prevent that if there were invoices for that customer. Are there ways to stop use of the delete key? Sure, but RI protects you from the pesky user that finds a way around your processes, accidentally or on purpose.

 

[pbaldy]

HaHa!! I like your comment.
I take it that you don't prefer to use that method?

I don't use that method, but I wouldn't criticize it either. If it works for you, go for it. I learned to use individual tables, and I'm too old to change now.

I assume you don't own a taxi company (I've heard the name in that context)?

 

[roystreet]

Nope - I work in an office that deals with certifying modifications to aircraft & brand new aircraft designs. Much more interesting than a taxi company - No offense to the taxi cab drivers out there

 

[pbaldy]

More interesting than getting robbed, having people throw up in the car, etc? I think not.

 

[gemma-the-husky]

roy.

it's the RI ("Integrity") bit of a relation that is the important thing for me.

By preventing orphan records, you can have more confidence that you are processing all of your data correctly.

The fact that a single "notional" join can do all of this, is little short of incredible. The alternative is to specifically add code for inserts, deletions and amendments to double check that the "other half" of the integrity-join remains in situ.

 

[stopher]

Hi Roy

Yes you can role your own "integrity". However, as per your deletion example you have to write your own code to prevent orphaned players - or more worryingly players that are still members of an invalid team. As databases grow, you will find the need to do housekeeping on data that you perhaps hadn't planned or maybe you are re-designing. As the owner, you would likely choose to use queries to do this. This is where mistakes can be made e.g. you update some records forgetting the impact of other tables. This might seem a stupid mistake but is an easy one once your database grows. The ingrity covered by relational design ensures that any additions/edits/deletions do not cause the data to become inconsistent.

Consider the wider picture. Suppose someone else comes along to take over development of your database. Suppose they design a new form (for whatever reason). They have to know that they have to implement all the integrity rules into their form in order to prevent inconsistent data. Typically, databases are split at the very least into the front and back end i.e. the interface (front end) and table and relations (backend). If you think about a wider case where the backend could be accessed from multiple front ends (potentially of different systems), then the database designer (the person who designs the back end) has to ensure none of the front end designers can mess up the data i.e. make it inconsistent. Hence the requirement to implement relational design (and in fact as many integrity rules as possible) on the back end. A general principle is the back end is responsible for data integrity, the front end is responsible for user interface.

Do you need to do it? No, but it's damned good practice and if your table structure is well thought through, applying the relational lines is pretty easy. Once you grow beyond a couple of tables, the actual relational design itself will help visualise if you have deisgned correctly.

Good question by the way.

Chris

 

[gemma-the-husky]

just as an aside, this discussion also highlights the need to ensure there is no redundant data, or more importantly that the requirements of a business are fully understood.

let's say you have a system where you have sales areas, sales territories, and customers.

sales area 1 consists of sales territories A, B and C
sales area 2 consists of sales territories D, E and F

Now if you add a customer, and you store both the sales area, AND the sale territory you may find the customer record could end up being allocated to sales area 2, but also to territory B. This would not break the RI, but might give you conflicting data extracts.

The right way is not to store the area in the customer record, but only the sales territory, since the area can be determined from the territory.

This does not fully solve the problem, however. There are other considerations. In time, a sales territory may get re-allocated to a new area, and then you need to decide what you do with the sales history - does it "go with the territory" or "go with the area", or even both. How do you design the data structure to allow for change.

 

[ButtonMoon]

Roystreet,

Relationships in Access have nothing to do with relational databases. They are actually quite contrary to the spirit and intention of relational databases.

The one truly useful feature of relationships is to enforce referential integrity constraints (AKA foreign keys). A foreign key is a data integrity rule that is enforced between two tables. For example a foreign key between an invoice table and a customer table can be used to ensure that every invoice refers to a valid customer. It does this by preventing any new rows being inserted that would break the integrity rule and by preventing any updates or deletes that would break the rule. In a nutshell, referential integrity constraints are very important for ensuring your data is correct.

To implement referential integrity you need to check the Enforce Referential Integrity check box when you create a relationship. If you don't do that then you have a "relationship" but the integrity rule is not enforced. As you may have noticed, such relationships (without referential integrity) don't achieve very much.

 

[The_Doc_Man]

I'll give you a personal viewpoint on Access relationships and why they are good.

When you create queries, forms, or reports, you can often do so faster by use of a "wizard" - a bit of code that automatically builds things for you. Often, you find that the wizards appear to be dumber than a box of rocks.

Access is a Rapid Application Development (RAD) tool. If you pre-define distinct relationships to parent/child tables or lookup tables or whatever, the wizards suddenly "get smarter" because you have given them something to use to build your targeted object faster. I.e. they know another fact that often makes a huge difference in how they will build whatever they are building.

The relationships help ME too. When I see the relationship diagram and unravel the spaghetti, I am able to "see" how things fit together better because, let's face it, Man is a visually oriented animal. The relationship diagrams help in the visualization.

Now, another key feature - when declaring relationships, you can declare whether you do or do not want to enforce relational integrity that stops you from making bonehead blunders regarding creating phantoms or deleting valid records. (Trust me, I made all sorts of bonehead blunders before I learned how to prevent them.) It often saves you a lot of precautionary coding. You don't have to verify in your VBA code that you are avoiding a mistake because Access has code to do exactly that for you - as long as you remember to set the option in the relationship declaration.

In summary, what good is the relational part of Access? It's just another tool in the toolbox of things you might wish to do in a database applications suite. Nothing more, nothing less. But I'll also point out the old craftsman adage: If all you have in your toolbox is a hammer, everything gets treated like a nail. So by having more tools in the box, you can see the nails, nuts, bolts, and screws for what they really are.

 

[roystreet]

Hi Roy

Yes you can role your own "integrity". However, as per your deletion example you have to write your own code to prevent orphaned players - or more worryingly players that are still members of an invalid team. As databases grow, you will find the need to do housekeeping on data that you perhaps hadn't planned or maybe you are re-designing. As the owner, you would likely choose to use queries to do this. This is where mistakes can be made e.g. you update some records forgetting the impact of other tables. This might seem a stupid mistake but is an easy one once your database grows. The ingrity covered by relational design ensures that any additions/edits/deletions do not cause the data to become inconsistent.

Consider the wider picture. Suppose someone else comes along to take over development of your database. Suppose they design a new form (for whatever reason). They have to know that they have to implement all the integrity rules into their form in order to prevent inconsistent data. Typically, databases are split at the very least into the front and back end i.e. the interface (front end) and table and relations (backend). If you think about a wider case where the backend could be accessed from multiple front ends (potentially of different systems), then the database designer (the person who designs the back end) has to ensure none of the front end designers can mess up the data i.e. make it inconsistent. Hence the requirement to implement relational design (and in fact as many integrity rules as possible) on the back end. A general principle is the back end is responsible for data integrity, the front end is responsible for user interface.

Do you need to do it? No, but it's damned good practice and if your table structure is well thought through, applying the relational lines is pretty easy. Once you grow beyond a couple of tables, the actual relational design itself will help visualise if you have deisgned correctly.

Good question by the way.

Chris

Hey Chris,
I see what you mean concerning housekeeping, I've already built in integrity checks when a user opens certain forms or opens the database. They usually run a query behind the scenes that cleans up data if there's some issue. For example, there's a subject field that should have been populated & the query checks for a value, if it's null, then updates the field to reflect "No Subject Listed" I don't have it run all of the clean up code when it opens as to not use so much bandwidth & not effect the user experience, ie delays. As an admin, I also have a button that runs several clean up queries at once.

Now, pertaining to someone taking over the database, yes I totally agree they wouldn't know how it works behind the scenes. I have created an Admin manual that lays out how the system works. I describe even what code is run when you press a specific button & what it triggers. I lay out what the system checks for, what fields in the tables are required. I lay out the design flow of how the system works. Even some possible errors that could be generated. It is a very thorough manual, but even can be helpful for me as not all of it is in my head now. Though there is a lot in my head. Although recently I did run into a situation where the database was doing something right on a particular form, but I couldn't figure out at first how it was doing it correctly. (Was a while back that I designed/implemented that part) I was adding features & modifying the design of the system (Especially a newer GUI)

The system also has some on error codes that has a number combination on the title of the dialog box that can be used to tell what function in what form or module the failure occurred. The user could give me the number over the phone & I would know where to look in the system. What specific function in that form or module it failed. It's in the title bar of the dialog box so that it is kinda discrete & doesn't usually confuse the user since they don't really notice it until I probably ask them what it states.

 

[roystreet]

just as an aside, this discussion also highlights the need to ensure there is no redundant data, or more importantly that the requirements of a business are fully understood.

let's say you have a system where you have sales areas, sales territories, and customers.

sales area 1 consists of sales territories A, B and C
sales area 2 consists of sales territories D, E and F

Now if you add a customer, and you store both the sales area, AND the sale territory you may find the customer record could end up being allocated to sales area 2, but also to territory B. This would not break the RI, but might give you conflicting data extracts.

The right way is not to store the area in the customer record, but only the sales territory, since the area can be determined from the territory.

This does not fully solve the problem, however. There are other considerations. In time, a sales territory may get re-allocated to a new area, and then you need to decide what you do with the sales history - does it "go with the territory" or "go with the area", or even both. How do you design the data structure to allow for change.

The database that I have actually has 12 tables. The system mainly deals with projects & correspondences. Correspondences that relate with projects, but don't always. This allows a user to open the frm_ProjectEditor form & see in a list box all of the correspondence related to that project & can open them.

A master contacts table that both the projects & correspondences system draw data from, but the projects have their own contacts that it populates the data from the contacts table "tbl_ProjContact". That data stays stamped in stone because it needs the contact information from that time, it doesn't matter if it becomes invalid later.

I also have a "tbl_ObjectUsage" table that allows me to see how often users use specific forms & some of the buttons they use in it. It allows me to know what features are needed in the system. Say a form is never used, I could get rid of it & decrease the size of the database.

There are other tables that pertain to models, etc.

Projects, contacts, & models are usually related by unique numbers, always numerical. Which has a specific numbering convention. I don't use auto numbers, but VB that looks at the last number & adds one to it. Part of it because the numbers are required to have leading zeros, is always at least 5 digits. If a correspondence date is changed, it will reflect that in the project editor because it's not related to the correspondence date, but rather a system number that isn't visible to the user & created by the system.

I have had very few issues with data integrity throughout the use of the system. Now, I can see where an integrity issue could occur when the system crashes, but could that not also happen using the "Relationships" that we've been discussing anyway? Now, as an Admin, I have caused a data integrity issue because of a stupid thing I did which the system allowed me because it trust's what I put in it & caused a number issue. But a regular user couldn't do that.

 

[roystreet]

Now, I'm getting the feeling that I may have made more work for myself in how I've designed it, but I'm not sure just yet. I'm getting a little bit of how relationships could help, but thus far I don't see how I'd accomplish much of anything different than what I've accomplished. With the exceptions of possibly database size (Which effects speed), less work on my part, a more rigid control over integrity so that even I can't mess that up, and I think there was one more thing, but I can't remember - My brain just got stuck

Anyway, I have around 12 users now & a little over 1900 projects & over 4000 correspondence entries in the system. It runs fairly smoothly, almost smooth as butter, except when users connect remotely...But, they can have a hard time opening a word document sometimes remotely. So, you can see their connection is over all pretty slow sometimes. I really wouldn't fault the database in that case & I don't know if I had a user have a copy of the front end on their machine help that. Currently they connect to a front end on a network location.