I'm trying to create my own events (issues, action items et al) log database for project management.

I have attempted to have the events interrelate to each other. I am inviting comments, suggestions and the like.

Thank you in advance.

Interesting.

1. Couldn't you do this is MS Project?
2. I don't see any 'Many' sides in the relationships
3. I (personally) would not maintain a relationship where all I'm doing is using a table for a look-up type table (like the tblStatus)

Sorry, I couldn't resist:
3. I (personally) would not maintain a relationship where all I'm doing is using a table for a look-up type table (like the tblStatus) This is warped thinking. It is in the same category of "I only have 3 so I won't make a many-side table". If you have a relationship, it is important to enforce RI. Letting the database engine do it is far better than leaving the the validation up to the application.

Lookup tables not only provide text values for the lookup values, but they also prevent bad data from being added to the main table - of course that is only if you enforce RI.

Show me an application that has been running for a while where the developer didn't bother to enforce Referential Integrity and I'll show you the bad data. I would bet cash money on it!

This is warped thinking.

That's certainly a pleasant and constructive start to voicing your opinion.

I'm sorry. I guess that could sound offensive. I didn't mean it to be. I was using the term in its literal sense of being skewed. ie your suggestion to not enforce RI deviated from the accepted norms. RI is your friend:) It doesn't hurt anything to enforce RI and it can certainly prevent bad data from being added to a table. I don't see a downside. There can be some inconvenience if you are in the process of deleting and reloading an entire database because you are working on a conversion but in that case, I drop the RI and then immediately readd it.

Hi Pat -

Regular appointments (e.g. every two months) are the key. Need to keep our dosage regulated as we get older, and older, and .....older!

Best wishes - Bob

I doubt anything I could say would sway your opinion. But if pmCleod cares to hear my reasoning, I'd be glad to elaborate ;)

You're right but that doesn't mean that I wouldn't like the opportunity to sway your's:) You contribute a great deal to this forum and I won't let this little disagreement change my opinion of that even if you are wrong:)

You're right but that doesn't mean that I wouldn't like the opportunity to sway your's:)

In my humble opinion, the reasons you give are important, but not enough to sway my opinion carte blanche, as of yet :)


You contribute a great deal to this forum and I won't let this little disagreement change my opinion of that even if you are wrong:)

Thanks, And I won't let it change my opinion of your contribution to the forum - Overall, I think your vast knowledge and ability to advise others on Access and database issues is among the best, even if you too, are on rare occasion, wrong (IMHO) :)

I posted this just before I left the office on Friday and had neglected to switch to RI. RI is a forgone conclusion.

MS Project .... not really a good tool for tracking the myriad things that come up the PL. Issues may or may not affect durations, sequences et al ... if they do, then those changes can be reflected within the project plan.

This tool is for tracking the things that come up in meetings and the like. If it's not writen down and tracked ... then it never happened.

Using the task manager in Outlook, again does not provide nice reporting.

Ok ... back to my little app. Did I get the many-to-many right for the events, as they relate to each other?

Issue ID 15 might be related to 12 and 12 might be related to 8 and so on. I want to make sure that I captured that capability.

Also I tried a many-to many for the sub-categories. I don't want to limit myself to just one sub-category. Example - the Category might be Content ... but I may want to drill down to XML conversion ... then even further to an issue with the DTD <PRELIM> </PRELIM> ..... at that point I may want to open another event as a change request and relate it to the DTD Issue where the change is to do something to that <PRELIM> tag.

Can you see where I'm going with this?

Again ..... Please review my DRAFT design and offer advise, make changes (and repost) ... please I really really want all of you input!

Hum...

When you select a category, do you want to limit the sub-categories based to that category or do you always want the user to be able to select any sub-category, regardless of the mian category?

The former would be ideal. I'll have to set up cascading combo-boxes when I start the UI. I expect that I'll be adding cats and subs as I require them. But yes ... I'd what the sub-cat list to be limited to it's parent category.

pm,

Please allow me to elaborate on my concern with the lookup tables you have set up in your schema. Please keep in mind that I don't mean to come across as an authoritarian on db's or Access, just some thoughts based on my experience.

RI is great. But I think it's main role is between entities not necessarily between an entity and one or more of it attributes. You can RI some db's to death. That's sometimes when the 'experts' say you may need to de-normalize.

When tables get real big, extraneous pk/fk stuff can have an adverse affect on performance. If you have 100k rows to deal with and mix in 3 or 4 extra pk/fk steps the db has to go through everytime, for ever record, the performance is degraded.

Something like this:

- Get the record and show it on the screen,
- Get the next record and show it on the screen,
- etc.

Vs.
- Get the record and show it on the screen,
- Go find the associated record in the tblStatus table and show it on the screen,
- Get the next record and show it on the screen,
- Go find the associated record in the tblStatus table and show it on the screen,
- etc....

Secondly now, and I realize this is very minor, every time you need queries run, you have to contend with making sure you've got the textual representation of the value, not just the fk/pk unique identifier...

I would just have the different status's in a table without the relationship back to the main table. Then a combo box based on the table that allows the user to select whichever one applies...

Like I said, just my opinion, nothing etched in stone as far as I can see :)

The former would be ideal. I'll have to set up cascading combo-boxes when I start the UI. I expect that I'll be adding cats and subs as I require them. But yes ... I'd what the sub-cat list to be limited to it's parent category.

Hum...

Then maybe you should do a 1 to many between the cat and sub cat tables. (Adding a CatID fk fld to the subcat table)

I think that would work. I'm not sure what would happen if you maintain the relationships between the cat tables and the other tables...

I see your point. True enough. Why have the db take the extra steps the field is no more than a simple lookup.

Did you happen to see if I got the many-to-many relationships right for the interrelationships between the events themselves?

thats the foundation of what I'm trying to accomplish ... there is great value is seeing who the web of activities interrelate to each other.

Also .... you mentioned the categories and sub-categories. Is the many-to many ok there?

I like your idea of having the cats and sub cats together. I can change the design it so that the sub-category table has a lookup the categoryID thus I can associate the right sub-cat to the parent category. That's dead easy .... I'd have to change the other relationships to those tables too, but again no worries on that.

I'm really concerned about the many-to-many's mentioned above. They they are not right than the whole thing falls apart!

Hum... So you have a project. The project has multiple events.

Now from an end user perspective, they're working along doing what ever and from where-ever they come up with a task that they need to log. Say, 'Boss says to get out report 'A" by the end of the day'. So they pull up the db, enter the verbal description of what they need to do. Then they select the project it is associated with, say 'Software Project X'. They also select a category, for this instance it may be 'Reports'. Then they may or may not have a sub cat for 'Reports' called maybe 'Printed Reports'. Is this close so far?

Hum... Many to many's are sometimes a flag that something else is needed. I'll take a peek. (I like this idea for a db :))

so far you are bang-on with you Use Case. Let's change the example.

I have an event that I want to track that is related to Project-X.

That event is an Issue that is related to the category Content Conversion.
If there is a sub-category it might be tagging requirements, or something.

The issue might be that the off-shore supplier is waiting for requirement 4.23.5 to be resolved. Do we follow path A or path B. The supplier can't proceed until we get back to them on which path to follow.

In a meeting I as the PM make a connection between this issue and another issue where say we have an event ... also an issue. This issue is that the business is waiting for some content samples to come in from another so that they can do an analysis. This analysis will answer requirement 4.23.5

I now (or some other user) will make an association between these two issues so that they can be tracked together.

As the PM I may create another event this time it might be an action item. that action item is for the sponsor to get on the phone and light a fire under the pants of the second supplier. I set a due date for that event and then relate it to the second event.

The sponsor gets on the phone ... lights the fire and I close the third event ... but the other 2 are still open.

my business analyst has to issue a report or give a presentation relating to these issues .... he/she can see that there are events pending and know knows how to adjust his/her deliverables ... which then can be reflected within the overall plan.

In a report ... I can see how these events relate to each other, what's open ... what's closed, where are we in the relationships between these events - as you image this can be of great value! The BA might then relate his/her report (to use your example) to the above mentioned.

The WEB is complete. :eek:

Agreed ... these examples would be in my plan ... but they are in the plan as end deliverables only. The actual nity-gritty might not be in the plan ... but still has to be tracked.


does this "use case" help?

Hum...

To make the event stuff work, seems like you'll need to link the primary event back to the related event table (Have two relationships; a 1-Many and a Many-1...)

Think that woukld work?

Not sure if I understand that. Is this what you mean? (see attached). This can't be right ... ok ... I DON'T understand. Can you give and example?

Ok ... now the "tblEventRelationships" has it's own Key (RelateID - auto number) does it need one?

I have 2 keys ... do I need them both?

Hum... Now i'm confused. I'm not sure how to have a table reference records in itself on a one to many like this...

Maybe if I haven't upset Pat too bad, she'll take a peek and give us some ideas :)

How about it Pat? Refering to the Use Case, how can we build the web of related events? Plus ..... riddle me this ... how does one avoid a loop? Can RI and schema take care it of alone or does a routine have to be written that will make sure the relationships make logical sense?

1 > 4 > 5,6 > 1 .... neither 5 nor 6 can go to 1 b/c 1 brings it right back to 5 and 6.

The plot thickens! :rolleyes: Well Pat ... what say you?!

OK. What about if you have the eventID linked to both the primaryEventID fld and the secondaryEventID fld in the relationship table, both being one to many.

tblEvents
EventID
001
002
003
004
005

tblRelationships
relationshipID primaryEventID SecondaryEventID
1001 001 104
1002 001 105
1003 002 106
1004 003 108
1005 003 129

Would this work?

following your example ... here is an attachment. When I look at the event table it does not show me any relationships. Honestly ... I don't know. I was not able to build a query that showed me the interrelationships.


Regardless, there is still an issue of loops.

The relationship between tblEvent and tblEventRelationships does not look correct. If what you are trying to do is to create a many-many relationship between events, you need only the two eventIDs. They should be part of a 2-field primary key. If you want to ensure that only a single entry is made for event1 and event2, you could always put the lower ID in the first event and the higher in the second event. That way, once you have Event1/Event2 you won't also have Event2/Event1. Or, alternatively, you could always force both entries. Whichever you think will be easier to work with.

To enforce RI, you need to add tblEventRelationships to the relationships window twice. Draw two join lines. One from EventID in tblEvent to the first EventID in tblEventRelationships and the second from EventID in tblEvent to the second EventID in tblEventRelationships.

Small lookup tables as you have defined do not burden SQL with extraneous overhead. When doing joins, the "plan" that the query engine prepares normally calls for small tables to be permanently loaded into memory while processing the query. So the joins are extremely fast.

Working with a 90 million row customer table, we proved that declarative RI (the lookups we're talking about) was faster than program controlled validation.

If you have HUGE tables and you don't want to incur the join overhead for select queries, you can make your lookup tables with text values and store the text values in the foreign key fields rather than using the more standard autonumber. This does present more of a maintenance problem should the string value ever need to change so don't use the technique arbitrarily. With this method, the lookup table only comes into play when new rows are added or the foreign key value is changed.

Pat, So in the example I have in Post #23, would this be correct if there was two 1->Many relationships from the event to the relationship table?

Yes it would. I tend not to use autonumbers as the primary keys for relation tables unless the relation itself has children but it doesn't hurt as long as there is also a unique index on the two foreign key fields.

I just reworded what you had already told pmcleod since he didn't understand it.

I followed your instructions, which were to add the EventRelated table twice and then attach the first (parent) event to the event id and then the second (child) event to the event id. However when I look at the Event table and select the "+" button, it does not show me the relationships for that record.

EventID has an RI enforced one-to-many with the first ID in the related table, which I've renamed to EventIDChild.

EventID has an RI enforced one-to-many with the second ID in the related table, which I've renamed to EventIDParent.

Both IDs in the EventRelated table are FKs. I cannot create a duplicate entry by adding a ParentID = 2 ChildID = 1 more than once ... as you'd expect.

However I can create a record where ParentID = 1 and the ChildID = 2. Which means that I don't understand how to force both entries, as you suggested. Nor did my attempt to understand "...lower ID in the first event ...", which I took to mean Field One is the child (lower), while field 2 is the higher, or parent.

I thought I had understood and followed your instructions ... would you continue to help me?

sample attached.

Hello,

I have a couple of questions:

What happens if Perry leaves and Bob takes over his events/projects?
Also rather than Perry being assigned in the Main Event/Project Tables you could Assign the events to Perry in a different table with Start Date End Date in that table,

then you can:

assign Bob to the event easily
have an unassigned event/project (might not be important)

What if Perry moves, unless it REALLY isn't important I would breakdown the contacts info further.

Another thought might be to not have the open/closed Status. Instead have a Closed Date, this way if there in no closed date....it's open.
You can track target levels this way, has it been complete before time, has it ran over.

I hope something there is useful.

Regards,

Sorry ... was away, family business. I like using the date as an indicator of status. Shameful that I never thought of it.

Setting up a person assigned table would be better too.

As my grandmother used to say .... "you be the smart one"

I'm still at a loss regarding the events relationships. that, however seems to have been picked up in another string. http://www.access-programmers.co.uk/forums/showthread.php?t=94257&goto=newpost

I completely understand the concepts, in fact manage projects far more complicated than this, however I'm not a developer ... and so I don't know how to apply them.

I can take another's code and modify it to suit my needs, but write something myself .... no can do.

I'll have to pull back my requirements considerably, back to something I can do myself.