ORM domain design pitfalls

Question

Are there any patterns which seem sensible when designing an object oriented domain, but do not translate nicely onto a relational database schema? If so, are there standard patterns that can be used instead?

Answer 1

Domains which have entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest.

An example of such a domain would be a medical practice, where there are a vast number of possible symptoms, but the number of symptoms that any patient might have at any given time is comparatively small.

These kinds of domains are typically represented using an Entity-Attribute-Value (EAV) model. This data representation is analogous to space-efficient methods of storing a sparse matrix, where only non-empty values are stored.

In the case of a medical domain, the problem space is complicated by the fact that any given symptom or medical test can have its own set of custom attributes, just as products sold in an online store can have custom specifications.

In fact, online stores have to deal with this problem also. A book has a "number of pages" specification, while a memory module has a "number of bytes" specification, and the two attributes are not related at all.

So a set of attributes appropriate for each product is chosen from an attributes table.

The Attributes table might look like this:

AttributeID
AttributeDescription
UnitsID --> FK to Units table

The ProductAttributes table might look like this:

ProductAttributeID
ProductID
AttributeID --> FK to Attributes table
Value

Notice that Number of Bytes and Number of Pages aren't features of the database schema. Instead, they are soft-coded into the tables. So there is no way to represent these features as part of the domain design.

Answer 2

Referencing data that can change, in a record that shouldn't.

I've seen a number of implementations that had something like:

Order
    Id : int
    OrderDate : DateTime

OrderProduct
    Id : int
    OrderId : FK of Order
    ProductId : FK of Product
    Quantity : int
    Cost : Decimal

Product
    Id : int
    Description : string

The code ends up looking something like:

class Order
{
    int Id;
    DateTime OrderDate;
    List<OrderProduct> OrderLines;

    AddProduct(Product p, int quantity)
    {
        this.OrderLines.Add(new OrderProduct(this, p, q));
    }
}

class OrderProduct
{
    int Id;
    Product Product;
    Order Order;
    int Quantity;
    Decimal Cost;

    OrderProduct(Order o, Product p, int quantity)
    {
        this.Order = o;
        this.Product = p;
        this.Quantity = q;
        this.Cost = p.Cost * quantity * applicableTax;
    }
}

class Product
{
    int Id;
    string Description;
}

It seems correct at first glance, the code works fine, and the data ends up being nicely normalized, and you can create and read orders correctly.

Except that if you sell an order today, and you receive info from a supplier tomorrow that the description of something on the product has changed, those systems usually just update the product with the change. Now if you were to reproduce that order 2 days from now the contents of the order will have changed, and you'll be none the wiser.

There's some easy ways to solve this problem but I've seen it a number of times in varying degrees of severity.