Database design: multiple unary relationships on same entity

Question

I have three types of user to be stored in the database.

• school
• consultancy
• student (individuals)

The idea here is that a student may apply to school directly, or apply through the consultancy.

They all have same attributes and hence are stored in a user table. Conceptually each of these user types has a relationship with two other user type. The relationships are

1. Consultancy and student: 0..1 to 0..M
2. school and consultancy: 1..M to 0..M
3. School and student: 0..M to 0..M (1 student may have 0 school as they are not connected                                                            directly in case the application is sent through consultancy).

I need help forming these relationships between same entity, that is user, preferably with a demonstrating er diagram.

Answer 1

A table represents an application relationship among values and/or entities that are identified by them. "Relationship" is confusingly/confusedly also used to mean "foreign key".

In original ER modeling there are boxes and tables for entity types, diamonds for relationship types, and lines/FKs for entity type participation in a relationship type. A line is labeled with how many relationship instances/rows (instance/row) an entity instance/row can be simultaneously participating in. But different methods and diagram styles use symbols and lines differently.

You have decide what application entities and relationships you are interested in. (Unlike ERM, the relational model doesn't make the artificial distinction between entities & relationships.)

 Student(p) -- P identifies a student
 School(s) -- S identifies a school
 ...
 User(u, ...) -- U identifies a user and ...
 ...
 Representation(c, p, ...) -- consultancy C represents student P and ...
 Application(p, s, ...) -- student P has applied to school S and ...

A table holds the rows that make the predicate in its comment into a true statement. You need sufficient entities & relationships and corresponding tables to fully describe any situation that can arise.

You mention three relationship types. You have told us what their participant entity types and participation cardinalities. But you need to tell us the predicate of each, ie what each relationship is. Then in each situation that can arise, a given entity participates in a given relationship a certain numbers of times. You thereby determine a cardinality that describes the possibilities.

You should pick a particular reference and follow it.