I've 3 kinds of content: blogs, press announcements, and memory joggers. These possess a body and entered by fields. The blogs and press announcements possess a title area, that the indication lacks, and also the memory joggers comes with an hour area, which blogs and press announcements lack. This is exactly what it appears as with tabular format therefore it is easy to focus on...

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

I am developing a primary table known as content that links towards the specialized tables blogs press releases reminders. I figured of two structures

First structure... This is the way the cms I personally use will it, but I'd rather not follow within their steps blindly because me won't be the same. Invest shared fields within the primary content table. Therefore the content table won't have type and type id to connect to the specialized tables, the content table can also get the most popular fields like body and entered by. Another 3 tables have only their own 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
body
entered by

Second structure. content table has only the type and type id essential to connect to another 3 tables, Which means that the most popular fields get repeated within 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

That ought to Time passes with? I figured the very first structure is much better because I'm able to search all content maybe it's a blog or pr release or indication for any specific word. I still need to try looking in another tables if I wish to search the title that is available simply to blogs and press releases, but...

So which structure is much better, and the reason why you think so? I am also available to other ideas or enhancements that aren't the same as these 2.

The first is the foremost construct, it enables for any content to possess a specific group of needed or common data within the content table after which specialized data within the child tables. This enables you to definitely increase the types later on along with other needs that also reuse the most popular elements in content but retain any unique data.

Another key real question is in the event that information is needed, for instance do all memory joggers require an hour or so and do all blogs/pr release need a title. If they're needed then you definitely make sure that individuals child tables will be populated. If they're not then possibly you should think about flattening the dwelling (yes Virginia you need to sometimes denormalize).

So rather your articles table simply becomes (nn = not null, n = nullable) id (nn) ,type id (nn), type (nn), body (nn), joined by (nn), title (n), hour (n). The primary reason It's my job to find for carrying this out is when the various data organizations you're creating are extremely similar that with time it's possible they'll merge. For instance memory joggers at this time around don't require a title, but later on the might.

The very first structure is really a classic super type-subtype approach, and suggested. I'd just suggest naming primary secrets with full table-title-id like ContentID to prevent possible confusion.

alt text