Is Learning about relationships in Access necessary?

[khodor]

Hello all, I created an Access file to set student information and I made the first table for basic student information and another table for the time forms that the student sends to me daily, and in this table I made the student’s name (number) and it is taken from the first table by lookup wizard, so Access built an automatic relationship, do I need to know more about Access relationships or that is enough?

 

[theDBguy]

Hi. Access may try to help you to get the job done right; but sometimes, it could try to help too much that the result ends up being wrong. So, yes, I would say you should learn about proper relationships, so you can at least make sure what you end up with is correct.

 

[isladogs]

Hello all, I created an Access file to set student information and I made the first table for basic student information and another table for the time forms that the student sends to me daily, and in this table I made the student’s name (number) and it is taken from the first table by lookup wizard, so Access built an automatic relationship, do I need to know more about Access relationships or that is enough?

I think you are referring to having a lookup field in your table. This will look like a combobox in that table field.
If so, be aware that this is widely considered to be a bad idea. See this article to understand why: Table Lookup Fields - Mendip Data Systems

I would also recommend you understand the purpose of relationships. They are not the same as query joins.
The main reason for setting relationships is so you can impose referential integrity. See Relationships- Mendip Data Systems

 

[khodor]

Hi theDBguy,
Thanks for you help, I will learn about it.

Hi isladogs,
Thanks for your Help.
I have read the two articles completely and the good news is that I was working on Excel and from a short time I moved to work on Access and I did not transfer data yet to Access.
To be honest, I was very comfortable working on Excel, but recently I faced many problems because excel became very slow and closes suddenly, so for these reasons I had To transfer the work to excel
Thank you so much, I have benefited a lot from reading these articles and understood the problem with what I was doing.

 

[khodor]

The reason that made me ask my question is that when I started learning on Access, I downloaded a ready-made file from the Access website about student data, and I did not see any constructed relationships in it, so I thought it was possible to Ignore it.

 

[isladogs]

The MS templates vary significantly in quality and, unfortunately, several include examples of very poor practice. That is partly because they are often designed mainly to show off Access features including such horrors as lookup fields in tables, multivalued fields and attachment fields. All best avoided.

If you want to look at some more examples of databases for schools, I also have a number of commercial schools apps on my website.
Free evaluation versions are available if you just want some ideas.
Note that in the case of split databases such as those on my site, the relationships will be in the backend file

 

[The_Doc_Man]

The question is, do you really NEED relationships? The answer is that you HAVE them whether you realize it or not. Access relationships just help to formalize matters. But if you have a WHERE clause that suggests that you want records from table A that match records from table B, then you are exploiting something you know that relates the two tables.

Relationships help you in that if you have declared relationships, Access can use the declarations to make the form, query, and control wizards seem "smarter." Do you need them? NO. But once you learn about them and start exploiting them, you will ask yourself how you ever lived without them.

 

[isladogs]

Nobody needs relationships just for automatically adding query joins etc.
To be clear, as already stated, their primary purpose is to enforce referential integrity

 

[The_Doc_Man]

My point, Colin, was that it sure was EASIER if you have relationships declared. I did say you didn't need them. But of course, if you want to enforce relational integrity, they are a sine qua non. You are indeed right about their need in that case.

 

[Isaac]

I will say that you can probably avoid the Relationships issue for some time and be OK.

I have created quite a number of non-trivial Access databases without paying any attention to the Relationships window whatsoever.
Of course, to handle integrity, I did so on the form level if needed - but I rarely if ever allow Deletes in the first place, much less a Parent delete.

Edit: I should add something. I am aware that, to many new developers (and maybe even to some expert developers), the Relationships feature is a chance, a way, a method whereby they.....mentally digest & even develop their concepts of the normalization of the database. It's where they prove the normalization level to be appropriate and sufficient. And that's totally fine. It's just that sometimes new developers get confused and seem to think that Relationships tool is as necessary as normalizing your data structure is. Because some people use them as a way of visualizing and proving their good data structure it gets confused as being part of that - but really it could have been a word document or a napkin, as long as it's done right. I say that, of course, without mention of referential integrity purposes--because I already commented on that, above.

 

[Uncle Gizmo]

There was a discussion on Relationships a few years back and deportas made some excellent observations. It is well worth revisiting this thread...

MS Access your Data with Sub-Forms

MS Access is such a brilliant tool, so easy to use, so powerful that I sometimes think people miss its obvious simplicity, the simple ability that makes it so powerful. I’m not talking about Relationships Many people get bogged down in the relationship issue, working out the relationships early...

www.access-programmers.co.uk

 

[Isaac]

deportas made some excellent observations

So did Mr. Gizmo!
That's a great article (seriously), thanks for linking.

 

[Isaac]

I wouldn't encourage people to do something you know to be poor practice. It is always easier to to it right than it is to do it twice.

And I strongly disagree with Uncle's assessment that Relationships are not necessary. They are even more necessary for newbe's than for experienced people. At least experienced people know enough to enforce rules in code. But, the problem with enforcing rules in code that the db engine can enforce for you is that developers who come after you might not realize what you have done. So even if you are totally conscientious about doing it "right", and never forget to enforce rules, your rules won't ever be automatically enforced in new forms or queries built by others. Also, doing it in code always means that you may have to do the same thing in multiple places and we know how that works.

I don't know it to be poor practice, and apparently neither do quite a few other people. I don't mind you disagreeing, but please don't tell me what to or not say in my own posts. I see more confusion from people trying to use a window that they get no benefit from than I do see people benefitting from referential integrity, and I don't allow people to delete parent records in the first place. Note that I'm not going to say "neither should you", I am only going to speak for myself.

 

[isladogs]

I agree with @Pat Hartman on this.
I could create databases with table level lookup fields, MVFs & attachment fields and for some time I wouldn't have problems.
However once those problems became an issue it would take a lot of effort to fix. Much longer than avoiding the issues in the first place

Similarly with relationships. If these are implemented from the start it will save a lot of time & effort in the long run
Working around this as described above is illogical and will take longer to implement and a lot more code

 

[The_Doc_Man]

Pat, I use relationships constructively and want them to be present. So I agree they are "good practice" actions. BUT for newbies who don't yet fully understand normalization, they are a deep, dark hole that frightens them - and Access works reasonably well without explicit relationships for those folks who have relatively simple situations. Granted, the more complex the problem, the more likely it will be that you really DO need relationships., and thus I definitely favor having them. But until you learn WHY you want to normalize your DB, you won't grasp relationships completely. I wouldn't bear down too hard on Isaac because I remember a time (dimly) when I didn't bother with relationships that much either. OR I created spot relationships in the upper portion of the query design grid. Not system-wide, but localized relationships. And Access is powerful enough to work on that basis until you DO eventually learn better.

Not all of us have databases that require Boyce-Codd normalization or 5th-normal form normalization. Not all of us have to manage 15-way JOINs (though the Navy DID have that with their personnel system).

 

[isladogs]

I'm sorry but I have to disagree...again
1. You cannot create relationships in the query design grid. You create joins.
There is no such thing as a 'spot' or 'localised' relationship, A join is not the same as a relationship
2. When I first started working with databases, I also knew nothing about normalisation, relationships or referential integrity.
After a while, when issues occurred I had to backtrack and undo a lot of the database structure to fix issues that had become intractable as the database became more complex.

It is far better to build a sound foundation at the start than have to go back and deal with the structure later.
That doesn't mean having to understand all the levels of normalisation as a theoretical construct.

 

[Galaxiom]

And I strongly disagree with Uncle's assessment that Relationships are not necessary.

One hundred percent agree. Like unique indexes they are tools that ensure the integrity of the data,

While forms can have all kinds of facilities to prevent bad data being accepted, best practice is to always protect the database from storing incoherent data right down at the table level. Data can be inserted and updated directly to the table by people trying to restore things or make corrections. However competent they may be, anyone can make a mistake.

So I agree they are "good practice" actions. BUT for newbies who don't yet fully understand normalization, they are a deep, dark hole that frightens them - and Access works reasonably well without explicit relationships for those folks who have relatively simple situations.

All the more reason to understand them. Fear is the primary enemy of all learning. The more something frightens you the more it should be tamed through gaining knowledge. Ignore stuff like that at one's peril.

2. When I first started working with databases, I also knew nothing about normalisation, relationships or referential integrity.
After a while, when issues occurred I had to backtrack and undo a lot of the database structure to fix issues that had become intractable as the database became more complex.

It is far better to build a sound foundation at the start than have to go back and deal with the structure later.
That doesn't mean having to understand all the levels of normalisation as a theoretical construct.

Exactly. (My emphasis).

 

[Isaac]

It could be that I've put the Relationships to limited use because I don't allow records to be deleted almost ever, I'm willing to acknowledge that it might be different if you do. My main point was that a) it's been 15+ years and I'm still OK without them, and b) they seem to serve more harm than good when people are new. But you're right - of course - with enough learning, everything beneficial is beneficial...if it actually is.

 

[Isaac]

Pat, I use relationships constructively and want them to be present. So I agree they are "good practice" actions. BUT for newbies who don't yet fully understand normalization, they are a deep, dark hole that frightens them - and Access works reasonably well without explicit relationships for those folks who have relatively simple situations. Granted, the more complex the problem, the more likely it will be that you really DO need relationships., and thus I definitely favor having them. But until you learn WHY you want to normalize your DB, you won't grasp relationships completely. I wouldn't bear down too hard on Isaac because I remember a time (dimly) when I didn't bother with relationships that much either. OR I created spot relationships in the upper portion of the query design grid. Not system-wide, but localized relationships. And Access is powerful enough to work on that basis until you DO eventually learn better.

Not all of us have databases that require Boyce-Codd normalization or 5th-normal form normalization. Not all of us have to manage 15-way JOINs (though the Navy DID have that with their personnel system).

I don't even think it has that much to do with complex vs. not complex. If you feel you will benefit from cascade deletes (etc), then go for it. If not, it's a nice visual tool to show others how your database works. And that's it.

 

[The_Doc_Man]

Well, there is more to it than cascade deletes. RI ALSO prevents you from creating a fatherless child record. But if you program that so that (a) you always create records through a form and (b) you always create them in a way to assure parentage, then things will work OK anyway. UNTIL you try to do something that involves a parent/child linkage.

For those people who use Access to manage lists, maybe there AREN'T any such relationships. However, when a newbie graduates to a true case of parent/child data, then is when relationships start to shine brightly. But you have to remember that I'm a pragmatist. When it comes to having relationships, you don't need them until you need them. (No, not intended as a tautology.)

@khodor - as you can tell, some of us feel more strongly on the subject than others. But then, there is that old rule of scientists: You can take all of the experts in the world for a given subject and lay them end to end but they still aren't guaranteed to reach a conclusion.