Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I am getting a circular ERD for my database

Status
Not open for further replies.

LLudlow

Programmer
Oct 5, 2001
186
CA


I am trying to create a database to keep track of company grievances. There are 12 divisions and 5 consultants. Each Division has only one consultant assigned to it, but each consultant can be in charge of more than one Division. The consultant then handles all of the grievances for that division. Each division can have many grievances. Also, the consultants can only update data about their divisional grievances but they can view the grievances from all the Divisions. The ERD that I keep coming up with is a circular one! I am using four tables: tblDivision, tblConsultant, tblUserAccess, tblGrievanceInfo. If anyone has any suggestions please let me know. Also if this is not clear also let me know and I will try to be clearer.

Thank you
 
I would think the entire schema would be necessary to 'un-ravel' the gordian knot, not just the definition of it.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over
 

I'm making a guess about your design.

tblDivision
Primary Key - DivID
ForeignKey - ConsultantID

tblConsultant
Primary Key - ConsultantID

tblGrievanceInfo
Primary Key - GrievanceID
ForeignKey - DivID

tblUserAccess - I'm unclear of the purpose of this table
PrimaryKey - UserAccessID

Relationship of tblConsultant to tblDivision is 1 to many.
Relationship of tblDivision to tblGrievanceInfo is 1 to many.
Relationship of tblConsultant to tblGrievanceInfo is 1 to many. (tblConsultant --> tblDivision --> tblGrievanceInfo)

In this design, I see nothing circular. What am I missing? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Thanks Terry L. Broadbent for the help. I am using the tblAccess to limit the consultants ability to update certain records based upon divisionId. The table has 3 fields: DivID, ConID, AllowUpdate. The AllowUpdate is a Yes/No field. Am I making this too complicated for myself?
 

I don't think you are making things too complicated. With the additional information, I would remove the foreign key that I placed on tblDivision and use tblAccess to identify the relationship of consultants to division.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Thanks again for the help Terry. So it should go like this..

tblConsultant tblAccess tblDivision tblGrievance
ConID ========>ConID GrievanceID
password DivID====>DivID=======>DivID
 

Yes, the relationship from tblConsultant to tblGrievance goes through the other tables as you illustrated. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top