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:
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:
OPTION 3
Have an approvals table for each object:
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?
23.9k 17 17 gold badges 79 79 silver badges 155 155 bronze badges asked May 9, 2012 at 16:10 user8676 user8676A 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.
answered May 9, 2012 at 16:19 410 1 1 gold badge 4 4 silver badges 12 12 bronze badgesWhere 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?
Commented May 9, 2012 at 17:38That 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.
Commented May 10, 2012 at 21:16It'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
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.
answered May 9, 2012 at 16:23 111 2 2 bronze badgesIf 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.
answered May 9, 2012 at 20:18 1,560 13 13 silver badges 19 19 bronze badgesI would suggest you have only one table =>
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.
answered May 11, 2012 at 5:39 techExplorer techExplorer 261 1 1 silver badge 4 4 bronze badgesYou 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.
answered May 9, 2012 at 19:29 FrustratedWithFormsDesigner FrustratedWithFormsDesigner 3,345 20 20 silver badges 21 21 bronze badgesTo subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2024 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2024.9.11.15092
