To kind of illustrate what I mean, say that you have a group of students and a group of professors in some kind of a traineeship. Every professor is to mentor one student and every student has to be mentored by one and only one professor. Thus, we have an obligatory one-to-one relationship. Let’s furthermore say that you every student has a studentID (primary key), name and a phone number, and every professor has a professorID, name and a phone number. In this scenario, would it make sense to have a single table where you list all the attributes for a single entity (that is, studentID, their name, phone number, the ID of their mentor and then their name and phone number)? My first thought is that this kind of scheme breaks the 3rd normalization form, as the professor’s name and phone number are dependent on the professorID, and that perhaps splitting the table into two, having a Professor table that contains the professor’s name and phone number and putting the professorID into the student table as a foreign key is the proper way of going about it. At the same time, since it’s an obligatory one-to-one relationship, would that even matter?
user24881603 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
First, let me get this out of the way: concepts like student & teacher are roles for a person, not subclasses of person — one person can be both a student and a teacher; student & teacher are ephemeral roles compared with being a person for life.
Next, I think of 1:1 or 1:N relationships as an optimization on N:M relationships, which I therefore start with and only perform optimization when convinced that it is appropriate and that it matters for performance. (Sure we can require that N>=1 and also M>=1.)
A person can have multiple phone numbers, multiple addresses, multiple contact methods, multiple degrees, multiple mentors, multiple mentees, etc..
The mentor-mentee relationship can be formalized as an entity in it own right, which may carry additional information regarding that relationship (an agreement id#, a start date, a subject matter, preferred methods of contact, schedule, an end date, etc..).
If there always is a strict 1:1 relationship between teacher and student, then it is not a violation of 3rd normal form to have both in the same table.
But is this really realistic? It seems likely there might be exceptions. For example a teacher retiring causing a new teacher to take over the mentee, a student dropping out causing a teacher to be without a mentee for a while etc. Usually you would also want to keep historical records over which teacher have mentored which students in the past, but this is also not possible with a strict 1:1 relationship.
The student and the teacher are both persons, name and phone number are attributes of a person. The relation there are two one-to-one relations person-student-teacher, person-teacher. Adding person to the model makes it possible to have a person student and teacher in the same time. To be thorough the model could be further normalised and represent phone number with its own representation with a one-to-one relation between student-phone number and profesor-phone number resulting in different relation for students and teachers…
- teacher
/
person-student
- phone number
person-teacher-phone number
…making possible for each person to be student and teacher in the same time and have two phone number, a student phone number and a teacher phone number.
user453105 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
No not really.
If you have an enforced 1:1 relationship between two tables it’s just the same as adding more columns to the first table.
You could have a 1:1/0 relationship where you want optional columns. eg
Person
Id, type
1, professor
2, student
Professor
Id, role
1, chemistry
Student
Id, Grade
2, B+
It is true that with an obligatory one-to-one relationship, there is no structural reason to require separate tables. You can simply add all columns in one table, and that is sufficient to store the data.
There can however be other reasons in practice to split the tables.
One is to conceptually organise what would otherwise be a large number of columns in one table. This doesn’t apply to your example as given, but I have certainly seen examples of tables that had too much variety of data which was conceptually disparate.
Another is to control permissions/access to the tables differently.
A third can be to control which columns are packed together in the underlying physical storage – this can sometimes have performance implications due to access or locking patterns. For example, splitting the columns across multiple tables, means that a single row lock now only locks some of the columns, and columns falling into different tables can be updated concurrently and independently.
A fourth is when readapting the database, and for whatever reason adding columns directly to an existing table is considered too disruptive, so another supplementary table is bolted on.
I wouldn’t say any of these cases occur very commonly, but it does demonstrate that there can be many practical, performance, maintenance and management concerns involved in real database work, which can go well beyond designing the basic relational model for storing the data.
You could try something like this.
Raja is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.