Please review and critique my table design

[pmcleod]

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.

 

[KenHigg]

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)

 

[KenHigg]

This is warped thinking.

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

 

[raskew]

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

 

[KenHigg]

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

 

[KenHigg]

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)

 

[pmcleod]

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!

 

[KenHigg]

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?

 

[pmcleod]

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.

 

[KenHigg]

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

 

[KenHigg]

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...

 

[pmcleod]

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!

 

[KenHigg]

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?

 

[KenHigg]

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 )

 

[pmcleod]

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.

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?

 

[KenHigg]

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?

 

[pmcleod]

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?

 

[KenHigg]

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

 

[pmcleod]

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! Well Pat ... what say you?!

 

[KenHigg]

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?