best practice for implementing object oriented class into database?

Question

I would like to implement the classes that I made into database,

I have a class like

Class Person {
 firstname ; 
 lastname; 
 email 
 etc 
}

Class Teacher extends Person {
  salary;
  graduatedFrom ; 
  etc ... 

  hasMany: [classess, experiences] ; 
}


Class Student extends Person{
   tuitionFee ; 
   parentName ; 
   etc ... 
}

I am going to use mysql database for this project, Should I create one table named Person and store all information there or should I create different table for each class (like Teacher, Student, etc) ?

note: i am going to implement this using YII Framework and MYSQL database.

Just a single `person` table will do and create a column with `role` to differentiate the users. — Shankar Narayana Damodaran, Mar 20 '14 at 07:28
Personally, i would create one table for each "kind" of person you have in your code. But you can all save them in one table too. If you have a lot of entries, more tables might speed up your application, e.g if you look for all teachers, you just have to look through the teacher table and not through all the others too. But if you only save a few entries, its ok to save them in one table. That wont affect performance very much. — Realitätsverlust, Mar 20 '14 at 07:31

score 1 · Answer 1 · answered Mar 20 '14 at 07:41

You would be better of creating Two tables one that stores all the person details, this table would have a foreign key which references to other table which would store the role details (eg teacher, Students)

This way would be advisable with respect to scalability as well, i.e. for example tomorrow if your application has 50 roles, you wont need to create 50 tables, you just would need to add a entry in the role table.

table person would be something like this (firstname, lastname, email, role_id,etc)

role_id here is the primary key of role table

role table would be something like this (role_id,role_name,role_desc,etc)

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

You can use a couple of different design patterns for this:

For more information on this (and other useful patterns) read Martin Fowler's Patterns of Enterprise Application Architecture.

For other examples:

edited May 23 '17 at 10:26

Community

1
1

answered Mar 20 '14 at 07:46

Bill Karwin

538,548
86
673
828

I know about that pattern but how to implement that as database table – nightingale2k1 Mar 20 '14 at 09:08

Akg · Answer 3 · 2014-03-20T08:24:44.070

I would create only one table Person with a column which will act as an identifier as to what the Person is (a Student, a Teacher etc...).

Advantage: we do not have to pay attention to sql joins while retrieving information as it would be fetching information from just one table! It will also give benefit when saving data. Think you do not have to save information to two different tables while storing data for Student.

In case, if you create separate table for each class ( and want to avoid sql joins) then you may end up creating lot of duplicate information in your database.

Less the number of the tables will produce a clean code.

Given the above case, it would still make sense, if you create a separate table for a special entity which you think should be stored separately (with a reference key to the main table) and do not want to clutter up the original table.

best practice for implementing object oriented class into database?

3 Answers3