Best way to inherit with Database

Question

lets assume I have these classes

public class Animal {
    public int AnimalID;
    public bool HasFeet;
}

public class Dog {
    public int AnimalID;
    public int DogID;
    public bool HasOwner;
}

public class Cat {
    public int AnimalID;
    public int CatID;
    public bool LovesSleeping;
}

These classes are mirroring a Database Table. What would be the best solution to get e.g. a Cat when only having the AnimalID? Checking the Table Dog if there is one with AnimalID I am looking for and then do the same with Table Cat would be totally ineffective.

How would the classes and the database look with the best solution?

Thanks for your help

EDIT:

After some comments I saw I could implement it like that:

public class Animal {
    public int AnimalID;
    public bool HasFeet;
}

public class Dog : Animal {
    public bool HasOwner;
}

public class Cat : Animal {
    public bool LovesSleeping;
}

But now I wonder if I only have the AnimalID, how would I get the Cat with Linq2Sql?

Normally I would do something like

public static Animal Get(DataContext db, int animalID) {

    IQueryable<Animal> query =
    from animal in db.GetTable<Animal>()
    where animal.AnimalID == animalID
    select animal;

    return query.Single();
}

This will return me an Animal, but not a Cat I guess, right? So how would I get the Cat if I don't know, the AnimalID is a cat?

Dog and Cat should inherit from Animal, they don't need separate `AnimalIDs`and you don't need DogID/CatID since those the the AnimalIds. — Tim Schmelter, Jan 17 '17 at 13:48
Are you asking for a database structure here? It's not clear. — DavidG, Jan 17 '17 at 13:48
If this classes mirrors tables on DB, so you can use only one table Animals with AnimalType column, it would be better in this situation — kgzdev, Jan 17 '17 at 13:51
I edited the code a little to make it clearer what I mean. @IkramTurgunbaev if Dog and Cat do have different properties, your solution would not work anymore. But normally i would do this too — DirtyNative, Jan 17 '17 at 14:03
Unfortunately I voted to close as unclear, but this is really a duplicate of http://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database — DavidG, Jan 17 '17 at 14:11
Possible duplicate of [How do you effectively model inheritance in a database?](http://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database) — Sean Lange, Jan 17 '17 at 14:21

score 1 · Accepted Answer · answered Jan 17 '17 at 20:06

By modeling your data like this you are creating an Is-A relationship. But as you already said you won't know which table the Id would belong.

public class Animal {
    public int AnimalID;
    public bool HasFeet;
}

public class Dog : Animal {
    public bool HasOwner;
}

public class Cat : Animal {
    public bool LovesSleeping;
}

Hence you can have tables like below for that relationship:

table Animal
------------
int Id (PK)
int Type
bit HasFeet

table Dog
---------
int Id (PK)
int AnimalId (foreign key to Animal.Id)
bool HasOwner

table Cat
---------
int Id (PK)
int AnimalId (foreign key Animal.Id)
bool LovesSleeping

When you apply this logic though it becomes a Has-A logic, but this doesn't hurt your requirement. So you can make a call only to Animal table, get the Type out of it, and make the next query against the related table to get the correct type of animal. This way you are still making 2 queries, but it won't increase when you add new types to your applications. Hence, this design also supports the Open-Closed principal.

Thank you, this seems a good starting point. Though I hoped there would be a solution without implementing a TypeID in the Animal Class, but this will work. Again: Thank you — DirtyNative, Jan 18 '17 at 07:22

Best way to inherit with Database

EDIT:

1 Answers1