Complex Database Relations (Junction Tables)

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--

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)

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)

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开发者_StackOverflow社区 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! :)

---

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)
);