How to model relation to different type of objects

Question

I am developing a software for creating and storing print layouts.

• A layout consists of a series of pages.
• Each page has one or more columns
• Within each column there will be one ore more elements
• Elements within a column are ordered: There is a fixed sequence of elements
• Elements may be of type "text" or "image" (for the sake of simplicity, in reality there are many more types)
• Different elements have different properties. Eg. images have a resource url, text has a font. See example code at end of posting.

My question is about modelling the relation between columns and elements.

Idea #1: One table for all elements

This would be really straight forward. Add all properties as columns to a table and use N:N relation to model the relation between columns and elements.

Relation could look like this

column_idelement_idorder
000000010000000010009
000000010000000020012
000000020000000060003

This seems not very appealing to me as the table to store elements would be polluted with empty entries, e.g. all font columns would be empty for image elements. It gets even worse if I consider all different erlement types I have (about 10).

Benefit: SQL queries are relatively simple:

select * from
  relation_table R inner join elements E
  on R.element_id = E.id
where R.column_id = FIXED_COLUMN_ID

Idea #2: One table for each type of element

Modelling the elements would be quite simple, just create one column for all properties of class element and add the properties of the specific element type like image.

But how about modelling the relation column:element?

My first approach would be to try to add a column to the relation that specifies the type of element which would directly map to the related table:

column_idelement_idorderelement_type
000000010000000010012text
000000010000000020003image
000000020000000060005image

This certainly works but all sql queries to retrieve the elements for a specific column would get quite complicated.

Questions

Is there an approach that combines the simplicity of sql queries from Idea #1 and the more accurate database modelling of Idea #2?

How do you approach this problem?

P.S.: Under other circumstances I would switch to a nosql based database but that's not possible :-(

<?php
// Sample code of element structure
class element {
    protected $id;
    protected $order;
    // more properties, getters and setters...
}

class image extends element {
    protected $image;
    // more properties, getters and setters...
}

class text extends element {
    protected $font;
    // more properties, getters and setters...
}
?>

Answer 1

I think you are better off starting with one table for elements and then adding join tables for extra info required for various element types.

It is usually a mistake to think about modeling data as real-world entities in db design but much better to take a step back and model based on the inner dependencies of the data. These often coincide (and do so just often enough to make real-world entity focus look attractive) but when you run into cases where they don't it will cause you grief.

Here, you have issues regarding querying and ordering which are trivial to solve in a single table but will give you big headaches with many tables.