I have 4 subclasses: Video
, Image
, Note
, and Form
. Each one contains different types of data. For example, the Image
class contains a path to the image file on disk and image properties, and the Form
class contains the form field values. The common element between each item, however, is the GPS coordinates and heading, so I have the following abstract base class:
public abstract class Content
{
public float? Latitude { get; set; }
public float? Longitude { get; set; }
public float? Heading { get; set; }
}
However, where I get hazy is on how to model this in a database. Currently I have a table called Events
(for the sake of example, let's say an event is a birthday party) and 4 tables (Videos
, Images
, Notes
, and Forms
). Each table has a foreign key linking back to Events
' primary key.
Using LINQ-to-SQL, I get 5 classes for each table. This is fine if I only want one type of data, for example Event.Images
, but what I want to do is count the total number of 'contents' an Event
has and get the GPS coordinates. I can wing the count easy enough by just using Event.Images.Count() + Event.Videos.Count() + ...
, but I can't do the same for the GPS coordinates. Is there some way I can model the database so that I can use a base class for every item and still be able to get the individual strongly-typed item when I need to see its data?
Take a look at these SO examples, it may be helpfull.
Actually, this exact question gets asked an awful lot, and has been answered many times. You probably have not searched using database terminology.
The problem is applying OO terminology and thinking in a non-object subject area; making an ordinary straight-forward task very complex and limited.
Martin Fowler's and Scott Ambler's books are not worth a dollar for the lot of them, details in this answer, starting at the 11 Dec 10 entry.
There are three different patterns for this documented in Martin Fowler's Patterns of Enterprise Application Architecture, each with different trade-offs:
That is one way to do it.
Generally speaking, there are two other ways:
table per sub-class: instead of trying to share the baseclass in the database, just create separate tables for each one.
table per hierarchy: put a superset of all the fields into a table, as well as a "discriminator" column, and store them in one place.
To figure out which is right, think about the usage patterns:
Also see what your tool supports most naturally. They all work.