How to handle 'extending' tables

Question

I'm designing an application involving "pages", which have many "blocks", of which there are several types of blocks. I'm struggling to find a good way to hold this data in the database however. I'd imagine there is a popular way of doing this, here are two I am considering:

Approach One

• Pages table
  • id
  • user
  • created, updated etc.
• Blocks
  • id
  • page (Blocks.page = Pages.id)
  • created, updated
  • block_type (e.g. "text")
  • block_id (Blocks.block_id = nBlocks.id where n = block_type)
• Text Blocks
  • id
  • text-specific attributes

The pros of this are that attributes that are common to all types of block (created, updated, page, title, order) are kept in one place. You would also not have to query each block-type table to check for blocks for the current page as you would have this "index". Cons are that it could become a little confusing in finding blocks but this just depends on implementing it correctly (find all blocks for page, group by block_type, run queries on each block_type).

Approach two

• Pages as before
• Text Blocks
  • id
  • page
  • body, text-specific items
  • created, updated, order
• List Blocks
  • id
  • page
  • list-specific items
  • created, updated, order etc.

Pros of this are removing the confusing way of finding the correct table to query for each block. Cons are that block-order can't be easily managed (update where order in any other block != $order) and that each table must have the same created, updated etc. fields which makes for a bit of effort if they need to be changed. The bigger issue is that each block-specific table must be queried for every page, rather than just block tables which definitely have blocks for the page.

Is there a third, better way of doing this? I think the best approach is the first (it's more normalised than the second at least and the table-logic isn't that confusing) but I'd like to know if there's something I'm missing :)

Answer 1

Approach Two has a huge con: the ordering.

I'd take Approach One, or this: (i know it looks dirty, but it works)

• Pages as approach one
• Blocks
  • id
  • page
  • block_type
  • text-specific items (all nullable)
  • list-specific items (all nullable)
  • xxxx-specific items (all nullable)
  • created, updated, order

u can optimize it reusing some columns

Pros: same as Approach One

Cons:

• if u have a lot block types or a lot of specific items per type, u'll have a wide and confusing-to-read table.
• everything is nullable, so you can't be sure of which fields are required by just looking at the table

Extra Tip

in case you are planning to create the application with VisualStudio, use an Entity Data Model (.edmx file), create the entities with inheritance "the way it should be" and then click on Generate database from model. i believe that has all the pros and non of the cons of both of your approaches.