-1

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.

Sirjan Sharma
  • 79
  • 1
  • 5
  • @philipxy: The design wise, I'm afraid I'm at the starting point. I hope what you really meant was the documents. I have the application prototype and the SRS. And I couldn't help notice the quotes around "forming relationships". Please let me know if I should have use some other terms. I'm only a learning student and so help in every little thing is highly appreciated. – Sirjan Sharma Jun 29 '16 at 17:08
  • It's not clear what "forming these relationships between same entity, that is user" means. Maybe by "forming" you just mean designing. I suppose you mean deciding on columns, candidate keys, foreign keys, etc but please just say what you are trying to deliver. Please give your best attempt at deliverables. Also there are many modeling & diagramming methods, so which ones are you using? PS Your question is essentially asking for design/diagram chapters of a textbook. That is too broad for a question here. Find a textbook as reference. (Many are online.) – philipxy Jun 29 '16 at 23:26

1 Answers1

0

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83