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.

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?

share|improve this question

migrated from stackoverflow.com May 9 '12 at 16:23

This question came from our site for professional and enthusiast programmers.

add comment

6 Answers 6

up vote 9 down vote accepted

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.

share|improve this answer
    
Where would the id relationship to a particular order/invoice go in this situation? Let's say an order requires a "credit approval", so the approval type would be credit, but how does it link to the order (or whatever entity)? Also, where would additional columns go that were specific only to a credit approval? –  user1384977 May 9 '12 at 17:38
    
That adds a bit of complexity. You could have a CreditApprovalDetails table. If you need to link approvals to orders, you would need a join table, unless it's 1:1. However, without a full understanding of your application I can't make an informed suggestion. –  sreimer May 10 '12 at 21:16
add comment

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.

share|improve this answer
add comment

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

enter image description here

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.