Derived classes with same base class and class specific ids

Question

I have three classes in my project. One base class and two derived classes.

public class Animal
{
    public long Id { get; set; }
    public DateTime Created { get; set; }
    public string Name { get; set; }
}

public class Cattle : Animal
{
    public int HornsCount { get; set; }
}

public class Fish : Animal
{
    public int FlippersCount { get; set; }
}

I want to store instances of these classes in database and since they have some same values (from base class) I thought that the best idea is to create Three tables: Animal (with PK Id), Cattle (with PK&FK to Animal.Id), Fish (with PK&FK to Animal.Id). But the problem occurs when I found out that each derived class has its own Id counter. So you can have Cattle.Id = 1 and at the same time Fish.Id = 1. Now I'm not sure how to proceed. Is one of these approaches correct?

1. Use just two tables (Cattle and Fish) with duplicate columns.
2. Use design specified above and add special column to Animal table which will be used for all Animals?
   a) This new Id is string code that is defined eg derived class in first letter and then is original Id of derived class.
   b) This new Id is auto increment database value. And move original Id column from Animal table to tables for derived classes.

1 downside - many duplicate columns -> have to change all tables for derived classes if I change Animal class.
2a downside - string as PK which actually doesn't mean anything in application.
2b downside - I have to insert into Animal table, then get auto Id of that record and then insert row to derived class table. Also there are identical original Id column in all tables for derived classes.

Or is there some better approach?

EDIT:
Question was put on hold as unclear so maybe some example data will help me explain my problem better. Here are five instances which I should save into database (I am not able to affect how these instances are created):

var c1 = new Cattle {Id = 1, Created = 2015-11-05 10:10, Name = "Cow1", HornsCount = 1}
var c2 = new Cattle {Id = 2, Created = 2015-11-05 12:12, Name = "Bull1", HornsCount = 2}
var f1 = new Fish {Id = 1, Created = 2015-11-04 05:05, Name = "Salmon1", FlippersCount = 5}
var f2 = new Fish {Id = 2, Created = 2015-11-05 06:06, Name = "Carp1", FlippersCount = 3}
var f3 = new Fish {Id = 3, Created = 2015-11-05 05:05, Name = "Trout1", FlippersCount = 2}  

As you can see one Id property is unique only for all Cattle instances and different one is unique only for Fish instances. I should be able to return the right result when the app wants for example: "Fish with Id = 2."

Answer 1

You can simply inherit the primary key from the Animals table in the Fish and Cattle table.

This example uses syntax that is SQL Server specific, however you should be able to convert this into SQLLite syntax:

CREATE TABLE dbo.Animals
(
    AnimalID INT NOT NULL
        CONSTRAINT PK_Animals
        PRIMARY KEY CLUSTERED
    , DateCreated DATETIME
);

CREATE TABLE dbo.Fish
(
    AnimalID INT NOT NULL
        CONSTRAINT FK_Fish_AnimalID
        FOREIGN KEY
        REFERENCES dbo.Animals(AnimalID)
    , FinCount INT NOT NULL
    , CONSTRAINT UQ_FISH
        UNIQUE (AnimalID)
);

This design prevents the need for surrogate key in the Fish table, while still ensuring uniqueness for the rows in that table by way of the unique constraint UQ_Fish.

If you want to see all the details about Fish, you'd use a query similar to:

SELECT Animals.AnimalID
    , Animals.DateCreated
    , Fish.FinCount
FROM dbo.Animals
    INNER JOIN dbo.Fish ON Animals.AnimalID = Fish.AnimalID;

Alternatively, if you are constrained by the classes built by the app, you can simply create the following structure:

CREATE TABLE dbo.Animals
(
    AnimalID INT NOT NULL
        CONSTRAINT PK_Animals
        PRIMARY KEY CLUSTERED
    , DateCreated DATETIME
);

CREATE TABLE dbo.Fish
(
    FishID INT NOT NULL
        CONSTRAINT PK_Fish
        PRIMARY KEY CLUSTERED
    , AnimalID INT NOT NULL
        CONSTRAINT FK_Fish_AnimalID
        FOREIGN KEY
        REFERENCES dbo.Animals(AnimalID)
    , FinCount INT NOT NULL
    , CONSTRAINT UQ_FISH
        UNIQUE (AnimalID)
);