Questions about DB modelling...

Question

How would you model these relationships in a db?

You have a Page entity that can contain PageElements.

A PageElement can for instance be an Article, or a Picture. An Article table obviously has other members / columns than a Picture. An article could have ie. "Title", "Lead", "Body" columns that are all of type nvarchar, while a Picture might have something like "AltText", "Path", "Width", "Height". I like this to be extensible, who knows what PageElements I might need in 3 months? So I guess I'd need a PageElementTypes table.

For the relationships, what about tables like these:

Pages with an Id, and other mumbo jumbo. (Create Date, Visible, what not)

Pages_PageElements with PageId and PageElementId.

PageElements with an Id and a PageElementTypeId and more mumbojumbo (SortOrder, Visibility etc.).

PageElementTypes with an Id and a Name (for instance "Article", "Picture", "AddressBlock")

Now, should I create a PageElementId column in every Articles, Pictures, AddressBlocks table to finish things up? That's where I'm a bit stuck, it's a simple 1:1 relationship so this should work, but somehow I might miss something.

Follow up:

The recommended solutions below with separate attributes would force me to store all attributes as the same type, or not? What If one PageElement has attributes that are nvarchar(255) and some are nvarchar(1000), what if some are integers?

If I got the EAV way I would have to create tons of tables for holding the attribute values for all the different data types out there.

Answer 1

The universal solution would be:

PageElementType: ID, Name, [Mumbo Jumbo]
PageElementTypeParameter: ID, PageElementTypeID, [Mumbo Jumbo]
Page: ID, [Mumbo Jumbo]
PageElement: ID, PageElementTypeID, [Mumbo Jumbo]
PageElementParameters: ID, PageElementID, PageElementTypeParameterID, Value, [Mumbo Jumbo]

In human words: There is a table for page element types, and an associated table, which lists possible parameters for each page element (like SRC and ALT for an image; TEXT for an article, etc).

Then there is a table with all the pages; an associated table which lists elements in each page; and a table which lists parameter values for each element.

Answer 2

I use a different naming convention then you but this is essentially what I would do:

PageElementType(PageElementTypeID, PageElementTypeName)

PageElement(PageElementID, PageElementTypeID)

Article(ArticleID, PageElementID, ...)

Picture(PictureID, PageElementID, ...)

Page(PageID, ...)

PageHasPageElement(PageHasPageElementID, PageID, PageElementID) => {PageID, PageElementID} are unique

This what I do and seems to be fairly well normalized and performs fine.

Answer 3

Just as you have configured Page Elements, you need to configure the Attributes associated with the Page Elements.

So we have two items that are extensible Page Elements & their Attributes.

I sugges the following tables:

Page : Page ID | ...

Page Elements : Page Element ID | Element Type ID | Page ID | ...

Page Element Type : Element Type ID | Page Element Type Label

Page Element Attribute Type : Attribute Type ID | Element Type ID | Attribute Label

Page Element Attributes : Page Element ID | Attribute Type ID | Attribute Value

The Page Element Attribute Type table will contain the list of attributes associated with an element. Example :

Atttibute Type ID 1 | Article | "Title"

Atttibute Type ID 2 | Article | "Lead"

Atttibute Type ID 3 | Picture | "AltText"

The Page Element Attributes table will store the actual value for the attributes assciated with a page element. Example :

Page Element ID 1 | Attribute Type ID 1 | "Everybody Loves Raymond"

Page Element ID 2 | Attribute Type ID 3 | "World Map"

Answer 4

The two common choices are Single Table Inheritance and Multi Table Inheritance. Other approaches include having tables for each concrete class which I've never used, and what I'd call a meta-table implementation, where the attribute definitions are moved into data rather than any sort of schema.

I've had generally good experiences with STI, and provided you don't expect a plethora of classes and attributes it's the simplest solution. Simple is very good in my book.

Unless new page element types need to be created by users at runtime, I'd avoid the meta-tables approach and anything that begins to look like it. In my experience such code quickly becomes a quagmire and rarely delivers much value compared to a more concrete implementation updated at regular intervals by developers.

Answer 5

I guess I'll just go with what I got, EAV is no option for me. What I got now is a somewhat hybrid approach.