Advanced Nesting (Hierarchy) in SQL

Question

I have read a lot of posts already on Hierarchy, Trees, composed keys and a lot more of issues in SQL but i can't find the info i need. I'm working with EF6 and SQL, C#.

The Case:

Let's Say that i have the following model. So the Application can be on each of these platforms (Or only on one or two of them. Application would be a base class and the OSX and windows and PlatformX would inherted him, if speaking from code.). But the Application can have for each platform reviews and a review may apply for multiple platforms. So i'm trying to model a database that doesn't need to separate the reviews in 3 different tables.

Has somebody got a good example on how to model this in the sql database? Or isn't this possible?

Some required queries would be: - Get all specified Types(OSX, winodws etc..) of an application? - Get all reviews of an application Type. - Get all Reviews of an application. - Get all applications by a review. (And could i then find out which type it is?)

Answer 1

As I understand Application is a base entity for OSX, Windows and PlatformX.

My first remark is, if your ~Value fields are platform specific fields for each component, like

-----------  ------------------
| Windows |  | OSX            |
-----------  -----------------|
| WinApi  |  | SupportsMobile |
| TempDir |  | WrittenLang    |
-----------  ------------------

Then you probably would like to make one table for Application and its primary key would be an identity field. Other entities are suggested to be separate tables, but would use the Application's primary key as their own, i.e. one to one relationship.

If the above is not true for your case and your tables are going to have Value1, Value2 and Value3 and the same data type, then I strongly recommend you to merge all classes into one table and add integer flag Platform. So you'll end up with only two tables which really simplify your work and will not truncate on functionality.

However resolution of your case with Review is the same for both cases. I advice you to connect Application table to Review table, this will save you a lot of time.

All your query questions: Yes you can do all of them.

EDIT

Ok, let me show you my vision.

Application (ApplicationId, Name, Price, Namespace)
OSX (ApplicationId*, OSXValue1, OSXValue2, OSXValue3)
Windows (ApplicationId*, WindowsValue1, WindowsValue2, WindowsValue3)
PlatformX (ApplicationId*, XValue1, XValue2, XValue3)
Review (ReviewId, ApplicationId*, FullName, ReviewText)

NB Please note that I used PascalCase for field names. This is a good convention for working with EF, as fields will map to the property names (by convention) and does not break the overall naming in you .NET application.

Here ApplicationId in the Application table is an identity field and is a primary key. In tables OSX, Windows and PlatformX you define ApplicationId field, define it as a primary key, and foreign key referencing ApplicationId field in Application table for each of them.

This is the way you store it.

As of Entity framework Define your classes as follows

public class Application {
    private ICollection<Review> _Reviews;
    public Int64 ApplicationId { get; set; }
    /*Other fields*/
    public Windows Windows { get; set; }
    public Osx Osx { get; set; }
    public PlatformX PlatformX { get; set; }
    public ICollection<Review> Reviews {
        get { return _Reviews = _Reviews ?? new List<Review>(); }
        set { _Reviews = value; }
    }
}

public class Osx {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey("Application")]
    public Int64 ApplicationId { get; set; }
    /*Other fields*/
    public Application Application { get; set; }
}

public class PlatformX {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey("Application")]
    public Int64 ApplicationId { get; set; }
    /*Other fields*/
    public Application Application { get; set; }
}

public class Windows {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey("Application")]
    public Int64 ApplicationId { get; set; }
    /*Other fields*/
    public Application Application { get; set; }
}

public class Review {
    public Int64 ReviewId { get; set; }
    public Int64 ApplicationId { get; set; }
    /*Other fields*/
    //Convention will catch up the ApplicationId field automatically
    public Application Application { get; set; }
}

Here you define some non obvious features by data annotations, e.g. the keys that does not match convention TableName + 'Id'. Further, you indicate that ApplicationId fields are not autogenerated using DatabaseGeneratedAttribute.

The following query gets the list of all applications with type Osx and Windows

var context = new DemoContext();
var res = context.Osx.Select(x=>x.Application).Union(context.Windows.Select(x=> x.Application));

This is a query of applications by review with its types

var res2 = context.Reviews.Select(x => new {
    x.Application,
    IsOsx = x.Application.Osx != null,
    IsWindows = x.Application.Windows != null,
    IsPlatformX = x.Application.PlatformX != null
});

You can find out the types according to the Boolean values in the object.