Geek Answers Handbook

Database Inheritance Modeling

To assign a database, I need to model the system for the school. Part of the requirements is to model information for staff, students and parents.

In the UML class diagram, I modeled this because the three classes are subtypes of the human type. This is due to the fact that all of them will require information, among other things, address data.

My question is: how can I simulate this in a database (mysql)?

Thoughts so far look like this:

  • Create a monolithic person table that contains all the information for each type and will have many null values ​​depending on what type is stored. (I doubt that this will be in good agreement with the lecturer if I do not argue this case very convincingly).
  • A table of people with three foreign keys that refer to subtypes, but two of which will be null - in fact, I'm not even sure if this makes sense or is it possible?

  • According to this wikipage about django, you can implement the primary key in subtypes as follows:

    "id" integer NOT NULL PRIMARY KEY REFERENCES "supertype" ("id")
  • Something else I didn’t think about ...

So, for those who previously modeled inheritance in the database; how did you do this? Which method do you recommend and why?

Links to articles / blog posts or previous questions are more than welcome.

Thank you for your time!

UPDATE

Well thanks for all the answers. I already had a separate address table, so that is not a problem.

Greetings

Adam

+5
8

4 , , . , , ( ).

, , (.. , ).

EDIT:

HLGM, , . ( - - , ..).

+5

, , , , , ( , , ), - .

NHibernate, . .

:

  • 1) , "". , . () , .
  • 2) , , , ( LIL ).

  • 3) "" . . , . Employee PersonId, :

    employee.id, person.name employee.personId = person.personId

, .

+2

# 3.

- , - . () ( nulls.)

django (PRIMARY KEY... ...) FOREIGN KEY.

+2

", , , ? ?"

1 3 . .

1) - ? FK .

2) - ? .

. .

, - FK-to-parent .

( ). . .

+1

OO .

, , , , OO ( ).

, , .

, , , .

, , "", "". - , .

, , , ...

0

"" , , № 3:

Person
PersonId Name Address1 Address2 City Country

Student
PersonId StudentId GPA Year ..

Staff
PersonId StaffId Salary ..

Parent
PersonId ParentId ParentType EmergencyContactNumber ..

PersonId , .

, , . , .

0

- , , , , , .

last_name, Middle_name, first_name, Name_suffix (, jr.), . , last_name! , , .

, . , : http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

0

:

CREATE  TABLE Person(PersonID int primary key, Name varchar ... etc ...)

Sub :

CREATE  TABLE IF NOT EXISTS Staffs(StaffId INT NOT NULL ,
  PRIMARY KEY (StaffId) ,  
  CONSTRAINT FK_StaffId FOREIGN KEY (StaffId) REFERENCES Person(PersonId)
  )

CREATE  TABLE IF NOT EXISTS Students(StudentId INT NOT NULL ,
  PRIMARY KEY (StudentId) ,  
  CONSTRAINT FK_StudentId FOREIGN KEY (StudentId) REFERENCES Person(PersonId)
  )

CREATE  TABLE IF NOT EXISTS Parents(PersonID INT NOT NULL ,
  PRIMARY KEY (PersonID ) ,  
  CONSTRAINT FK_PersonID  FOREIGN KEY (PersonID ) REFERENCES Person(PersonId)
  )

, , :

  • Person , . . , Person, , . , Student Person.
  • " null", , . . Student Person.
0

More articles:


All Articles