The best database design for this situation

database-design

I'm working on building a business application for my company and struggling to pick the most appropriate database design for a particular situation. Let's say I have the following entities:

Approval

  • Id
  • Status
  • …

ApprovalComment

  • Id
  • ApprovalId
  • Comment

Order

  • Id
  • …

Invoice

  • Id
  • …

There obviously might be multiple types of approvals and multiple objects that require approvals. What would be the most appropriate of the following options for designing the tables:

OPTION 1

Have one approvals table with null foreign keys:

Approvals

  • Id PK
  • Status
  • OrderId FK NULL
  • InvoiceId FK NULL

ApprovalComments

  • Id PK
  • ApprovalId FK
  • Comment

In this case I would have to add a column for every object that needs an approval

OPTION 2

Have a parent Approvals table with common fields, and a child table for each object that needs an approval:

Approvals

  • Id PK
  • Status

ApprovalComments

  • Id PK
  • ApprovalId FK
  • Comment

OrderApprovals

  • ApprovalId PK FK
  • OrderId FK

InvoiceApprovals

  • ApprovalId PK FK
  • InvoiceId FK

OPTION 3

Have an approvals table for each object:

OrderApprovals

  • Id PK
  • OrderId FK
  • Status

OrderApprovalComments

  • Id PK
  • OrderApprovalId FK
  • Comment

InvoiceApprovals

  • Id PK
  • InvoiceId FK
  • Status

InvoiceApprovalComments

  • Id PK
  • InvoiceApprovalId FK
  • Comment

I know these are all valid solutions, but I can't decide which one would be best for adding different types of approvals in the future. Any thoughts?

Best Answer

A rule of thumb I use, is that it's bad database design to have to alter a table to accomodate a code change or a new feature if it can be planned for in the future.

That said, I would use a variant of Option 1

Approvals
    Id PK
    ApprovalTypeID FK
    Status


ApprovalComments
    ID PK
    ApprovalId FK
    Comment

ApprovalTypes
    ID PK
    Name

Now when adding new types of objects that need approval, you only need to insert a row into ApprovalTypes instead of altering a table to add a column.

Related Question