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.