Welcome! My Real question is about the thought of mixing two junction tables into one, for similarly related tables. Please read to determine what i'm saying. Also observe that this is actually a problem I'm confronted with and for that reason highly relevant to this forum. It's really a subject of broad consequence that I am wishing to elicit a little more participation from various professionals to obtain a better census of "best practice" for a moment.

I've this rather challenging database design problem. I am wishing this is kind of a wiki that lots of people can lead to and study from. To create this simpler, I have produced some graphics, and can break the issue lower into 1) Process, and a pair of) Structure.

Process Steps

  1. A request (DocRequest) for documentation (Publication) is created.
  2. A brand new publication is produced IF stated publication doesn't already exist.
  3. A running log (StatusReport) is stored for progress on fulfilling the request.

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

Database Structure

Note: Both DocRequest and StatusReport tables have some of fields and supporting tables not proven within the attached graphics. In addition, a specific Publication may be the master record that all records in individuals tables belong.

--Current Implementation-- enter image description here

Note: The main flaw with this particular design is the fact that if you create whether new DocRequest and StatusReport record, you need to also produce a new record within the Guides table (which functions just like a junction table), but this produces a brand new Publication consequently. This isn't the preferred behavior.

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

Note: This really is ok, and most likely ideal, but handles updates either to the DocRequest and StatusReport tables, individually connecting these to the Publication that they belong.

--My Preferred Implementation-- (with this special situation) enter image description here

Note: The concept I'd here, was only to mix the twin junction tables into one. Within this situation the junction table would customize the record anytime either the DocRequest or StatusReport were built with a place occur. I'll likely handle this having a trigger.

Discussion

Let's focus on the discussion. I must know from the fellow Database Designers if you feel this can be a bad idea, and what issues might arise out of this. I believe the internet quantity of records ought to be identical just like the 2 separate junction tables, and actually uses slightly less space by saving an additional ID column. :)

Tell me what everyone think. I would love to obtain lots of people involved with this discussion. Cheers! :)

I believe you are harming yourself by thinking when it comes to junction tables. Just think about tables.

  • Since StatusReport has related to the status from the document request, you'll need a table that relates individuals two in some way.
  • "StatusReport" is definitely an awful reputation for a table that stores details concerning the status of the document request.
  • "ID" is definitely an awful reputation for any column in almost any table.
  • The id quantity of the publication appears to possess more related to the document request compared to the status from the request. (You stated, "A brand new publication is produced IF stated publication doesn't already exist." Frankly, that's skating pretty on the brink of not making sense.) Therefore the publication number probably goes within the DocRequest table.

Mentioning towards the diagram of the preferred implementation, I'd drop the table TripleJunction, and replace StatusReport with this particular.

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