The iBATIS philosophy - iBATIS in Action - page 31

Table 1.3 Address data in a skinny model (continued)

ADDRESS_ID FIELD VALUE

1 COUNTRY USA

2 STREET 456 Another Street

2 CITY New York

2 STATE New York

2 ZIP 54321

2 COUNTRY USA

This design is an absolute nightmare. To start, there is no hope of possibly nor-
malizing this data any better than it already is, which can only be classified as first
normal form. There's no chance of creating managed relationships with COUN-
TRY , CITY , STATE , or ZIP tables, as we can't define multiple foreign key definitions
on a single column. This data is also difficult to query and would require complex
subqueries if we wanted to perform a query-by-example style query that involved a
number of the address fields (e.g., searching for an address with both street and
city as criteria). When it comes to updates, this design is especially poor in terms
of performance; inserting a single address requires not one, but five insert state-
ments on a single table. This can create greater potential for lock contention and
possibly even deadlocks. Furthermore, the number of rows in the skinny design is
now five times that of the normalized model. Due to the number of rows, the lack
of data definition, and the number of update statements required to modify this
data, effective indexing becomes impossible.
Without going further, it's easy to see why this design is problematic and why it
should be avoided at all costs. The one place that it is useful is for dynamic fields in
an application. Some applications have a need to allow users to add additional data
to their records. If the user wants to be able to define new fields and insert data into
those fields dynamically while the application is running, then this model works
well. That said, all known data should still be properly normalized, and then these
additional dynamic fields can be associated to a parent record. The design still suf-
fers all of the consequences as discussed, but they are minimized because most of
the data (probably the important data) is still properly normalized.
Even if you encounter a skinny data model in an enterprise database, i BATIS
can help you deal with it. It is difficult or maybe even impossible to map classes to
a skinny data model, because you don't know what fields there might be. You'd
have better luck mapping such a thing to a hashtable, and luckily i BATIS supports