Is it bad to have a cyclical relationship?

Question

I have 5 tables that have ended up with a cyclical relationship and I don't think that is ok.

I School is in one County. A Distict has many Counties. A Teacher can teach in many Districts.

STUDENTRECORD - RecordId, TeacherId, CountyCode

COUNTY - CountyCode, CountyName, DistrictId

DISTRICT - DistrictId, DistrictName

TEACHER - TeacherId

TEACHERDISTRICT - DistrictId, TeacherId

So the relationship goes from the StudentRecord, teacherid, to the district, to the county and back to the studentrecord.

I have built the database, populated the teacher, district and county tables, but I have not tried to insert a StudentRecord yet. I just know I have not seen a db design with a circle relationship before and I think there is a reason for that. I assume I have created a bad design.

Is a cyclical relationship a bad thing in database design? If so, why?Thoughts on fixing?

EDIT: I forgot the DistrictId in the COUNTY table, which completes the circle. Sorry for the incomplete question. Here is a picture:

Answer 1

Here is the original model -- at least as worded in the question.

Though there is nothing cyclical here it does have few problems. For example, the model has no relationship between County and District, although you do state that "District has many Counties". It is also possible to have StudentRecord {RecordId, TeacherId, CountyId} such that that teacher does not teach in the district the county is located in.

---

One possible fix may look like this, though I do not think this would be complete solution, but it may be a good starting point. I will use predicates -- marked by | -- and constraints (italics) to describe the model. Predicates map to tables and constraints to PK,AK, FK.

| District (DistrictId) exists.

District is identified by DistrictId.

| County (CountyCode) is located in district (DistrictId).

County is identified by CountyCode.

Each county is located in exactly one district; for each district that district may have more than one county.

If a county is locaeted in a district, then that district must exist.

| Teacher (TeacherId) exists.

Teacher is identified by TeacherId.

| Teacher (TeacherId) is licensed to teach in district (DistrictId).

For each teacher, that teacher may be licensed to teach in more than one district.

For each district, more than one teacher may be licensed to teach in that district.

If a teacher is licensed to teach in a district, then that teacher must exist.

If a teacher is licensed to teach in a district, then that district must exist.

| School (SchoolId) is located in county (CountyCode), district (DistrictId).

School is identified by SchoolId.

For each school, that school is located in exactly one county, district.

For each county, district; more than one school may be located in that county, district.

If a school is located in a county, district; then that county must be located in that district.

| Teacher (TeacherId) teaches in school (SchoolId), in district (DistrictId).

Each teacher may teach in more than one school in a district.

For each school in a district it is possible that more than one teacher teaches in that school.

If a teacher teaches in school in a district, then that school must be located in that district.

If a teacher teaches in school in a district, then that teacher must be licensed to teach in that district.

| Student (StudentId) exists.

Student is identified by StudentId.

| Student (StudentId) attends school (SchoolId).

Each student may attend more than one school, for each school is is possible that more than one student attends that school.

If a student attends school, then that student must exist.

If a student attends school, then that school must exist.

| Student record (RecordId) was generated for student (StudentId) by teacher (TeacherID) in school (SchoolID).

Student record is identified by RecordId.

If a student record was generated for a student by a teacher in a school, then that student must attend that school.

If a student record was generated for a student by a teacher in a school, then that teacher must teach in that school.

Answer 2

It is ok to have relationships with everyone. As long as there's a reason for that, meaning, as long as the concepts have an independent relationship amongst themselves. If your student lives in a county and you want to keep that info; and their teacher is a teacher in a different county, that's ok. But if the student must only have teachers in their own county, it's not ok.

You must be thinking about normalization. Check "Database normalization" for more details, might some of your questions as to why tables are designed in some ways.