2

My co-worker is designing a schema where most entities (like contacts, quotes, tasks) inherit from an "entity" table, which keeps track of things like which tenant owns the entity, and chagnes to the entity. This is similar to VTiger CRM's schema.

Is this a terrible idea? What should I tell him the problems are?

Is there a name for this pattern?

ENTITY
id PK
tenant FK TENANT
version
draft
deleted

ENTITYCHANGE
entityId PK FK ENTITY
version PK FK ENTITY
createdBy
createdAt
fieldName
fromValue
toValue

CONTACT
id PK FK ENTITY
name
[other contact fields]

QUOTE
id PK FK ENTITY
contactId FK CONTACT
[other quote fields]
Improve this question
4
  • Do you have an ENTITY-ATTRIBUTE and an ENTITY-ATTRIBUTE-VALUE table as well?
    – ypercubeᵀᴹ
    Commented Aug 8, 2012 at 22:16
  • No. For example, contact date of birth is stored in a dateOfBirth field in the contact table. I only showed the bare contact and quote definitions to minimize clutter. Thank
    – Balasubramanium
    Commented Aug 8, 2012 at 22:38
  • WHat are these 3 columns about: fieldName, fromValue and toValue?
    – ypercubeᵀᴹ
    Commented Aug 8, 2012 at 23:17
  • That's an audit table, as described here: stackoverflow.com/questions/39281/database-design-for-revisions
    – Balasubramanium
    Commented Aug 8, 2012 at 23:41

1 Answer 1

Reset to default
2

Yes, there are some issues with this design. The pattern is Entity-Attribute-Value at least for the EntityChange table. See the link for all the reasons why this almost always should be avoided.

The Entity table itself may not be EAV, but it does nothing but increase complexity without adding any value. To know if a Contact has another tenant, another version, another draft, or is logically deleted you have to join. Even if none of these are applicable to the Contact table, your design dictates their existence and accommodation.

If you decide that Contacts can be logically deleted then add a deleted column to the contact table. If you decide you need different drafts of Contacts, then add a drafts column, etc. Essentially, there is no advantage to moving these attributes to a common table. It increases storage requirements, reduces performance more than it helps, and make the system more complex.

Improve this answer
4
  • 1
    Good answer (+1). Having similar attributes (columns) on multiple entities (tables) doesn't necessarily mean they are redundant. You wouldn't think of dragging every name or description or cost field in a database together into a single table. That's not what normalization is about. Change tracking/soft deletion attributes are just the same.
    – Joel Brown
    Commented Aug 10, 2012 at 1:52
  • @Joel Brown Excellent Point.
    – Leigh Riffel
    Commented Aug 10, 2012 at 12:12
  • Which normal form is it violating though...?
    – Neil McGuigan
    Commented Aug 10, 2012 at 17:12
  • The point wasn't that it is violating a normal form, just that it is not necessary to satisfy normalization.
    – Leigh Riffel
    Commented Aug 13, 2012 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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