2

My Question is about the idea of combining two junction tables into one, for similarly related tables. Please read to see what I mean. Also note that this is indeed a problem I am faced with and therefore relevant to this forum. It is just a topic of broad consequence for which I'm hoping to elicit a bit more participation from various professionals to get a better census of "best practice" if you will.

I have this rather challenging database design problem. I'm hoping this will be sort of a wiki that many people can contribute to and learn from. To make this easier, I've created a set of graphics, and will break the problem down into 1) Process, and 2) Structure.

Process Steps

  1. A request (DocRequest) for documentation (Publication) is made.
  2. A new publication is created IF said publication does not already exist.
  3. A running log (StatusReport) is kept for progress on fulfilling the request.

Note: For any given Publication there may be many DocRequests and StatusReports (including updates)

Database Structure

Note: Both the DocRequest and StatusReport tables have numerous fields and supporting tables not shown in the attached graphics. Furthermore, a particular Publication is the master record to which all records in those tables belong.

--Current Implementation-- enter image description here

Note: The major flaw with this design is that whenever you create either a new DocRequest and StatusReport record, you have to also create a new record in the Publications table (which acts like a junction table), but this also creates a new Publication as a result. This is not the desired behavior.

--Typical Implementation-- (for this type of relationship) enter image description here

Note: This is ok, and probably ideal, but handles updates to either the DocRequest and StatusReport tables, independently linking them to the Publication to which they belong.

--My Preferred Implementation-- (for this special case) enter image description here

Note: The idea I had here, was simply to combine the dual junction tables into one. In this case the junction table would get a new record anytime either the DocRequest or StatusReport had a insert occur. I will likely handle this with a trigger.

Discussion

Now for the discussion. I would like to know from my fellow Database Developers if you think this is a bad idea, and what issues might arise from this. I think the net number of records should be identical as with the two separate junction tables, and in fact uses slightly less space by saving an extra ID column. :)

Let me know what you guys think. I would really like to get many people involved in this discussion. Cheers! :)

tshepang
  • 12,111
  • 21
  • 91
  • 136
Chiramisu
  • 4,687
  • 7
  • 47
  • 77
  • Can one document request create multiple documents, one document be the result of multiple requests, neither, or both? Would one status report relate to multiple documents, multiple documents relate to one status report, neither, or both? Is there a relation between a document request and a status report? The three designs you present differ on these points, so you'll have to make them explicit. – Adam Robinson Sep 28 '11 at 21:58
  • What attributes define a publication from the perspective of the business? I.e., is it that a publication can be or is a result of a doc request or a status report or both or neither and thus you are trying to create a type-subtype structure? Is "StatusReport" imply a status on a report or is it a type of report (and thus publication)? – Thomas Sep 28 '11 at 22:12
  • Does "status report" have to do with the status of the document request, the status of the publication that will fulfill the document request, or both? – Mike Sherrill 'Cat Recall' Sep 28 '11 at 22:49
  • 1
    Also, you might want to edit your question to reduce the chances of it being closed. "If your motivation for asking the question is 'I would like to participate in a discussion about ______', then you should not be asking here." [FAQ] – Mike Sherrill 'Cat Recall' Sep 28 '11 at 22:51
  • @Adam & Thomas: Well for any product, there are multiple Publication associated with it. For any given Publication, there can be multiple requests for various changes to that Publication, and each request can have multiple updates to the original request. I could create a diagram if that would help? Also Thomas, please review the "Process Steps" again, that may answer your questions. They are sequential. – Chiramisu Sep 28 '11 at 23:47
  • @Catcall: It is indeed a question I would like help on Catcall, hence the reason for my asking it here (also per my link in the answer I created). And Yes, the StatusReport is directly related to the Request to track progress on fulfilling said Request, as noted in the process I outlined at the beginning of my question. As always, your input is valued. :) – Chiramisu Sep 28 '11 at 23:55

1 Answers1

2

I think you're hurting yourself by thinking in terms of junction tables. Just think of tables.

  • Since StatusReport has to do with the status of the document request, you need a table that relates those two somehow.
  • "StatusReport" is an awful name for a table that stores facts about the status of a document request.
  • "ID" is an awful name for any column in any table.
  • The id number of the publication seems to have more to do with the document request than with the status of the request. (You said, "A new publication is created IF said publication does not already exist." Frankly, that's skating pretty close to the edge of not making sense.) So the publication number almost certainly belongs in the DocRequest table.

Referring to the diagram of your preferred implementation, I'd drop the table TripleJunction, and replace StatusReport with this.

-- Predicate: Document request number (doc_request_id) has status (status) 
--            as of date and time (status_as_of).
create table document_request_status (
  doc_request_id integer not null references DocRequest (id),
  status_as_of timestamp not null default current_timestamp,
  status varchar(10) not null,
  -- other columns go here
  primary key (doc_request_id, status_as_of)
);
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Aye, tell me about it. I've been trying to wrap my head around the requirements for this design for months now. This is the best I can come up with. To put it more simply, we have a product. For that product we need various publications. Those publications are requested first through a web form, then created. Once created, we track progress on their development. The trick is that we need a running log (history) for the original requests (changes are not SQL Updates, but rather Inserts, hence many-to-many) as well as accompanying status reports and their updates (actually inserts). Clearer? Oi. – Chiramisu Sep 29 '11 at 07:03
  • Actually I was wrong. After rethinking it, I'm sure the StatusReport references the progress on a particular Publication. The starting point for both Publications and StatusReports IS the Request. Also, please note that StatusReports and Requests contain the data from an actual web form. The table names reflect the real objects they model. We're essentially moving a former paper process into a digital one with a database. As for "ID" I personally prefer it because then my joins look like `on ProductID = Products.ID`. – Chiramisu Sep 30 '11 at 00:10
  • THIS!! -- I had a chance to meet with my old professor the other day and he pointed out much like Catcall did, that I was thinking about the relations wrong. What I have here is not actually a true many-to-many relationship. It's actually a grouping of StatusReport entries which belong to a grouping of DocRequest entries. The solution was to drop the Publications table and simply add a "FirstRequest" field to the DocRequest table, and one to the StatusReport table, both of which acted as foreign keys to the primary key of DocRequest. It took me awhile to get there, but thanks for your input :) – Chiramisu Oct 11 '11 at 20:05