2

I've got 3 C# classes: Circle, Square and Shape. Circle and square are both derived from shape.

In my database I have a table for each, referencing the base table to show their inheritance.

At the moment I am only ever accessing a circle or a square, never going directly to a shape

But in my shape table should I store a "Type" which shows that this shape is of type circle?

My tables are like this

    CREATE TABLE [dbo].[Shape](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ShapeType] [int] NOT NULL,   //This is the column I'm not sure on
        [sides] [int] NOT NULL

Then I have a square and circle table for the unique properties

I feel like the ShapeType column is a good idea, although I've been told before that building for "Just in case" scenarios is bad practice

Alex
  • 718
  • 3
  • 11
  • 26
  • 1
    foreign keys are for "has a" relationship. not for "is a". using shape type is a good idea. – Rabi Aug 08 '14 at 07:39
  • Ah, okay, I'm just looking at this question http://stackoverflow.com/questions/4361381/how-do-we-implement-an-is-a-relationship for more details. Thanks for the help. What would you suggest for the Type column? – Alex Aug 08 '14 at 07:42
  • 1
    NHibernate handles mapping inheritance to the database very nicely – Liath Aug 08 '14 at 07:45
  • 2
    I would use "tinyint" for ShapeType. I'm little conservative in memory consumption. – Rabi Aug 08 '14 at 07:46
  • @Liath It's an existing system that I'm trying to extend so nhibernate isn't a possibility – Alex Aug 08 '14 at 07:50
  • @Alex I suspected as much hence adding it as a comment rather than an answer – Liath Aug 08 '14 at 07:53

2 Answers2

2

In general, since you are doing some kind of Object-Relational mapping, you need to have some way of distinguishing a Circle from a Square. You could do this based on the data in the Shape table (e.g. Radius vs Length is not null). However there are some reasons to have a Discriminator column that determines the type:

  • If you extend your system to include other shapes such as Triangle you can easily add another value to the disciminator column.
  • It might not be possible to distinguish one type from another based just on data.
  • You will often query the data filtering or grouping on this discriminator column, so you can index it to improve the performance.

What I usually do is create an enum that maps to the different values:

public enum ShapeKind { Circle = 0, Square = 1 }

public abstract class Shape { 
    public abstract ShapeKind Kind { get; }
}

public class Square : Shape {
    public override ShapeKind Kind { 
        get { return ShapeKind.Square; } 
    }
}

This way you can use the ShapeKind to map to the discriminator column in a logical way.

Bas
  • 26,772
  • 8
  • 53
  • 86
  • At the moment my ShapeType is working as your ShapeKind. Would you say it's best to put it in even if I'm not using the kind at the moment? The square and circle also have their own tables. – Alex Aug 08 '14 at 07:47
  • If the Square and Circle have their own tables you don't need the Shape table at all? – Bas Aug 08 '14 at 07:50
  • Sorry, I've edited the example above. Shape will have other properties that both share, then circle and square will have their own properties in their table – Alex Aug 08 '14 at 07:53
2

Adding the ShapeType column makes it possible to add a shape with a ShapeType of Circle, without a corresponding record in the Circle table. In general, you should store as little data as necessary in the database, to prevent the possibility of such inconsistencies, until a clear need arises to add redundant information.

There can indeed be occasional practical advantages to such a column, but they mostly apply when the Circle's own properties are stored directly in the Shape table. For you, that's not the case, so you should ask yourself whether it would actually be useful to you. If not, I'd leave it out.

  • perfect, exactly what I was after! I was wondering whether I had worded my question wrongly. I'll go with that for now then! I suppose in the future, if it's needed, then I can patch with an update via the other tables? – Alex Aug 08 '14 at 08:04
  • Yes, you can run something like `ALTER TABLE Shape ADD ShapeType int DEFAULT 0; UPDATE Shape SET ShapeType = 1 WHERE ShapeID IN (SELECT ShapeID FROM Circle); UPDATE Shape SET ShapeType = 2 WHERE ShapeID IN (SELECT ShapeID FROM Square);` if you later do need it. –  Aug 08 '14 at 08:06