Validating process for new Access 2016 database
Folks,
I have attached a Word 2016 document that outlines my new database that takes Implementation through Step One. With so much introductory information needed I felt it would be difficult to describe my problem and objective here.
Thanks to all that participates. I wish I could distribute points like we us to do but it appears that is no longer an option.
Logic-for-Excel-Solutions.docx
I have attached a Word 2016 document that outlines my new database that takes Implementation through Step One. With so much introductory information needed I felt it would be difficult to describe my problem and objective here.
Thanks to all that participates. I wish I could distribute points like we us to do but it appears that is no longer an option.
Logic-for-Excel-Solutions.docx
ASKER
ste5an,
Thanks for comments. Let me try to answer them to the best of my ability.
1. WHAT ARE YOUR QUESTIONS?
Answer: I should have been clearer in regards to the specific problem in the first step in order to go forward. The first step is presented to determine what is the best method to validate that a Topic has not already been enter to avoid duplicate named Topics? Can this be done at the table level by making the field Topic a Primary Key? Or would it be better to search through the tblTopic. If the Topic already exist then no new Topic can be entered. Which approach serves to be the best solution or is there another approach that is better?
2. From the general problem and the sample, I'm not sure whether your topic sample makes sense a single work sounds more like a tag.
Answer: First of all I was simply attempting to introduce a database design, subject to change, based upon the advice provided by the Expert-Engineer participants. Therefore, I started with the first table that serves to be the primary table from which the next table tblSubTopic will depend upon. You used the term "tag" which I hope you will clarify regarding the first table and subsequent tables.
3. You mention cascaded deletes: I would not use them in this case.
Answer: I elected Cascade delete because there may be a need to delete any SubTopic, Issue, and Solution without deleting the Topic. Cascade Delete may not be the best way to accomplish this so what approach should be used?
4. The Topic table is no correctly implemented.
Answer: The term implement was probably a poor word. What I was wanting to communicate was the design of this table Topic correct? I have yet to show additional tables at this time so as not to confuse my objective which is "To create an Access Database for the purpose of recording various Excel Topics, SubTopics associated to a Topic, Issue associated to a SubTopic, and Solution associated to an Issue."
5. Your object naming could be better.
Answer: The Object Naming convention I chose was based upon following recommendations from other developers of which there can be as many naming conventions as developers. I chose to prefix tbl for tables, frm for forms, qry for query, rpt for report and mod for module.
6. Column names should reflect the attribute. The column Topic should be TopicName or TopicText depending on the kind of attribute stored here.
Answer: I can see your logic and your recommendation will be implemented. Good catch!
7. The TopicName must have Allow Zero Length: No and you need to make it unique, thus create an unique index on that column alone. It must not be part of a multi-column primary key, cause in combination with an auto-incrementent, would allow duplicate content in the TopicName column.
Answer: I can see your logic and your recommendation will be implemented. Good catch!
8. Catching different names representing the same as "PivotChart" or "Pivot Charts" requires a keyword table. You then use this keyword table to identify similar topics. The problem is: You must populate that table before any topic is entered.
Answer: Indeed, this is a problem. A keyword table would be cumbersome to maintain. How do you feel about a Message box that serves to ask the user to confirm that a Topic entered was not located and they could accept or reject the Topic?
9. The topic form should have a text box to search for topics and sub-form showing the topics with some stats, like number of sub topics, issues and solutions.
Answer: It appears that a text box searching topics needs to be implemented as stated in my answer to your question 8. I am having a problem regarding the second part of your question here regarding stats. However, I do see value in showing all SubTopics as it relates to the
Topic. This would allow the user to continue towards a Solution by selecting a SubTopic they are interested in.
Note: Items italicize and bold are questions back to you. Items in bold are your suggestions to be implemented.
Thanks for comments. Let me try to answer them to the best of my ability.
1. WHAT ARE YOUR QUESTIONS?
Answer: I should have been clearer in regards to the specific problem in the first step in order to go forward. The first step is presented to determine what is the best method to validate that a Topic has not already been enter to avoid duplicate named Topics? Can this be done at the table level by making the field Topic a Primary Key? Or would it be better to search through the tblTopic. If the Topic already exist then no new Topic can be entered. Which approach serves to be the best solution or is there another approach that is better?
2. From the general problem and the sample, I'm not sure whether your topic sample makes sense a single work sounds more like a tag.
Answer: First of all I was simply attempting to introduce a database design, subject to change, based upon the advice provided by the Expert-Engineer participants. Therefore, I started with the first table that serves to be the primary table from which the next table tblSubTopic will depend upon. You used the term "tag" which I hope you will clarify regarding the first table and subsequent tables.
3. You mention cascaded deletes: I would not use them in this case.
Answer: I elected Cascade delete because there may be a need to delete any SubTopic, Issue, and Solution without deleting the Topic. Cascade Delete may not be the best way to accomplish this so what approach should be used?
4. The Topic table is no correctly implemented.
Answer: The term implement was probably a poor word. What I was wanting to communicate was the design of this table Topic correct? I have yet to show additional tables at this time so as not to confuse my objective which is "To create an Access Database for the purpose of recording various Excel Topics, SubTopics associated to a Topic, Issue associated to a SubTopic, and Solution associated to an Issue."
5. Your object naming could be better.
Answer: The Object Naming convention I chose was based upon following recommendations from other developers of which there can be as many naming conventions as developers. I chose to prefix tbl for tables, frm for forms, qry for query, rpt for report and mod for module.
6. Column names should reflect the attribute. The column Topic should be TopicName or TopicText depending on the kind of attribute stored here.
Answer: I can see your logic and your recommendation will be implemented. Good catch!
7. The TopicName must have Allow Zero Length: No and you need to make it unique, thus create an unique index on that column alone. It must not be part of a multi-column primary key, cause in combination with an auto-incrementent, would allow duplicate content in the TopicName column.
Answer: I can see your logic and your recommendation will be implemented. Good catch!
8. Catching different names representing the same as "PivotChart" or "Pivot Charts" requires a keyword table. You then use this keyword table to identify similar topics. The problem is: You must populate that table before any topic is entered.
Answer: Indeed, this is a problem. A keyword table would be cumbersome to maintain. How do you feel about a Message box that serves to ask the user to confirm that a Topic entered was not located and they could accept or reject the Topic?
9. The topic form should have a text box to search for topics and sub-form showing the topics with some stats, like number of sub topics, issues and solutions.
Answer: It appears that a text box searching topics needs to be implemented as stated in my answer to your question 8. I am having a problem regarding the second part of your question here regarding stats. However, I do see value in showing all SubTopics as it relates to the
Topic. This would allow the user to continue towards a Solution by selecting a SubTopic they are interested in.
Note: Items italicize and bold are questions back to you. Items in bold are your suggestions to be implemented.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pat, I saw my error in logic after I chewed on it. Last night I came to the SAME conclusion and design as you recommend! It seems great minds think a like. I'll play around with this at first light but accept your solution. Thanks
ASKER
Thanks folks
You're welcome.
Start by creating dummy tables having only the necessary columns to create the relationship diagram and the relationships. Post a screenshot of it. Also post the context in the text instead of a word document. Reading extra documents is tedious.
From the general problem and the sample, I'm not sure whether your topic sample makes sense. Cause a single work sounds more like a tag. So maybe it's
Tags -1:n-> SubTopics -1:n-> Issues -m:n-> Solutions
instead of
Tags <-n:1- Topics -1:n-> Issues -m:n-> Solutions
You mention cascaded deletes: I would not use them in this case.
For your question 1): The Topic table is no correctly implemented.
a) Your object naming could be better. Using general prefixes for tables and views makes no sense, cause both are data sources, thus the same class of objects from the consumer. Using prefixes which indicate the consumer, like frm_Topics for the view which collects the data for the Topcis main form or rpt_Topics for a topics report.
Column names should reflect the attribute. The column Topic should be TopicName or TopicText depending on the kind of attribute stored here.
b) The TopicName must have Allow Zero Length: No and you need to make it unique, thus create an unique index on that column alone. It must not be part of a multi-column primary key, cause in combination with an auto-incrementent, this would allow duplicate content in the TopicName column.
c) Having such a unique TopicName column solves a part of your problem automatically, cause Access will throw an error when a duplicate is tried to be inserted. You catch this error and show then your topic already exists message.
d) Catching different names representing the same as "PivotChart" or "Pivot Charts" requires a keyword table. You then use this keyword table to identify similar topics. The problem is: You must populate that table before any topic is entered.
e) The topic form should have a text box to search for topics and sub-form showing the topics with some stats, like number of sub topics, issues and solutions.