Which of these 2 database setups should I choose?

I have 3 types of content: blogs, press releases, and reminders. All of them have a body and entered by fields. The blogs and press releases have a title field, which the reminder lacks, and the reminders has an hour field, which blogs and press releases lack. This is what it looks like in tabular format so it's easy for you to see...

                   blog       press release      reminder
entered by field   yes        yes                yes
body field         yes        yes                yes
title field        yes        yes                --
time field         --         --                 yes

I'm creating a main table called content that links to the specialized tables blogs press releases reminders. I thought of 2 structures

First structure... This is how the content management system I use does it, but I don't want to follow in their steps blindly because my needs are not the same. Put ALL shared fields in the main content table. So the content table will not only have type and type id to link to the specialized tables, the content table will also have the common fields like body and entered by. The other 3 tables only have their unique fields.

content table    B=blogs table   PR=press releases table     R=reminders table
id               id              id                          id
type=B/PR/R      title           title                       hour
type id
entered by

Second structure. content table only has the type and type id necessary to link to the other 3 tables, This means that the common fields get repeated in the 3 tables.

content table    B=blogs table   PR=press releases table     R=reminders table
id               id              id                          id
type=B/PR/R      entered by      entered by                  entered by 
type id          body            body                        body
                 title           title                       hour

Which should I go with? I thought the first structure is better because I can search all content whether it's a blog or press release or reminder for a specific word. I still have to look in the other tables if I want to search the title which is available only to blogs and press releases, but...

So which structure is better, and why you think so? I'm also open to other ideas or improvements that are different from these 2.


The first one is the better construct, it allows for a content to have a specific set of required or common data in the content table and then specialized data in the child tables. This also allows you to add more types in the future with other requirements that still reuse the common elements in content but retain any unique data.

One other key question is if that data is required, for example do all reminders require an hour and do all blogs/press release require a title. If they are required then you ensure that those child tables will always be populated. If they are not then perhaps you should look at flattening the structure (yes Virginia you should sometimes denormalize).

So instead your content table simply becomes (nn = not null, n = nullable) id (nn) ,type id (nn), type (nn), body (nn), entered by (nn), title (n), hour (n). The main reason I usually find for doing this is that if the different data entities you are creating are so similar that over time it is possible they will merge. For example reminders at this time do not require a title, but in the future the might.

The first structure is a classic super type-subtype approach, and recommended. I would just suggest naming primary keys with full table-name-id like ContentID to avoid possible confusion.

Need Your Help

How to save XImage as bitmap?

c++ linux jni x11 xlib

i'm trying to create JNI C++ library that will capture desktop video (frames). First step is to simply make a screenshot of desktop. Code is : #include <iostream> #include <X11/Xlib.h>

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.