How would you accomplish these requirements?
Faculty has many Members. Faculty belongs to a Council. Now - in the Council there is one representative member from corresponding Faculty. This part creates a circular relationship (see image). Where is the problem in my design approach?
Leave out the memberId and facultyId fields from Council. Add a councilId field to Faculty and a facultyId field to Members.
The database will look like this:
You can find the members that are in the council using the following query:
SELECT
*
FROM
Council AS c
INNER JOIN
Faculty AS f
ON
f.councilId = c.councilId
INNER JOIN
Members AS m
ON
m.facultyId = f.facultyId
I would keep the id + name in each table (so facultyid+faculty name, memberid+membername and councilid+councilname).
Then you can have link tables like: faculty_members with facultyid, memberid, a table for faculty_council with facultyid and councilid as columns and one council_members with memberid and councilid as columns.
"Where is the problem in my design approach?"
Nowhere. You're given the rules of the business, and you've modeled them appropriately.
Presumably (presumably because you haven't stated anything explicitly to this effect), the "problem" you perceive is that there is a rule to the effect that the one representative member of a council, must be a member of the faculty that the council belongs to. That is, the FacultyID in Council must be equal to the FacultyID that appears in Member for the row that has the concerned memberID. That is, the (MemberID FacultyID) pairs from council must be a subset of the (MemberID FacultyID) pairs from Member.
A Foreign key declaration on Council can achieve that, but you need (MemberID FacultyID) to be known as a unique key on Member. You'd need to add this (extra, redundant) UNIQUE declaration to Member, and then things should work.
