CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2sql-server-2012
Ancient
Ancient

Reputation: 3057

Inheritance in database in sql server

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

Upvotes: 0

Views: 276

Answers (1)

Abhishek Chaudhary
Abhishek Chaudhary

Reputation: 807

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.

Upvotes: 1

Related Questions