Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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]
share|improve this question
    
Do you have an ENTITY-ATTRIBUTE and an ENTITY-ATTRIBUTE-VALUE table as well? –  ypercube Aug 8 '12 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 Aug 8 '12 at 22:38
    
WHat are these 3 columns about: fieldName, fromValue and toValue? –  ypercube Aug 8 '12 at 23:17
    
That's an audit table, as described here: stackoverflow.com/questions/39281/database-design-for-revisions –  Balasubramanium Aug 8 '12 at 23:41

1 Answer 1

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.

share|improve this answer
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 Aug 10 '12 at 1:52
    
@Joel Brown Excellent Point. –  Leigh Riffel Aug 10 '12 at 12:12
    
Which normal form is it violating though...? –  Neil McGuigan Aug 10 '12 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 Aug 13 '12 at 12:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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