Forum Discussion
Copilot, Access, and VBA
Here is my website that has more info on the application: https://spendingincometracker.com/ Here is the Access Relationship Diagram. I had Copilot evaluate it and here is what it gave me: "Overall, your Access Relationship Diagram reflects a well-thought-out database design. Great job! Is there anything specific you want to dive deeper into or adjust? 🚀" I would welcome human comments as well!
Unfortunately, the forum has reduced the image to a size much too small for my elderly eyes to decipher.
I'm nervous about accepting the word of ChatGPT here, to be quite honest. It's often ebullient about issues that are anything but cut and dried. Terms like "Great job!" strike me as being slightly condescending to boot. We're professionals, not 2nd graders in need of praise for putting our books away properly. Don't take that as a criticism of your work; it's aimed at the creators of ChatGPT etc. Apparently they think that makes their responses more trustworthy or something.
Unfortunately, from what I can garner from the name, there is at least one query in the diagram? And one table with no relationship to any other tables. That solo table may well be appropriate (hard to tell), but if that really is a query and not a table with an inappropriate prefix, I don't see how it belongs.
I have attached a .png copy. I wasn't ChatGPT but CoPilot. Here is its complete reply:
I reviewed your relationship diagram, and it's quite comprehensive! Here’s a general evaluation:
Strengths:
- Clear Relationships: The relationships between tables are well-defined, which is crucial for maintaining data integrity.
- Normal Form: The structure seems to follow normalization rules, minimizing redundancy and ensuring efficient data management.
- Key Constraints: Primary and foreign keys are correctly set up, which helps enforce referential integrity.
- Modular Design: The inclusion of various tables such as Tbl_Accnt_Class, Tbl_Accnt_Category, and Tbl_Accnt_Type indicates a modular approach, which is good for database scalability and maintenance.
Potential Areas for Improvement:
- Complex Queries: Ensure your queries like Qry_CashProj_NoPay are optimized for performance, especially if your database grows.
- Table Documentation: Adding documentation or comments in the relationship diagram can help others understand the purpose of each table and relationship more quickly.
- Indexing: Make sure that appropriate indexing is applied to frequently searched columns to enhance query performance.
Questions:
- Usage of Null Values: Are there plans for handling null values in foreign key fields? Null values can affect how data is related and should be considered in your design.
- Future Expansion: Are there plans for future expansion or addition of more tables? If so, consider how new relationships will impact your current schema.
Overall, your Access Relationship Diagram reflects a well-thought-out database design. Great job! Is there anything specific you want to dive deeper into or adjust? 🚀
Thank you.
Again, I find the gratuitous "Great job!" to be inappropriate in this context. It is, no doubt, part of Microsoft's attempt to sell the Copilot service. It makes me nervous to have AI passing value judgements. It should stick to factual statements. But maybe that's just me.
I see two flaws in the diagram. Both were mentioned in my previous response.
I can't understand how an object with the "qry" prefix in its name made its way into a relationship diagram. There is no way to create a relationship between a query and a table. Only tables can support relationships. This alone undermines confidence in the AI built model. If it thinks that queries can be part of the relationship model, it can't be that strong.
The other potential problem I saw was that table which apparently has no relationships to any other, the "Budget Timeframe" table. Now that I can see what it actually is in the larger view, I can understand how it might not have a relationship, but I'd be sure to validate that in practice.
Finally, in the absence of a narrative explaining the business being modeled and the business rules which apply, one can't say for sure whether the tables and relationships are or are not appropriate. On the surface, they do appear to be okay for the most part, other than the caveats above.
There is one place I'd want to have a deeper understanding of what this represents. I see two relationships involving the "Account" table and the "Financial Institution Accounts" table that seem questionable. They are directly related. However, both "Cash Projections" and "Account Transactions" also have relationships to both of those tables. Again, given limited exposure to the context, I would want to drill into those to be sure they are properly modelling the business.
I agree that CoPilot's tone is a little strange at times. I just ignore the tone when it tries to sound like a person. The Budget Timeframe table has one record with two fields. A primary key and a field that is either yearly or monthly. That field is not needed in any other table but does affect forms, reports, and whether a value is kept in the period field of the Account Budget table. You can add queries to an Access relationship diagram. That is the only one I added and it is more for a reminder.
In the app Financial Institution Accounts need for lack of a better term, a chart of accounts -account to charge transactions. For example, First Back Checking, the financial institution account name, and the account name Checking. While the financial institution account name could be used in two places, I think it is cleaner to keep them separate. Account Transactions requires an account name such as Rent and where that account was paid from...example Checking. Cash Projections requires both the account that is to be included and also which institutional account.
I appreciate your feedback.
My next task with CoPilot is to assist me in putting together a ClickOnce deployment package with Access Runtime. I will post how it goes.
