Inheritance in database in sql server

Question

I am going to make a project in which i have

Task Entity and there can be several types of task and suppose all of them have different fields from each other . So how can i manage this kind of approach in sql ?

Like this

 Task ( abstract class ) 
 Change Engine Oil ( child class ) 
 Meet James ( child class )

so in above case each child class have different type of properties

Answer 1

You can think of having 2 tables to start with - Tasks and TaskEntityDetails.

Tasks table will have 2 columns - TaskID (Primary Key) and Task

TaskEntityDetails table will have 4 columns - ID (Primary Key), TaskID (Foreign Key), Field, Value.

Field column will store all the fields for a TaskID on rows instead of having them as separate columns. Value column will have the value corresponding to the field for each TaskID.

Your table will look like this:

ID TaskID Field Value
1 1 StartDate 2014年01月01日
2 1 EndDate 2014年01月31日
3 1 Contact ABC
4 2 StartDate 2014年01月01日
5 2 TaskName XYZ

You can then use PIVOT operator when you want to retrieve data. In this way, you can avoid having a number of NULL values in your table.

• Ancient: if we consider your solution then we are going to put almost all data in TaskEntityDetail table . is this a good practice ?
• Abhishek Chaudhary: What i suggested is the basic structure to start with. You can always go ahead and have a separate table for Fields as well and have their keys in the main table.
• Abhishek Chaudhary: Its on the same lines as the above example. Similar to TaskID, you will have FieldID (Foreign Key) in place of Field and there will be a separate table Fields having 2 columns - FieldID (Primary Key) and Field Name. The table mentioned above will remain as your master data table, similar to fact tables in data warehouse.
• Ancient: thanks , i would prefer first one as second approach will increase a join for each query of task . Anyway thanks