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?


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:

UnitsID --> FK to Units table

The ProductAttributes table might look like this:

AttributeID --> FK to Attributes table

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.

  • Interesting. Is this a good way of defining these properties in a relational database, or a bad way? – Armand Dec 8 '10 at 13:57
  • @Alison: It's OK, provided it's only used on these kinds of problems. – Robert Harvey Dec 8 '10 at 15:38

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

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

    Id : int
    OrderDate : DateTime

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

    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.

  • This definitely looks like a problem. Surely the real solution would be to have immutable products? – Armand Dec 8 '10 at 13:57
  • @Alison: That is on of the more popular solutions I've seen. – Steven Evers Dec 8 '10 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.