0

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."

  • 1
    Why have a primary key on the fish table that is different from the primary key of the animal table? Use the same key, as in your 2b. – Max Vernon Nov 4 '15 at 20:03
  • @MaxVernon Unfortunately I can't affect the way how instances of those classes are created. I just have to store them in database and then read them again when they are needed. App just "tells" me that it wants fish with Id = 2 or something like that. – Artholl Nov 4 '15 at 20:25
  • Another option: use animal_type, id for the PK in Animal. – mustaccio Nov 5 '15 at 21:23
  • @mustaccio thank you for your opinion. It looks like the best method so far. – Artholl Nov 5 '15 at 21:48
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)
);
  • Thank you for your example. But if I understand it right, it is basically the state which I have now. It doesn't allow me to save same Id for Fish and Cattle or am I missing something? – Artholl Nov 5 '15 at 8:02
  • Huh? I don't understand. Your question says you have a problem with having the same ID for both. This answer will result in unique ID's for fish and cattle. – Max Vernon Nov 5 '15 at 13:35
  • Sorry, maybe I wrote it wrong. I try to explain problem differently. That app creates instances of these two objects. It also fills Id property of those instances. Those Ids are unfortunately unique only in derived class - not in all derived classes together. My task is to save those instances to database and then read them when they are needed. So the problem is that I can't change that Id property and I can't use it as the real database Id because they are not as unique as I need :-) – Artholl Nov 5 '15 at 14:19
  • @Artholl why does your app generate a surrogate key of no apparent value? It should be left to the database. Is it possible to adjust your logic around this? – dezso Nov 5 '15 at 15:50
  • @dezso it is not my app and I don't have any chance to change how it creates instances - I don't have access to that part of the app. Those Ids have specific meaning in the app elsewhere so they were created just for the app needs. The best way for me so far seems to be 2b (from question). – Artholl Nov 5 '15 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.