Return to Answer

I want to answer just one facet of your initial question, the feeling you have that this question has been asked multiple times. The answer is that yes it has but the question has been phrased in a very different way.

What you are basically seeking is information about organizations and persons that are connected to each other (maybe the person works for the organization). You have the further requirement that you are only interested in persons who are admins or moderators, or both.

When it's phrased this way, the question has been asked in this place dozens of times. People are always interested in information about members of one or more subclasses of some more general superclass. In some cases, the superclass is vehicles and the subclasses are cars, motorcycles, and trucks (lorries). In some cases, the superclass is pets, and the subclasses are dogs, cats, and birds.
In the case of a college, the superclass might be persons, and the subclasses might be students, teachers, and staff. In general, people want to combine the information about subclasses with other information generally using relational JOIN to combine data.

Other answers have given you a way to phrase an SQL query that will get you this information given that you have stored the information in three tables: the organization table, the admin-organization table, and the moderator-organization table. I have no improvements to offer over those other answers.

What I would like to suggest is a possible alternative table design, based on five tables. The five tables are: organization, admin, moderator, person, and person-organization. Organization is an entity table with data about organizations. Admin is an entity table with data about admins. Moderator is an entity with data about moderators.

Person is yet another entity table with data about persons. Data like first name and last name can be removed from the admin and moderator table because these attributes are common to persons, and can be stored in the person table instead. Organization-person is a relationship table with data about the relationship between persons and organizations including (at least) personId and organizationId.

The relationship between the admin table and the person table is what is known as an IS-A relationship. The same can be said about the moderator table and the person table.

IS-A relationships are often overlooked in introductory database courses, even though they occur all the time in the real world. Unfortunately, relational modeling techniques don't make this kind of situation as simple as we would wish. In object modeling, the superclass-subclass model takes care of the whole case, and inheritance does a lot of work for the modeler.

I'm going to refer you to three buzzwords that will give you a handle on this design problem. They are "Single Table Inheritance", "Class Table Inheritance", and "Shared Primary Key". Depending on your case, you might choose single table inheritance over class table inheritance. I have chosen class table inheritance in the answer given above.

If you choose class table inheritance, I recommend the additional technique of shared primary key. Here, individual rows in the admin table are identified by personId instead of inventing a new ID for the admin table. The same goes for the moderators table. This makes needed joins simple, easy, and fast.

If you do searches of those three buzzwords, you are going to come up with lots of good articles about dealing with this kind of case. Maybe some day in the future, the people who teach courses about databases will incorporate this stuff into their subject.

Is it a bad practice to have several mutually exclusive one-to-one relationships?

I want to answer just one facet of your initial question, the feeling you have that this question has been asked multiple times. The answer is that yes it has but the question has been phrased in a very different way.

What you are basically seeking is information about organizations and persons that are connected to each other (maybe the person works for the organization). You have the further requirement that you are only interested in persons who are admins or moderators, or both.

When it's phrased this way, the question has been asked in this place dozens of times. People are always interested in information about members of one or more subclasses of some more general superclass. In some cases, the superclass is vehicles and the subclasses are cars, motorcycles, and trucks (lorries). In some cases, the superclass is pets, and the subclasses are dogs, cats, and birds.
In the case of a college, the superclass might be persons, and the subclasses might be students, teachers, and staff. In general, people want to combine the information about subclasses with other information generally using relational JOIN to combine data.

Other answers have given you a way to phrase an SQL query that will get you this information given that you have stored the information in three tables: the organization table, the admin-organization table, and the moderator-organization table. I have no improvements to offer over those other answers.

What I would like to suggest is a possible alternative table design, based on five tables. The five tables are: organization, admin, moderator, person, and person-organization. Organization is an entity table with data about organizations. Admin is an entity table with data about admins. Moderator is an entity with data about moderators.

Person is yet another entity table with data about persons. Data like first name and last name can be removed from the admin and moderator table because these attributes are common to persons, and can be stored in the person table instead. Organization-person is a relationship table with data about the relationship between persons and organizations including (at least) personId and organizationId.

The relationship between the admin table and the person table is what is known as an IS-A relationship. The same can be said about the moderator table and the person table.

IS-A relationships are often overlooked in introductory database courses, even though they occur all the time in the real world. Unfortunately, relational modeling techniques don't make this kind of situation as simple as we would wish. In object modeling, the superclass-subclass model takes care of the whole case, and inheritance does a lot of work for the modeler.

I'm going to refer you to three buzzwords that will give you a handle on this design problem. They are Single Table Inheritance, Class Table Inheritance, and Shared Primary Key. Depending on your case, you might choose single table inheritance over class table inheritance. I have chosen class table inheritance in the answer given above.

If you choose class table inheritance, I recommend the additional technique of shared primary key. Here, individual rows in the admin table are identified by personId instead of inventing a new ID for the admin table. The same goes for the moderators table. This makes needed joins simple, easy, and fast.

If you do searches of those three buzzwords, you are going to come up with lots of good articles about dealing with this kind of case. Maybe some day in the future, the people who teach courses about databases will incorporate this stuff into their subject.

Here is a link to one of the earlier questions, with an answer.

Is it a bad practice to have several mutually exclusive one-to-one relationships?

I want to answer just one facet of your initial question, the feeling you have that this question has been asked multiple times. The answer is that yes it has but the question has been phrased in a very different way.

What you are basically seeking is information about organizations and persons that are connected to each other (maybe the person works for the organization). You have the further requirement that you are only interested in persons who are admins or moderators, or both.

When it's phrased this way, the question has been asked in this place dozens of times. People are always interested in information about members of one or more subclasses of some more general superclass. In some cases, the superclass is vehicles and the subclasses are cars and trucks (lorries). In some cases, the superclass is pets, and the subclasses are dogs, cats, and birds. And, in general, people want to combine the information about subclasses with other information generally using relational JOIN to combine data.

Other answers have given you a way to phrase an SQL query that will get you this information given that you have stored the information in three tables: the organization table, the admin-organization table, and the moderator-organization table. I have no improvements to offer over those other answers.

What I would like to suggest is a possible alternative table design, based on five tables. The five tables are: organization, admin, moderator, person, and person-organization. Organization is an entity table with data about organizations. Admin is an entity table with data about admins. Moderator is an entity with data about moderators.

Person is yet another entity table with data about persons. Data like first name and last name can be removed from the admin and moderator table because these attributes are common to persons, and can be stored in the person table instead. Organization-person is a relationship table with data about the relationship between persons and organizations including (at least) personId and organizationId.

The relationship between the admin table and the person table is what is known as an IS-A relationship. The same can be said about the moderator table and the person table.

IS-A relationships are often overlooked in introductory database courses, even though they occur all the time in the real world. Unfortunately, relational modeling techniques don't make this kind of situation as simple as we would wish. In object modeling, the superclass-subclass model takes care of the whole case, and inheritance does a lot of work for the modeler.

I'm going to refer you to three buzzwords that will give you a handle on this design problem. They are "Single Table Inheritance", "Class Table Inheritance", and "Shared Primary Key". Depending on your case, you might choose single table inheritance over class table inheritance. I have chosen class table inheritance in the answer given above.

If you choose class table inheritance, I recommend the additional technique of shared primary key. Here, individual rows in the admin table are identified by personId instead of inventing a new ID for the admin table. The same goes for the moderators table. This makes needed joins simple, easy, and fast.

If you do searches of those three buzzwords, you are going to come up with lots of good articles about dealing with this kind of case. Maybe some day in the future, the people who teach courses about databases will incorporate this stuff into their subject.

Is it a bad practice to have several mutually exclusive one-to-one relationships?

I want to answer just one facet of your initial question, the feeling you have that this question has been asked multiple times. The answer is that yes it has but the question has been phrased in a very different way.

What you are basically seeking is information about organizations and persons that are connected to each other (maybe the person works for the organization). You have the further requirement that you are only interested in persons who are admins or moderators, or both.

When it's phrased this way, the question has been asked in this place dozens of times. People are always interested in information about members of one or more subclasses of some more general superclass. In some cases, the superclass is vehicles and the subclasses are cars, motorcycles, and trucks (lorries). In some cases, the superclass is pets, and the subclasses are dogs, cats, and birds.
In the case of a college, the superclass might be persons, and the subclasses might be students, teachers, and staff. In general, people want to combine the information about subclasses with other information generally using relational JOIN to combine data.

Other answers have given you a way to phrase an SQL query that will get you this information given that you have stored the information in three tables: the organization table, the admin-organization table, and the moderator-organization table. I have no improvements to offer over those other answers.

What I would like to suggest is a possible alternative table design, based on five tables. The five tables are: organization, admin, moderator, person, and person-organization. Organization is an entity table with data about organizations. Admin is an entity table with data about admins. Moderator is an entity with data about moderators.

Person is yet another entity table with data about persons. Data like first name and last name can be removed from the admin and moderator table because these attributes are common to persons, and can be stored in the person table instead. Organization-person is a relationship table with data about the relationship between persons and organizations including (at least) personId and organizationId.

The relationship between the admin table and the person table is what is known as an IS-A relationship. The same can be said about the moderator table and the person table.

IS-A relationships are often overlooked in introductory database courses, even though they occur all the time in the real world. Unfortunately, relational modeling techniques don't make this kind of situation as simple as we would wish. In object modeling, the superclass-subclass model takes care of the whole case, and inheritance does a lot of work for the modeler.

I'm going to refer you to three buzzwords that will give you a handle on this design problem. They are "Single Table Inheritance", "Class Table Inheritance", and "Shared Primary Key". Depending on your case, you might choose single table inheritance over class table inheritance. I have chosen class table inheritance in the answer given above.

If you choose class table inheritance, I recommend the additional technique of shared primary key. Here, individual rows in the admin table are identified by personId instead of inventing a new ID for the admin table. The same goes for the moderators table. This makes needed joins simple, easy, and fast.

If you do searches of those three buzzwords, you are going to come up with lots of good articles about dealing with this kind of case. Maybe some day in the future, the people who teach courses about databases will incorporate this stuff into their subject.

Is it a bad practice to have several mutually exclusive one-to-one relationships?

I want to answer just one facet of your initial question, the feeling you have that this question has been asked multiple times. The answer is that yes it has but the question has been phrased in a very different way.

What you are basically seeking is information about organizations and persons that are connected to each other (maybe the person works for the organization). You have the further requirement that you are only interested in persons who are admins or moderators, or both.

When it's phrased this way, the question has been asked in this place dozens of times. People are always interested in information about members of one or more subclasses of some more general superclass. In some cases, the superclass is vehicles and the subclasses are cars and trucks (lorries). In some cases, the superclass is pets, and the subclasses are dogs, cats, and birds. And, in general, people want to combine the information about subclasses with other information generally using relational JOIN to combine data.

Other answers have given you a way to phrase an SQL query that will get you this information given that you have stored the information in three tables: the organization table, the admin-organization table, and the moderator-organization table. I have no improvements to offer over those other answers.

What I would like to suggest is a possible alternative table design, based on five tables. The five tables are: organization, admin, moderator, person, and person-organization. Organization is an entity table with data about organizations. Admin is an entity table with data about admins. Moderator is an entity with data about moderators.

Person is yet another entity table with data about persons. Data like first name and last name can be removed from the admin and moderator table because these attributes are common to persons, and can be stored in the person table instead. Organization-person is a relationship table with data about the relationship between persons and organizations including (at least) personId and organizationId.

The relationship between the admin table and the person table is what is known as an IS-A relationship. The same can be said about the moderator table and the person table.

IS-A relationships are often overlooked in introductory database courses, even though they occur all the time in the real world. Unfortunately, relational modeling techniques don't make this kind of situation as simple as we would wish. In object modeling, the superclass-subclass model takes care of the whole case, and inheritance does a lot of work for the modeler.

I'm going to refer you to three buzzwords that will give you a handle on this design problem. They are "Single Table Inheritance", "Class Table Inheritance", and "Shared Primary Key". Depending on your case, you might choose single table inheritance over class table inheritance. I have chosen class table inheritance in the answer given above.

If you choose class table inheritance, I recommend the additional technique of shared primary key. Here, individual rows in the admin table are identified by personId instead of inventing a new ID for the admin table. The same goes for the moderators table. This makes needed joins simple, easy, and fast.

If you do searches of those three buzzwords, you are going to come up with lots of good articles about dealing with this kind of case. Maybe some day in the future, the people who teach courses about databases will incorporate this stuff into their subject.

I want to answer just one facet of your initial question, the feeling you have that this question has been asked multiple times. The answer is that yes it has but the question has been phrased in a very different way.

What you are basically seeking is information about organizations and persons that are connected to each other (maybe the person works for the organization). You have the further requirement that you are only interested in persons who are admins or moderators, or both.

When it's phrased this way, the question has been asked in this place dozens of times. People are always interested in information about members of one or more subclasses of some more general superclass. In some cases, the superclass is vehicles and the subclasses are cars and trucks (lorries). In some cases, the superclass is pets, and the subclasses are dogs, cats, and birds. And, in general, people want to combine the information about subclasses with other information generally using relational JOIN to combine data.

Other answers have given you a way to phrase an SQL query that will get you this information given that you have stored the information in three tables: the organization table, the admin-organization table, and the moderator-organization table. I have no improvements to offer over those other answers.

What I would like to suggest is a possible alternative table design, based on five tables. The five tables are: organization, admin, moderator, person, and person-organization. Organization is an entity table with data about organizations. Admin is an entity table with data about admins. Moderator is an entity with data about moderators.

Person is yet another entity table with data about persons. Data like first name and last name can be removed from the admin and moderator table because these attributes are common to persons, and can be stored in the person table instead. Organization-person is a relationship table with data about the relationship between persons and organizations including (at least) personId and organizationId.

The relationship between the admin table and the person table is what is known as an IS-A relationship. The same can be said about the moderator table and the person table.

IS-A relationships are often overlooked in introductory database courses, even though they occur all the time in the real world. Unfortunately, relational modeling techniques don't make this kind of situation as simple as we would wish. In object modeling, the superclass-subclass model takes care of the whole case, and inheritance does a lot of work for the modeler.

I'm going to refer you to three buzzwords that will give you a handle on this design problem. They are "Single Table Inheritance", "Class Table Inheritance", and "Shared Primary Key". Depending on your case, you might choose single table inheritance over class table inheritance. I have chosen class table inheritance in the answer given above.

If you choose class table inheritance, I recommend the additional technique of shared primary key. Here, individual rows in the admin table are identified by personId instead of inventing a new ID for the admin table. The same goes for the moderators table. This makes needed joins simple, easy, and fast.

If you do searches of those three buzzwords, you are going to come up with lots of good articles about dealing with this kind of case. Maybe some day in the future, the people who teach courses about databases will incorporate this stuff into their subject.

Is it a bad practice to have several mutually exclusive one-to-one relationships?