Help with Database Planning?!

[Michelle F]

Hi there! I am hoping someone can help me weed through this mess of
relationships and set up a logical structure. Hopefully I can lay this
problem out clearly.... I think the answer may lie in creating place holding
"blank" values to help the grouping, but I am looking forward to what
everyone else thinks.

The database is designed to track "activities" that can be associated with
either a project or a document. A project MAY be (does not have to be)
associated with one or more documents. Currently, I am forcing activities to
be one or the other (project related or document related) but this splitting
is getting complicated and confusing. I would like to be able to combine
everything more neatly.

Ok, so there are projects, documents, and Technical Committees (TC). All
projects belong to a TC, but not all documents belong to a TC. And not all
TCs have projects or documents. So, I think it is something like this:

Project to Document: one to many
Project to TC: many to one
Document to TC: many to one

I would like to be able to view activities sorted by TCs, projects or by
documents (whether the document is associated with a project (entered as
project-activity) or not (entered as a document activity)). I am considering
creating a blank "project" to catch all documents not associated with other
projects but I am not sure if I will be able to sort activities within that
blank catch-all group.

I hope this makes sense! My current method of having activities entered as
either associated with a project or a document is becoming cumbersome and I
am afraid it will lead to more problems in the future.

Thanks!

 

[Fred]

If one takes everything that you said and implied literally you end up with
a spiderweb of relationships, with most of them being "not necessarily"
Including a one to many relationship between two entities (projects and
documents) which you say you want to combine! And those two are mutually
exclusive to each other with respect to their relationship to activities.
You also introduced an entity "activities" which you didn't mention in your
draft structure leading to the concern that it may be a synonym to one of the
other entities. And documents and TC's can sometimes directly related to
each other, and sometimes indirectly related via. a connection through
projects.

While it would be possible to create a structure that stores all of the
above, I think that it would be disjointed and not do much because nearly
every relationship rule is "not necessarily" With no rules there is no
organization, and relationships described as being "not necessarily" are not
really relationships.

I'm not sure what to say from here. The challenge may merely one of
describing it, or of adding and telling us about a few more "rules" in the
relationships.

Hope that helps a little.

Fred

 

[Michelle F]

I think "not necessarily" is the operative word. I also like the term
convoluted to describe it. But I will try to describe it better from the
top. The overarching structure is a TC which will have projects and
documents. For instance

TC 08 has

Doc 201
Doc 202
Doc 203
Doc 204

Proj1 = combined revision of Doc 201 and 202 (i.e., project is associated
with two documents)

Sample activities:

Act1 = first draft for Proj1 (activity associated with project)
Act2 = inquiry about creating project for revision of Doc 203 (activity
associated with document)

On top of this, take into account an activity like

Act3 = meeting for Doc 210 (does not fall into a TC)

Now imagine a scenario where Act4 = is a first draft on a new Proj2 which is
a revision of Doc 203 (activity associated with a project).

If I wanted to do a search for all activities for Doc 203 I have instances
where it was not associated with a project and then later had a project.

I have thought about creating a fake project Proj00 which would house all
documents not associated with a TC (would have to make up fake TC) or a
project. But, would I then be able to extract activities associated with Doc
203 if it once belonged to Proj00(along with many others) and now is in
Proj2? The problem is that, for instance, any activites associated with Proj1
(combined revision of Doc 201 and 202) will able to BOTH documents. But
activities associated with Proj00 will not apply to all documents within it.

Does this make more sense? It is a spiderweb. I have inherited this mess
without much forethought to feasibility of design. So, I am doing my best!

Thanks again!
Michelle

 

[Fred]

Hello Michelle,

On my first pass at rigorously absorbing what yopu wrote my brain started to
explode so I had to shut it off. I'll try to try again, but in the
meantime, a few thoughts.

Even a spiderweb can be created / documented in Access relationships.
Write a query that links whatever tables when needed. And even the most
complex scenerios (like "sometimes many-to-many") can be accomodated with
itermediate linking tables. I'm just not sure how informative / useful such
a spiderweb will be. If you have no rules (= all exceptions, fluid
definitions and infinite linking possibilities) then you don't have data
structure that serves you.

Not sure to what extent those terms are part of the real world ( = you
probably shouldn't / can't change them) vs. inventions for the DB
implementation (= you can change / redefine / reorganize them.)

My gut feel is that when you have a multi-tier structure that the core
structure should follow one attribute/nature of entity, and then other
types of tabled-items should be just "hung on" to the main structure. I'm
guessing that your core structure should be big and small elements of
objectives for work or work (projects, tasks, activities etc. ). This would
be a pyramid of 2 or 3 tables where the records are strictly this. And that
the other entitities (TC's & pieces of paper) should be merely attached to
this core structure. You'd probably to get rid of a current double meaning
of "document" (something to accomplish[e.g. completion of a piece of paper],
and a piece of paper) or split it into those two.

Just a few thoughts.