comment system for multiple tables

[southpawz505]

Hi all - first post, and will appreciate the feedback. I have a bit of a problem I'm stumbling into, and I was hoping someone could point me in the right direction. I have the following table setup:

-Goals (high level parent)
-Responsibilities (child of Goals)
-Activities (child of Responsibilities)
-Problems Identified (see below for explanation)

The concept is fairly simple...I have a table of higher level goals, each of those goals has child responsibilities to meet that goal, and each of those responsibilities has child activities that make up what specifically needs to be done to complete that responsibility.

I also created the "Problems Identified" table to keep a list of problems that I've found with various Responsibilities and Activities (not Goals). I would like for each Problem in the table to be able to identify it's parent, whether it's in the Activity table or the Responsibility table. If this doesn't make sense, let me provide an example.

Say the Goal is "Run my house". It has a child responsibility "Walk dog", and that responsibility has child activities "put on leash" and "take dog around house." I want to be able to have my Problem's table be inclusive of problems I find with the "Walk dog" responsibility, as well as it's "put on a leash" and "take dog around house" activities, and be able to identify it's parent.

The only way I can think of is to create 2 columns in the Problem table- one called Responsibility Parent and the other Activity Parent, and have the foreign key from the Responsibility/Activity tables populate respectively. It would be nicer though if I could do this in one column.

Is this possible? Am I thinking about this correctly? I would love to know if anyone knows a better design.



Thanks!

[Dal Jeanis]

Interesting. Parent Child terminology is pretty old school nowadays. Nowadays, we mostly look at the entities themselves, and then link them using junction tables that represent the relationship.

For example, instead of having a manager table and a staff table, and having each staff record have a pointer to their manager, you would have a staff table, possibly that also included managers, and have a junction table to represent the relationship that StaffMemberA reports to StaffMemberB.

You can also look at, for example, whether the relationship between a staff member and her department is such that department is a property of the staff member (just like gender) or whether it requires a junction table to make the linkage (such as when an employee can be shared between two departments).

Start by running over and reviewing Access MVP Roger carlson's quick tutorials about database design. http://www.rogersaccesslibrary.com/forum/topic238.html

If you put the relationships in a separate table, then you can have one activity that meets two or more goals or responsibilities. For example, if you have a Goal/Responsibility of "Spent Time with Johnny" and a Goal/Responsibility of "Maintain My Property in a Clean and Orderly State", then the activity of "Have Johnny help me Clean the Car" can have two junction records to represent the fact that it supports both goals.

Likewise, you have a problem table (or challenge, or consideration), and the problem can be associated with a particular activity, or with a particular responsibility.

I tend to think that you'll get superior results by looking at "Responsibility" as a special kind of high-level Activity (like the manager in my Staff scenario). Put a flag in the Activity record to denote Top-level activities that have sub-activities, and use a junction table to link them. Another possiblity is to look at "Responsibility" as table of subjects, and each activity has only one subject (which makes subject a property of the activity, rather than a separate thing). Or, each subject which might be related to more than one goal and each subject might be related to more than one activity, in which case you have a junction table for each of those.

I'd suggest you pop over to Roger Carlson's site and do his quick tutorials on database design. After doing two of his practice examples, do an entity relationship diagram for your database, and then post your proposed layout here for suggestions.

By the way, this kind of question would be a natural for the "database design" forum.

[southpawz505]

Thank you for the input. I'll take a look at junction tables and come back with a modified design.

And apologies on the mispost.

[southpawz505]

Dal - where in here does he discuss junction tables and the solution to a problem such as mine? I read through and didn't see anything about it. Am I missing something?

[June7]

Think Roger calls them 'linking' tables. See the ERD Word document - Figure 2 on page 11 and figure 1 on page 12.

[Dal Jeanis]

A junction table is just a table where the "entity" being described is some relationship between two other tables.

For instance, if there are tables called tblStaff and tblClass, the table that documents which Staff members have completed which Classes might be called tblAchievements, or tblCompletions, or tblClassesTaken, or tblStaffClasses. In this case, the record on the junction table would probably have additional information like the date the class was taken, results of testing, and so on.

On the other hand, a "pure" junction table might be one that links tblStaff to a table called tblHobbies, a list of hobbies and interests that any staff member might have any number of. The records on the junction table indicate only that that Staff member has that hobby or interest, and no other information.

[southpawz505]

Ok, thanks. So in my case with my hierarchy of Goals-->Responsibilities-->Activities, based on the reading I did and the idea of normalization, I agree that they should all be included in one table (which I'll call Items), and that it's better to think of a Goal being a higher level Responsibility, and a Responsibility being a higher level Activity. This simplifies it as now I can create a one to many relationship on each record in this "Items" table to something in the Problems table I described.

So I consolidated Goals, Responsibilities, and Activities down into one Items table as described above, but I'm still having trouble figuring out out how to use a separate junction table to indicate the relationships between the records in this table. My guess is in the junction table, have 2 columns - one column for the ID of the record in question, and then a second column with the ID of its parent? But I'm still not sure how this would work.

I get how it would work if there were 2 separate tables, but in my case, I want to be able to say that multiple items within in the same table are related...more specifically, Goals are going to own multiple responsibilities, and Responsibilities are going to own multiple Activities. Or the paraphrase your staff/manager example, one record is the manager of another, or several others.

[Dal Jeanis]

Yep, yours works just the same way.

Relating parent/childs in the same table is sometimes hinky. For example, do you think you'll ever want to print out the entire thing with all relationships? If so, how will you know how many levels you have of things that have been broken down into other things? On the other hand, do you really want to limit it arbitrarily to three levels?

If you establish that as an arbitrary limit, then just put a single "Type" field on the record that establishes location in the hierarchy.

Type=10 is a Goal.
Type=20 is a Responsibility, and its parent must be a Goal.
Type=30 is an Activity, and its parent must be a Responsibility.

Of course, if you've established that arbitrary guideline, then why not keep them in separate tables? The answer is that you may later decide to add additional levels, and there would be no need to add tables if you do it this way.

Think of it this way, then.

You can think of "SELECT * FROM tblItems WHERE MYTYPE = 10" As your Goal table.
You can think of "SELECT * FROM tblItems WHERE MYTYPE = 20" As your Responsibility table.
You can think of "SELECT * FROM tblItems WHERE MYTYPE = 30" As your Activity table.

You could even create queries called qryGoals, qryResponsibilties and qryActivities to do exactly that.

Your MyRelation table contains records that link Responsiblities to Goals or Activities to Responsibilities. You would only have to INNER JOIN that table on one side to one of the above three queries, and the opposite side would naturally be limited to the appropriate subset of records. Or, you could link it on both side to the queries.

[southpawz505]

So let me describe to you what I have laid out (along with a few questions)...I think I understand all you've told me in concept, but I'm just not sure on how to actually execute it. I took a stab at it though.

I have my one Activity table with the various attributes of an Activity. Among these is Activity Level and Parent Activity. Activity Level populates a drop down list from a separate table that simply allows you to select Goal, Resp, Activity. Parent Activity populates a dropdown list from what Access calls Activity_1, which looks like a clone of the Activity table. Between these 2, I have my Activity Relation table that connects the 2.

In case my explanation isn't clear, see the below map out of Access. The "many" side on the Activity table - the left one is to a column called "Activity Type", and the right is to a column called "Parent Activity."



My concerns here become, do you have to manually populate the "relation table?" That seems like an extra step that the end user isn't going to want.

Also, regarding the INNER JOINS, would it be possible to join all 3 so you could get a nice looking Goal--->Resp--->Activity type layout?

Thanks!

[Dal Jeanis]

1) Just to be clear, Activity_1 is an alias of the exact same table.

2) I'm not sure why there's a line from Activity to Activity_1. The table is only related to itself through Activity_Relation. You should not have a Parent Activity field on the Activity table - that's duplicating the purpose of the Activity Relation Table.

3) Regarding "manually populating the relation table". Whatever relationships you now have can be ported over programmatically. Any time you "break down" a Goal into responsibilities, or a responsibility into activities, you will be populating both the Activity table and the Activity_Relation table. When a user selects a Goal, for example, and wants to add a new responsibility related to that Goal, you create the Activity Record for the Responsiblity, then create the Activity_Relation record for that combination of Goal and Responsibility. Somewhere else, you'll probably have a screen to select an existing Goal and an existing Responsibility and create a record that relates them.

4) Yes, you can create one big inner join that will return all the info like that, but you have to think in terms of what is on the final record. The join will link each activity to each responsibilty it is connected with, and then to each goal it is connected with. If an activity is linked to two reponsibilities, and one of those reponsiblities is linked to two goals, then there will be three detail records with that activity on them. (This is a strength of the design, not a problem, but you have to be aware of it when you design your reporting.)

SELECT 
   TA.ActivityID, 
   TA.ActivityName, 
   TR.ActivityID, 
   TR.ActivityName, 
   TG.ActivityID, 
   TG.ActivityName
FROM
(((Activity AS TA
   INNER JOIN ActivityRelation AS TX1
   ON TA.ActivityID = TX1.ChildID)
   INNER JOIN Activity AS TR
   ON TR.ActivityID = TX1.ParentID)
   INNER JOIN ActivityRelation AS TX2
   ON TR.ActivityID = TX2.ChildID)
   INNER JOIN Activity AS TG
   ON TG.ActivityID = TX2.ParentID)
WHERE {{test that TG  is a goal record}};

[southpawz505]

Got it, thanks...took your feedback and reworked it - also got rid of the parent column in the Activity table. See table at the bottom of this post (erased a few things just for confidentiality reasons). Does there need to be a relationship established with the primary key of the relation table as well? I don't think so, but I'm honestly just not sure.

As you can tell, I'm pretty new to database design as well as Access, so your feedback and help working on this is very helpful. I apologize for my perceived lack of understanding, but you've got to start somewhere, so I thank you for your patience. Regarding #3 on your post...I'm assuming you mean that all this should be done at the form level (aka make your form so that you can enter details of your Activity, along with select a "Parent", and use that information to create a records in both the Activity and Relation tables). Because I'm not sure how my just entering records into the Activity table would allow relationships to "port over programmatically" as you say.

[Dal Jeanis]

Nope, the primary key of the relation table is only used for the purpose of identifying unique records in the case that you need to deal with accidental duplicates. Some people would leave it off and enforce a two-field unique key constraint instead. Your mileage may vary.

You did read #3 correctly. Design the forms to make it easy for the user.

The "port it over" comment was only for the case that you already had a database (of some different design) or a set of excel spreadsheets that you wanted to use to populate the initial database. For simple databases, excel spreadsheets can be a very useful tool for creating your initial data load.

I assume that "owned by" is a userid, and that there's a user table not yet pictured.

I also assume that, since you have "frequency", you'll be doing something to track when the activity was last completed and to remind the user when it is do again. You should determine those features at this point as well.

[southpawz505]

Thanks Dal - I think I should be able to walk from here. One final question - data most likely WILL be getting imported from somewhere like excel, so I don't necessarily know that a form would be the most helpful tool in establishing the relationships. How WOULD the information be ported over to that table? Would the user have to specify somewhere in the imported spreadsheet the ID of the parent, and then I'd use that info to just import to the relation table?

[Dal Jeanis]

Sure, you could do it that way.

Think in terms of importing the spreadsheet to one or more temporary tables, and then using the temporary table(s) to establish all the desired relationships. Whether you choose to manually build the detailed table data in excel and import it directly, or to build a semblance of it and then use temporary processes to normalize the data into your actual layout, is up to you.

For instance, have initial temporary keys (perhaps in the 1000s) populated in the Goal spreadsheet. (Parent key is empty.)
Have initial temporary keys (perhaps in the 2000s) and parent Goal keys (in the 1000s) populated in the Responsibility spreadsheet.
Have initial temporary keys (perhaps in the 3000s) and parent Responsibility keys (in the 2000s) populated in the Activity spreadsheet.

That can be a total of one or three spreadsheets, it doesn't matter.

Import those tables, then use them to load a temporary Activity table. (including their temp keys and parent temp keys). Add an autokey field, then use the autokey field as the Foreign key to build the activity relation records. Once that's done, you can delete the temporary keys and parent temp keys from the records, and you're done.

By the way, it would be a good idea to review your naming conventions. Spaces in table and field names just lead to a lot of extra work.