Collections in Data Design
Repeating attributes can be stored along with the instance of the entity to which they belong.
Attributive entities can be avoided.
Collection attributes can be Lean toward relational, using Due to 3rd party and driver limitations, need a good reason to use collection attributes.| Recall your
experience and training in relational data modeling. One of the goals is to avoid
redundancy. This aids data integrity
and simplifies data maintenance and extension. A good data architect attempts to keep models simple and
elegant using a technique called normalization. |
|
| TROUBLE_CALL(1)
has a repeating attribute. In first
normal form, that attribute becomes a child table whose primary key is a
foreign key back to its parent, an attributive relationship. After first normalization, the model looks
like TROUBLE_CALL(2). It’s clean and
it meets first normal form, but it’s a pain.
As a list of dates, the callbacks don’t describe anything useful
without joining to the parent. No
other entity depends on them. I like
to call these attributive tables “Clingons”.
It would be nice to stuff them in the parent as shown in
TROUBLE_CALL(3), but normalization and most databases won’t allow it. |
|
| What if you
could virtually include the attributive Clingons in the parent record? It would avoid excessive joins, eliminate
table clutter, and still preserve normalization. Cascading deletes would be a breeze too: delete the parent row,
and Poof! all the child rows are gone too.
Updating would be easier too since you wouldn’t have to traverse
several physical tables to update a table at the bottom of a hierarchy. In OO all this is accomplished using a
technique known as object composition.
In the relational world, it’s known as heresy. Heresy or not, since Oracle 8, you can
include collections with the parent row to which they belong. Tables like TROUBLE_CALL(3) are easy to
create, and make a lot of sense for certain scenarios. |