Relational Mapping Patterns

1
Relational Mapping Patterns
2

• The patterns
• Foreign Key Mapping
• Identity Map
• Dependent mapping
• Association Table Mapping
• Inheritance Mappers
• Concrete Table Inheritance
• Single Table Inheritance
• Class Table Inheritance

3
Foreign Key Mapping
4
Foreign Key Mapping
Our propose
Map an association between objects to a foreign
key reference between tables
Foreign Key Mapping
The problem

• Objects can refer to each other directly by
  object references.
• Even the simplest object oriented system will
  contain a bevy of objects connected
• to each other.
• To save these objects to a database, it's vital
  to save these references.
• However since the data in these references is
  specific to the specific instance of
• the running program, you can't just save raw
  data values.

• This is further complicated by the fact that
  objects can easily hold collections
• of references to other objects.
• Such a structure violates the first normal form
  of relational databases.

5
Mapping a simple reference to a foreign key

• The obvious key to this problem is Identity
  Field.
• Each object contains the database key from the
  appropriate database table.
• If two objects are linked together with an
  association, this association can be replaced by
  a foreign key in the database.
• In the simple form of this, when you save an
  album to the database, you save the ID of the
  artist that the album is linked to in the album
  record.

Foreign Key Mapping
6
Mapping a multi-valued reference to a foreign key

• A more complicated case turns up when you have a
  collection of objects.
• You can't save a collection of objects in the
  database, so you have to reverse the direction of
  the reference.
• So, if you have a collection of tracks in the
  album, you use put a the foreign key of the album
  in the track record.

Foreign Key Mapping
7

• The complicated part of this occurs when you have
  an update.
• Updating implies that tracks can be added and
  removed to the collection within an album.
• Essentially We have three options
• Delete and insert
• The limitation is that you can only do this if
  tracks are Dependent Mappings.
• Add a back pointer
• changes the object model.
• Diff the collection.
• Anything on the database that isn't in the album
  was clearly removed.
• anything in the album that isn't on the disk is
  clearly a new item to be added.

Foreign Key Mapping
8
When to Use it

• A Foreign Key Mapping can be used for most
  associations between classes.
• The most common case where it isn't possible to
  use Foreign Key Mapping is with many-to-many
  associations.
• Foreign keys are single values and first normal
  form means you can't store multiple foreign keys
  in a single field. So instead you need to use
  Association Table Mapping
• If you have a collection field with no back
  pointer you should consider whether the many side
  should be a Dependent Mapping, if so that can
  simplify your handling of the collection.
• Be Aware!
• One thing to watch out for is cycles in your
  links.

Foreign Key Mapping
9
Identity Map
10
Our propose
Ensure each object only gets loaded once by
keeping every loaded object in a map. Lookup
objects using the map when referring to them.
Identity Map
11

• Explicit Vs. Generic
• An explicit Identity Map is accessed with
  distinct methods for each
• kind of object you need such as
  findPerson(1).
• A generic map uses a single method for all kinds
  of objects, with a
• parameter to indicate which kind of object you
  need, such as
• find("Person", 1).
• Your type of key affects the choice. You can
  only use a generic map if
• all your objects have the same type of key.
  This is a good argument for
• encapsulating different kinds of database key
  behind a single key object.

Identity Map
How many

• Here the decision varies between one map per
  class and one map for the whole session.
• A single map for the session only works if you
  have database-unique keys .Once you have one
  Identity Map, the benefit is that you only have
  one place to go to and no awkward decisions about
  inheritance.

12
Dependent mapping
Dependent mapping

• Some objects naturally appear in the context of
  other objects.
• Tracks on an album may be loaded or saved
  whenever the underlying
• album is loaded or saved.
• If these tracks are not referenced to by any
  other table in the database,
• you can simplify the mapping by having the
  album mapper perform
• the mapping for tracks as well - treating the
  mapping of the tracks as a
• Dependent Mapping.

13

• How it Works
• The basic idea behind Dependent Mapping is that
  one class (dependent)
• relies upon some other class (owner) for its
  database persistence.
• Each dependent can have only one owner and must
  have one owner.
• An important property of a dependent is that it
  does not have an
• Identity Field and therefore doesn't get
  stored in a Identity Map.
• It therefore cannot be loaded by a find method
  that looks up an id.
• Indeed there is no finder for a dependent, all
  finds are done with the
• owner.
• A dependent may itself be the owner of another
  dependent. In this case
• the owner of the first dependent is also
  responsible for the persistence of
• the second dependent.
• In a UML model, it's appropriate to use
  composition to show the

Dependent mapping
14
When to Use it

• For Dependent Mapping to work there are a number
  of
• pre-conditions
• A dependent must have exactly one owner.
• There must be no references from any object other
  than
• the owner to the dependent.

Dependent mapping

• Any change to a dependent needs to mark the
  owner as changed so that the
• owner will write the changes out to the
  database.
• Or making the dependent immutable, so that
  any change to a dependent needs
• to be done by deleting the old one and
  creating a new one. Since the writing
• and saving of dependents is left to the
  owner, this allows updates to the
• dependents to be handled through deletion
  and insertion.
• So if you wish to update the collection of
  dependents you can
• safely delete all rows that link to the owner
  and then reinsert all
• the dependents.

15
Example Albums and Tracks (Java)
class Track... private final String
title public Track(String title) this.title
title public String getTitle() return
title The tracks are held in the album
class. class Album... private List tracks
new ArrayList() public void addTrack(Track
arg) tracks.add(arg) public void
removeTrack(Track arg) tracks.remove(arg)
public void removeTrack(int i)
tracks.remove(i) public Track getTracks()
return (Track) tracks.toArray(new
Tracktracks.size())
Dependent mapping
16
class AlbumMapper... protected String
findStatement() return "SELECT ID, a.title,
t.title as trackTitle FROM albums a, tracks t
WHERE a.ID ? AND t.albumID a.ID
ORDER BY t.seq" protected DomainObject
doLoad(Long id, ResultSet rs) throws
SQLException String title rs.getString(2)
Album result new Album(id, title)
loadTracks(result, rs) return result
public void loadTracks(Album arg, ResultSet rs)
throws SQLException arg.addTrack(newTrack(rs))
while (rs.next()) arg.addTrack(newTrack(rs))
private Track newTrack(ResultSet rs) throws
SQLException String title rs.getString(3)
Track newTrack new Track (title) return
newTrack
Dependent mapping
17
public void update(DomainObject arg)
PreparedStatement updateStatement null try
updateStatement DB.prepare("UPDATE albums SET
title ? WHERE id ?") updateStatement.setLong(
2, arg.getID().longValue()) Album album
(Album) arg updateStatement.setString(1,
album.getTitle()) updateStatement.execute()
updateTracks(album) catch (SQLException e)
throw new ApplicationException(e) finally
DB.cleanUp(updateStatement) public void
updateTracks(Album arg) throws SQLException
PreparedStatement deleteTracksStatement null
try deleteTracksStatement
DB.prepare("DELETE from tracks WHERE albumID
?") deleteTracksStatement.setLong(1,
arg.getID().longValue()) deleteTracksStatement.ex
ecute() for (int i 0 i lt arg.getTracks().leng
th i) Track track arg.getTracks()i
insertTrack(track, i 1, arg) finally
DB.cleanUp(deleteTracksStatement)
Dependent mapping
18
public void insertTrack(Track track, int seq,
Album album) throws SQLException
PreparedStatement insertTracksStatement null
try insertTracksStatement
DB.prepare("INSERT INTO tracks (seq, albumID,
title) VALUES (?, ?, ?)") insertTracksStatement.
setInt(1, seq) insertTracksStatement.setLong(2,
album.getID().longValue()) insertTracksStatement
.setString(3, track.getTitle())
insertTracksStatement.execute() finally
DB.cleanUp(insertTracksStatement)
Dependent mapping
19
Association Table Mapping
20
Save an association as a table with foreign keys
to the tables that are linked by the association.
Association Table Mapping
The answer is the classic resolution used by
relational data people for decades create an
extra table to record the relationship. We can
then use Association Table Mapping to map the
multi-valued field to this link table.
21

• How it Works
• The basic idea behind Association Table Mapping
  is
• to use a link table to store the association.
  
• The link table has only the foreign key ids for
  the
• two tables that are linked together. It has
  one row
• for each pair of objects that are associated.
• The primary key of the link table is the
  compound
• of the two primary keys of the tables that are
  
• associated together.
• The first stage queries the skillsEmployees
  table to
• find all the rows that link to the employee
  you want.
• Then for each row in the link table you find
  the skill
• object for the related id.

Association Table Mapping
When to Use it The canonical case for
Association Table Mapping is a many-to-many
association, since there's not really any
alternatives for that situation.
22
Association Table Mapping
23
class AbstractMapper... abstract protected
String findStatement() abstract protected
DomainObject doLoad(Long id, ResultSet rs) throws
SQLException protected Map loadedMap new
HashMap() protected DomainObject
abstractFind(Long id) DomainObject result
(DomainObject) loadedMap.get(id) if (result !
null) return result PreparedStatement stmt
null ResultSet rs null try stmt
DB.prepare(findStatement()) stmt.setLong(1,
id.longValue()) rs stmt.executeQuery()
rs.next() result load(rs) return result
catch (SQLException e) throw new
ApplicationException(e) finally
cleanUp(stmt, rs) protected DomainObject
load(ResultSet rs) throws SQLException Long id
new Long(rs.getLong("id")) if
(loadedMap.containsKey(id)) return (DomainObject)
loadedMap.get(id) DomainObject result
doLoad(id, rs) loadedMap.put(id, result)
return result
Association Table Mapping
24
class AlbumMapper... public Album find(Long id)
return (Album) abstractFind(id) protected
String findStatement() return "select ID,
title, artistID from albums where ID ?"
protected DomainObject doLoad(Long id,
ResultSet rs) throws SQLException String title
rs.getString(2) long artistID
rs.getLong(3) Artist artist
FinderRegistry.artist().find(artistID) Album
result new Album(id, title, artist)
result.setTracks(FinderRegistry.track().findForAlb
um(id)) return result
Association Table Mapping
25
class TrackMapper... public static final String
findForAlbumStatement "SELECT ID, seq, albumID,
title " "FROM tracks " "WHERE albumID ?
ORDER BY seq" public List findForAlbum(Long
albumID) PreparedStatement stmt null
ResultSet rs null try stmt
DB.prepare(findForAlbumStatement)
stmt.setLong(1, albumID.longValue()) rs
stmt.executeQuery() List result new
ArrayList() while (rs.next())
result.add(load(rs)) return result catch
(SQLException e) throw new ApplicationException(
e) finally cleanUp(stmt, rs) Loading
the track data occurs in the load method.
protected DomainObject doLoad(Long id,
ResultSet rs) throws SQLException String title
rs.getString("title") Track result new
Track(id, title) result.setPerformers(findPerfor
mers(id)) return result There's a separate
method for loading the performers. This invokes
the query on the link table.
Association Table Mapping
26
TrackMapper... private static final String
findPerformersStatement "SELECT "
ArtistMapper.COLUMN_LIST " FROM
artist_plays_tracks apt, artists art " " WHERE
apt.trackID ? AND apt.artistId art.id " "
ORDER BY apt.artistID" public List
findPerformers(Long trackID) PreparedStatement
stmt null ResultSet rs null try stmt
DB.prepare(findPerformersStatement)
stmt.setLong(1, trackID.longValue()) rs
stmt.executeQuery() List result new
ArrayList() while (rs.next()) ArtistMapper
artistMapper (ArtistMapper) FinderRegistry.artis
t() result.add(artistMapper.load(rs)) return
result catch (SQLException e) throw new
ApplicationException(e) finally
cleanUp(stmt, rs)
Association Table Mapping
27
Inheritance

• Concrete Table Inheritance
• Single Table Inheritance
• Class Table Inheritance
• Inheritance Mappers

28
Concrete Table Inheritance
29
Represent an inheritance hierarchy of classes
with one table per concrete class in the
hierarchy.
Concrete Table Inheritance
30

• How it Works
• Concrete Table Inheritance uses one database
  table for each concrete
• class in the hierarchy.
• Each table contains columns for the concrete
  class and all its
• ancestors.
• Pay attention to the keys with this scheme
• Ensure that keys are not just unique to a
  table, but also unique to
• all the tables in a hierarchy.
• If you are hooking up to databases that are
  used by other systems.
• you can't guarantee key uniqueness across
  tables.
• In this situation you either have to avoid
  using superclass fields or
• do a compound key that involves a table
  identifier.
• Compound keys your id field use both the
  primary key of the table and
• the table name to determine uniqueness.

Concrete Table Inheritance
31
Problems with using referential integrity
Concrete Table Inheritance

• The problem is
• There's no table for the player.
• The solutions are
• Ignore the referential integrity
• Use multiple link tables
• On top of this there's also problems if you can't
  guarantee key uniqueness.

32
Strengths Vs. Weaknesses

• Strengths
• Each table is self contained and doesn't have
  any irrelevant fields.
• Good sense when used by other applications that
  aren't using the
• objects. There's no joins to do.
• Each table is only accessed when that class is
  accessed
• Weaknesses
• Primary keys can be a pain to handle.
• Can't enforce database relationships to
  abstract classes
• If the fields on the domain classes are pushed
  up or down the
• hierarchy, you have to alter the table
  definitions. You don't have to do
• as much alteration as with Class Table
  Inheritance but you can't ignore
• this as you can with Single Table Inheritance.
  
• If a superclass field changes, you need to
  change each table that has
• this field
• A find on the superclass forces you to check
  all the tables, which leads
• to multiple database accesses (or a weird
  join.)

Concrete Table Inheritance
33
Single Table Inheritance
34
Represent an inheritance hierarchy of classes as
a single table which has fields for all the
fields of the various classes

Single Table Inheritance
35

• How it Works
• One table that contains all the data for all
  the classes in the
• inheritance hierarchy.
• Each class stores the data that's relevant for
  that class into
• one row of the table.
• Any columns in the database that aren't
  relevant for the
• appropriate class are left empty.
• Field in the table that indicates which class
  should be used.
• This can be the name of the class or a code
  field. A code
• field needs to be interpreted by some code to
  map it to the
• relevant class.
• If you embed the class name into the table you
  can just use
• it directly to instantiate an instance.
• The class name, however, will take up more
  space and
• maybe less easy to process by those using the
  database
• table structure directly, as well as more
  closely coupling
• the class structure to the database schema.

Single Table Inheritance
36
Strengths Vs. Weaknesses

• Strengths
• A single table to worry about on the database
• No joins in retrieving data
• Any refactoring that pushes fields up or down
  the hierarchy doesn't
• require you to change the database.
• Weaknesses
• Fields are sometimes relevant and sometimes
  not.
• Wasted space in the database.
• The single table may end up being too large
  with many indexes and
• frequent locking!
• Useing the same name for different fields.

Single Table Inheritance
37

Class Table Inheritance
38
Represent an inheritance hierarchy of classes
with one table for each class

Class Table Inheritance
39

• How it Works
• One table per class in the domain model.
• The fields in the domain class map directly to
  fields in the
• corresponding tables.
• How to link together the corresponding rows of
  the database tables?
• The primary keys are going to be unique across
  the tables if you use
• this scheme.
• An alternative is to let each table have its
  own primary and use
• foreign keys into the superclass table to tie
  the rows together.

Class Table Inheritance
40

• The Problem
• How to bring the data back from multiple tables ?
• Solutions
• Making a call for each table - isn't nice since
  you have
• multiple calls to the database.
• Doing a join across the various component
  tables - joins for
• more than three or four tables tend to be
  slow.
• Storing a type code in the root table!

Class Table Inheritance
41
Strengths Vs. Weaknesses

• Strengths
• All columns are relevant for every row so
  tables are easier to
• understand and don't waste space.
• The relationship between the domain model and
  the database is very
• straightforward.
• Weaknesses
• To load an object you need to touch multiple
  tables, which means a
• join or multiple queries and sewing in
  memory.
• Any movement of fields up or down the hierarchy
  causes database
• changes.
• The supertype tables may become a bottleneck as
  they have to be
• accessed frequently.
• The high normalization may make it hard to
  understand for ad hoc
• queries.

Class Table Inheritance
42
Inheritance Mappers

43

• How it Works
• The abstract player mapper is the one whose
  responsibility is to load
• and save the specific player data to the
  database.
• The player mapper class is provides a find
  method and overrides the
• insert and update methods.
• The finder methods are declared on the
  concrete subclasses because
• they will return a concrete class not an
  abstract class.
• The load method is implemented by each mapper
  in the hierarchy and
• the mapper loads the behavior for its
  corresponding domain object.
• The insert and update methods operate in a
  similar way using the save
• hook methods - each class storing its
  specific data and calling the
• superclass save method.

Inheritance Mappers
44
class Mapper...
public virtual void Update
(DomainObject arg) Save (arg) class
FootballerMapper... protected override void
Save(DomainObject obj) base.Save(obj) DataRow
row FindRow (obj.Id, tableFor(TABLENAME)) Footb
aller footballer (Footballer) obj row"club"
footballer.club class AbstractPlayerMapper..
. protected override void Save(DomainObject obj)
DataRow row FindRow (obj.Id,
tableFor(TABLENAME)) Player player (Player)
obj row"name" player.name row"type"
TypeCode class PlayerMapper... public
override void Update (DomainObject obj)
MapperFor(obj).Update(obj) private Mapper
MapperFor(DomainObject obj) if (obj is
Footballer) return fmapper if (obj is Bowler)
return bmapper if (obj is Cricketer) return
cmapper throw new Exception("No mapper
available")
Updating an object Class Table Inheritance

Inheritance Mappers