Skip to main content
Database Administrators

Return to Question

Post Reopened by mustaccio, Paul White♦
try to improve understandability with example data
Source Link
Artholl
Artholl
  • 101
  • 4

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

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

Post Closed as "Needs details or clarity" by Hannah Vernon♦, Paul White♦
Two tags added.
Link
Artholl
Artholl
  • 101
  • 4
Source Link
Artholl
Artholl
  • 101
  • 4

Derived classes with same base class and class specific ids

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?