What is the best database design for this situation?

Question

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?

Answer 1

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.

Answer 2

It's up the cardinality of your DB, and what things you want to store, for example you could just make approval_comment like an unique and make 1-1 relationship between approval and approval_comment, so you'll skip create one extra table... I saw it this way:

Cardinality:
Approval N ---- 1 Approval_comment.
Approval 1 ----- N Order
Order N ----- 1 Invoice

Answer 3

I vote for the first alternative. If you add approval requisite to an item, you propably alter the behavior of those items, so adding a column is not a big problem. And also, it is the best way to use joins in queries.

The second has useless additional tables, which may suggest that more than one approval may belong to one item.

The third contains a lot of redundant table, and probably redundant code to handle them.

Answer 4

If you have to decide between one of your three options, I believe Option 2 is best. @sreimer is correct that having to change your table structure for foreseeable events points at bad design. Having OrderID and InvoiceID in the Approvals table is, as I see it, very close to a repeating group across columns and violates the spirit, if not the letter, of first normal form.

I would consider adding an ApprovalID column to each table that needs approval. If the value is null, it has not been approved. If that is not an option, I think that @sreimer is onto the proper design--some clarification might help firm that up.

Answer 5

I would suggest you have only one table =>

Approval

id

status

comment

And have approvalId stored in inovice or Order table. This way you don't have to add column for each object and there are less table to maintain. And if I have to choose between option which you have provided I'll go with option 2 and merge approval comment into approval table.

Answer 6

You could try something like this:

Approvals
---------
  ID
  status
  approver_name
  (other stuff)

Orders
------
  ID
  (other stuff)

Invoices
--------
  ID
  (other stuff)

approved_objects
----------------
  ID
  Approval_id (FK - refers to approvals table)
  approved_object_id (FK - can refer to the ID of Invoices, Orders, etc...)
  approved_object_type_id (FK refers to approved_object_types)

approved_object_types
---------------------
  ID
  Name

The approved_objects table tells you which approval goes with which approved object. The approved object could be in invoices, orders, or something else. You determine which table to look up in based on approved_object_type_id. This will be flexible, but could make reporting a little tricky.