Table Relationships (1 Viewer)

[CelevGadol]

Hi everyone
I have been looking at an online course. It looks pretty good and I'm learning a lot from it and hopefully I will be able to create the database that I want that does all the things that I want it to so in that sense it's great.
There's a demo database all ready to use with tables containing data which is used throughout the course. When I first started looking at databases a couple of years ago, I built a database which does a lot of what I need based on tables and their relationships with each other. When I go to the database tools and look at the relationships there are lines all over the page between the tables. I thought that this was the whole point of a database. However, when I look at the demo database relationships, there's one link between two tables and it has an arrow at each end rather than the 1 to many links that my database has. Is it common practice to not link tables? I'm still going through the course so I haven't really covered anything particularly advanced yet but I get the idea that the table relationships are actually done through the queries and reports rather than between the actual tables themselves.
If this is the case, what's the point of having the relationship capability and is there any advantage to not having these relationships in place?

 

[gemma-the-husky]

Relationships are good to maintain "relational integrity", so you can't have orphan data, and you cant delete records that would leave orphan data.
They can also speed up development by building the links for you in queries.

The dbs will still work without them, so to some extent, it's a matter of taste.

 

[The_Doc_Man]

I will answer this the best way I can.

Access is a developer tool for Rapid Application Development. You are correct that what really counts when using the database is the content of the tables, the structure of the queries, and the layout of the reports. HOWEVER, Access is working with you in order to BUILD those things. When you define formal relationships among your tables using Access, it is possible for the query, form, and report builders to see those relationships and automatically define things in a way that exploits the extra information.

It is, indeed, possible to manually build queries using SQL via a text editor and then just copy/paste that into Access. You CAN manually build forms without recourse to relationships. You CAN build reports tediously with manual SQL and a bunch of settings for filtration, ordering, etc. But why would you NOT take advantage of something that can help you.

Let's be honest here. Access is often dumber than a box of rocks - but it DOES know how to put together a good scaffold for you with its wizards. You can then go back and customize what it builds for you. But with the extra information of the pre-defined formal relationships, you get the ability to tell Access to apply knowledge of the relationships.

There is one other issue... Access can enforce something called Relational Integrity. In simple terms, this is how Access prevents creation of orphaned records that should - but do not - have a "parent" record. Can't do that unless you have declared the relation first.

 

[CJ_London]

However, when I look at the demo database relationships, there's one link between two tables and it has an arrow at each end rather than the 1 to many links that my database has.

an arrow at one end indicates the type of join. (left or right) and is usually applied in queries, not relationships. Not sure about arrows at both ends.

 

[CelevGadol]

Thanks all for your replies. I may have misrepresented slightly what is going on here. The queries are multitable queries so the query sort of establishes a connection between the tables. The reports and forms are then based on the queries which is what I have done in any case in my own db. Obviously, the query is used for filtering and sorting prior to creating a report.

 

[CJ_London]

queries are not relationships - and no point in sorting a query for a report, you specify the sort order in the report.. As the others have said, you don't need to specify relationships unless you need to use some of the relationship features.

 

[mike60smart]

Hi
I would always Enforce Referential Integrity between related tables.