Why Use Relationships?

[ohmydatabase]

Hey everyone,

So I get the importance of relationship. By relating a field in Table-A to a primary key of Table-B you:
- Reduce the amount of data that is being stored since these keys can be represented as integers or other smaller data types
- Improve consistency (i.e. Change in Table-B's value associated with a given primary key will be reflected in all other tables granted the primary key itself doesn't change)
- Visualize the relationships of your data and what is being used where

All these are great and seem like one of the very first things taught in learning Access. I do everything through VBA so I'm a little confused by the usefulness of relationships. And have come here for everyone's input!

1) When I use the CurrentDb.OpenRecordset method on Table-A I would get whatever data I had for that record, which would include the primary key of the associated record in Table-B. I would then have to use a DLookup or another OpenRecordset call to grab the actual value of the primary key in Table-B. Now what if Table-B had 15 fields (or more) and every single one was an ID that was related to different tables (Table-C, Table-D, etc.) Would the additional code and massive amount of DLookups ultimately slowing my database? To date, I've ignored relationships entirely and put the entire string, date, or piece-of-data, directly into my "Table-B". This has come back to screw me a few times in the form of massive amounts of repetitive correcting, or typos leading to improper string matching I do to perform my own manual relationship.. But, every time I use CurrentDb.OpenRecordset I can easily define any variables I need right then and there.



2) Would the definition of many relationship end up slowing the database overall? I get this question from the argument of using Linked-List vs. Arrays. While arrays are labor intensive, linked-lists can be memory intensive. The actual nuance of it all is above my head to be honest.

3) Are relationships used in any other databases like MariaDb, MangoDb. I worry that using all the quirky features in Access will make you great at Access, but will leave you at a disadvantage if you are forced to swtich to Java or something like that.

[andy49]

Why open a record set then go looking for another item using dlookup?

A subform does the hard work or even use an sql which pulls all variables you need from different tables.

I myself would admit to probably writing too much vba but it seems you are taking the long route and are happy with that?

That's up to you. I guess speed would be effected as you added more code especially dlookups but to what extent would depend on your database size.

[CJ_London]

Are relationships used in any other databases like MariaDb, MangoDb.

as a rule, relationships are used by all databases (except perhaps not NoSQL which works with unrelated data)

In your example you would just jon the tables together in a query

relationships can also have rules applied - primarily a record in a child table cannot be created if the parent does not already exist. e.g. a parent table might contain invoice headers, the child table the individual invoice lines. You would not want to create a line without a header

[ohmydatabase]

Why open a record set then go looking for another item using dlookup?

A subform does the hard work or even use an sql which pulls all variables you need from different tables.

I myself would admit to probably writing too much vba but it seems you are taking the long route and are happy with that?

That's up to you. I guess speed would be effected as you added more code especially dlookups but to what extent would depend on your database size.

I didn't know that an SQL statement could be written that would pull values from multiple tables. I've never used a subform either. All the forms I've created don't display single records. I use list-boxes quite frequently to show the user a list of records of interest. I've also never got into Access queries. All the queries I use are SQL strings. I thought that these were the same as what Access makes using the query designer. I have a feeling the SQL command I'm missing is the "JOIN" command. It confused me when I tried to learn it, so I've never really used it successfully. I tend to use a lot of the "IN" command.

[ohmydatabase]

as a rule, relationships are used by all databases (except perhaps not NoSQL which works with unrelated data)

In your example you would just jon the tables together in a query

relationships can also have rules applied - primarily a record in a child table cannot be created if the parent does not already exist. e.g. a parent table might contain invoice headers, the child table the individual invoice lines. You would not want to create a line without a header

Awesome, I'll see if I can simplify my backend by introducing relationships and redefining things to actually use Primary Keys! Thanks!

[orange]

Here's a link that may be helpful and makes a good reference re database planning and design.

If you're a fast learner, here is a set of youtube videos by Dr. Jennifer Widom.


Good luck.