Please review and critique my table design

[pmcleod]

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.

 

[KenHigg]

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?

 

[pmcleod]

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.

 

[Ian Mac]

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,

 

[pmcleod]

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.