Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm sure this scenario has arisen before, and I want to know what experience has taught to be the best solution.

I have a number of classes that are all of a kind. Say all the objects are "Content". They may be "Article", or "Book" for example.

The reason I want the "Content" abstraction is because I want to define a number of behaviours for all "Content" objects and not have to build a new DB Table and 10 classes of essentially the same code for each type of "Content". For example, to attach a "Tag" or a "Premise" to a content object would be much nicer if, say, I just had two columns one for ContentID and one for TagID.

A solution I've played around with is to have a Content table with a unique ID, and then to have foreign key references on all the other tables (Book, Article, etc). This has actually proven quite solid, but I'm just not sure about it.

Do you know how to call this described pattern?

share|improve this question
    
in DB world, you may call it star pattern –  Yusubov Oct 13 '12 at 13:18
    
@ElYusubov - I don't think this is a star schema –  JeffO Oct 13 '12 at 13:42

4 Answers 4

up vote 3 down vote accepted

You can map inheritance to database tables in the following ways,

  • Map the entire class hierarchy to a single table: All attributes of the classes are stored in one table. This is a good strategy for simple and/or shallow class hierarchies where there is little or no overlap between the types within the hierarchy.

  • Map each concrete class to its own table: A table is created for each concrete class, each table including both the attributes implemented by the class and its inherited attributes. This is a good strategy when changing types and/or overlap between types is rare.

  • Map each class to its own table: Create one table per class, with one column per business attributes and any necessary identification information (as well as other columns required for concurrency control and versioning). This is a good strategy when there is significant overlap between types or when changing types is common..

As said, none of these mapping strategies are ideal for all situations. Experts recommend to start with one table per hierarchy at first, then if needed refactor schema accordingly.

Do you know how to call this described pattern?

Catalog of Patterns of Enterprise Application Architecture calls this Inheritance Mappers.

share|improve this answer
    
Thanks, Shuvo. Question: in your description for "Map entire class hierarchy to single table" is it a typo to say this is a good strategy where there is "little or no overlap between the types within the hierarchy"? I thought it would've been the opposite? –  nulliusinverba Oct 13 '12 at 22:31
    
"When there is significant overlap between types" mapping each class to its own table is a better option. –  IAmTheDude Oct 14 '12 at 8:07

Your model is solid because you have a strong idea about the different types of content you want to manage (books, articles, etc.). Having a table for each type of content defines the different attributes. You can probably predict over 90% of the types you'll need for several years.

If you put them in one large table, you will have several columns that will be null. There are some databases that have improved the handling of sparse data and can save on space. From a data query perspective, you can include different types of content very easily.

I've seen several systems (mainly enterprise apps) where they provide greater flexibility for user-defined fields. Unlike your situation, they can't predict all the entities. A flexible way to do this is with a Entity Attribute Value Model. The joining of extra tables and possibly transposing row data into columns, performance is sacrificed over user flexibility. Basically, do you want your app to be programmer/code driven or user/data driven?

You may want to consider how you're going to handle searching. There are tools out there to make this easier. I've you're going to build your own, you have to deal with multiple tables.

Maybe a NoSQL solution should be considered?

share|improve this answer
    
Thanks, Jeff - I have only just now learned of NoSQL. It is very impressive. For other aspects of my application this will be perfect. "Polyglot Persistence" probably, but I will now look into a total NoSQL solution. Cheers. –  nulliusinverba Oct 15 '12 at 0:15

If you know in advance the entire set of possible types of content you could use table-per-hierarchy and stick all of the books, articles etc. sub-classes of Content into one table, If, on the other hand, you may (or already know you will) need new subclasses of Content later on I'd use table-per-subclass (like you are thinking about)

So, the question in my opinion is to ask: Is my set of Content subclasses complete or should it be easily extendable later?

share|improve this answer
    
You will end up with several columns of Null data for fields that only apply to one content type. –  JeffO Oct 13 '12 at 14:02
    
Only if you have a variable number of fields (or attributes as it happens) for each instance of a given subclass of Content. –  soloist Oct 13 '12 at 14:42
    
Thanks, soloist - this was very useful. I've marked Shuvo's response the answer due it's detail. –  nulliusinverba Oct 13 '12 at 22:29
    
In this case, it is likely there are going to be a variable number of fields. –  JeffO Oct 14 '12 at 15:11
    
I understand the original problem as having a set of subtypes for COntent and that the idea is to minimize the code and properties repeated for each subclass. In that case there will not be any attributes that have not been defined for eoither the base class of the subclasses. If the idea is to allow for any number of arbitrary properties then those have to be stored in a separate table with a Foreign Key from for eaxmple the COntent base class table. –  soloist Oct 14 '12 at 17:26

I have been running into something similar, with a base table of Persons, a useful abstraction, and then other tables depending on whether they are Contractor, Client, etc.

For the table design, if the "sub table" (in your case, Book, Article) cannot exist without being Content, then I'd go with your design, and use the Content_ID as the key for Book and Article as well.

Since I use PHP, I have a class that handles Content, then a class that extends that to handle Books/Articles.

If you are using the database, I'd recommend a VIEW which SELECTS and JOINs the Content-Book tables, and another with does Content-Articles for ease of use.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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