Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I want to register all faculty of a university and they are in different fields and have different resume information. Here we have some common fields and some field specific fields. for example for CS and economic and medicine we may have:

CS:

ID:123
Name:Ali
Lname:Alipour
projects:p1, p3, p6
programming Skills:C[5 star],Java[5 star],JS[3 star],etc.
Research Interest: Network 

Medicine faculty:

ID:456
Name:Jafar
Lname:Jafarson
Hospital experience:hospital1 (2 year), Hopital2(1 month),...
Research Interest: Human body

Economic faculty:

ID:789
Name:Sadegh
Lname:Alipour
Company: Company1(company_name, add, tel,etc), Company2(company_name, add, tel,etc)
Research Interest: online currencies

We also may have students in the system:

ID:st_123
Name:mahdi
Lname:Mahdiyar
Major:CS
reg_date:2013.09.09

My first guess was to make a user_table and place all the common fields in it and then make field specific table for each major, CS_faculty_table, Medicine_faculty_table, Economic_faculty_table and student_table. Then thought about inheritance and polymorphism is OO programming language and if it is available in DB area. Then I saw accepted answer of this question that says you can have different fields inside a JSON file and store it in your DB as a BLOB field!

I'm new to DB filed and don't have enough experience in designing DBs so I wanted to know what's the best approach in this kind of situations?

I want a general answer. I don't know if the DB differs or not, and I don't care to change my DB to another open-source DB (sql or no-sql). I had PostgreSQL in mind.

share|improve this question
1  
Postgres supports table inheritance and a a "NoSQL" key/value store data type called hstore. –  a_horse_with_no_name Oct 23 '13 at 21:58
    
Bit of an FAQ really. EAV, hstore, json, XML. –  Craig Ringer Dec 23 '13 at 4:26
add comment

1 Answer 1

You did not mention whether this was an OLTP or Decision Support solution; lets assume OLTP.

Basically, you are asking about superclass (the set of common attributes) and subclass (the set of different attributes) by some classification.

In your example you have indicated that the sub-class can have many values. When the number of sub-classes are known and static, you could model it as follows:

enter image description here

The DDL for the User table is as follows:

CREATE TABLE User (
UserId INTEGER DEFAULT nextval('UserId_seq')  NOT NULL,
UserName CHARACTER VARYING(40)  NOT NULL,
UserLname CHARACTER VARYING(256),
UserType CHARACTER VARYING(10),
CONSTRAINT PK_User PRIMARY KEY (UserId));

The UserType attribute is new and is used to identify which subclass you are storing for a given user.

For your CS Subclass example

insert into user (UserName, UserLname, UserType)
       values  ('Ali', 'Alipour', 'CS'); 

-- > Assume that the assigned UserId is = 1 for the purposes of this example

insert into cs_user_projects (user_id, projectName) 
       values  (1, 'p1');
insert into cs_user_projects (user_id, projectName) 
       values  (1, 'p3');
insert into cs_user_projects (user_id, projectName) 
       values  (1, 'p6');
       ...

Now the application would know that it had to go to the CS_USER tables to get the information for CS users as follows:

select UserName
     , UserLname
     , ProjectName
  from user u
  join cs_user_projects p
    on u.userid = p.userid
 where u.UserType = 'CS';

If you want multiple sub-class information (User Programming Skills), you have to join to that sub-class table as well.

This is a straight forward, simple design; however a potential drawback is that it is a static design. If you wish to represent another sub-class (Engineers, Nurses, ...); then the sub-class tables must be added and the application changed to handle the new sub-class use cases.

In order to allow the model to take on new sub-classes without a change in the data model; you must think of the sub-class as a data object and model the relationship in the following way:

enter image description here

The User table (super-class) stays the same and you still have a classification described by UserType; but now you have an Attrib table to hold the values for each sub-class of user.

As an attribute can belong to many users and a user can have many attributes, the relationship between users and attributes is called a many-to-many relationship. This is resolved by addition of the UserAttrib table with primary keys from each parent table. The Attribute Role is provided for context of the relationship. Let's see how this would load for your CS example again:

    -- identical to before
    insert into user (UserName, UserLname, UserType)
       values  ('Ali', 'Alipour', 'CS');

    -- Load up the different projects 
    insert into Attrib ('p1');
    insert into Attrib ('p2');
    insert into Attrib ('p3');
    insert into Attrib ('p4');
    insert into Attrib ('p5');
    insert into Attrib ('p6');

    -- Assign only the appropriate projects to Ali
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p1',1,'PROJECT');
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p3',1,'PROJECT');
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p6',1,'PROJECT');

To query our little data model as before:

select UserName
     , UserLname
     , AttribValue Project
  from user1 u
  join UserAttrib a
    on u.UserId = a.UserId
 where a.AttribRole = 'PROJECT'
   and u.UserType   = 'CS';

Note that to return multiple attributes (e.g. also return User Programming Skills say), you would have to join to the UserAttrib table again. The benefit in this design is that if a new sub-class comes along (Engineers who specialize in Electrical, Chemical, Mechanical, ... sub-classifications). Then you would simply add data for these classifications and assignments as above without having to change the data-model to do so.

These are the classical, general solutions to super-class/sub-class design for ALL Databases that you mention in your question.

In the Postgres Database case, you should investigate the INHERITS clause of CREATE TABLE.

It allows a table (sub-class) to inherit the parent (super-class) table definition. In addition, you can alter the child (sub-class) tables to add additional attributes to achieve your desired effect of tables which would match on a sub-class by sub-class basis. Note that this implementation method is very similar to the first method we discussed (static, known sub-classes) and would not apply in all design criteria (e.g. dynamic and/or swiftly changing sub-classes).

share|improve this answer
    
Amazing answer. Thank you very much. These solution works fine but they seems to have few downside. first is performance and second is scalability. What if I want to let the users (system) add custom (not predefined) fields for different types of users? If I want to let users define new types and new fields for themselves it becomes harder. I think there is a NoSQL solution to this problem but I'm not familiar to NoSQL DBs yet. (Are you?). I think a Graph based DB can helps a lot in this kind of situations. –  sajjadG Oct 23 '13 at 20:48
    
In db's there is a trade-off between structure of data and performance. Extending the second answer to model Thing:Thing in a many to many relationship –  Guy Birkbeck Oct 24 '13 at 13:08
    
Sorry, rented fingers. Modelling a many to many, Thing:Thing does not scale and leaves the knowledge of how to negotiate the model up to the application. Using a Blob to store unstructured data has the same problem and invariably holds information that you want to access via SQL. NoSQL DB's exchange SQL for an API, it still doesn't solve the structure issue. As always, garbage-in, garbage-out. –  Guy Birkbeck Oct 24 '13 at 13:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.