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.
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.
Should I generate a complex object in the database or data access layer?
Log to a database using log4j
Best authentication solution for RESTful Database Server
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.
Is it possible to serialize and deserialize a void pointer to & from a blob in a database?
Maps: Does calculating distance between 2 points factor in altitude?
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.
Unique Constraint on two fields and their reverse
Simple is very good in my book..
Copy records from two database in sqlite
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.
Postgresql one db with multiple schemas vs multiple db with one schema
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..
. 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".
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..
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]
What I got now is a somewhat hybrid approach..