Developer Area/Database design strategies

Work in progress

I've created this page to try to consolidate Mahara's stock of database design strategies, when we have data structures that are tricky to map into a standard relational database.

See https://bugs.launchpad.net/mahara/+bug/1522309

Suggested strategies

Here's an essay the describes four well-known strategies for dealing with inheritance in the database, and has a table describing their advantages and disadvantages and suggesting when to use them: http://www.agiledata.org/essays/mappingObjects.html#ComparingTheStrategies

This source calls them "One table per hierarchy", "One table per concrete class", "one table per class", and "generic schema".

You should read the essay to fully understand what they mean, but I'll give an example, using a simplified version of our own Plugin hierarchy.

In PHP, we have a base "Plugin" class, and each plugin will have these attributes stored in the database: name, version, release, active. We then have several subclasses: PluginArtefact, PluginAuth, PluginBlocktype, etc. Most of these have exactly the same attributes as the base Plugin class, but the PluginBlocktype has the additional (NULLable) attribute of "artefact".

1. "One table per hierarchy". You store everything about all the classes, in one table