Multiple one-to-many conundrum. (1 Viewer)

[k!t]

I have a table “tblRequest” which will be used to track requests in our team.

A request can relate to many tasks/actions and an action can relate to many requests. I have created a table which links actions - “tblLnkAction” - and in this table are also some fields (date completed, date cancelled, language)
When an action is selected, depending on what the action is, I want it to link to another table. For example, my team work on publications and web pages. We have two separate tables for these which include relevant fields (e.g. url for web page, publication id for publications).

If a request to update an existing webpage is received I would want to link the action to an appropriate record from the web page table. I’ve therefore included the unique ID for the web page table as a foreign key in tblLnkAction and created a one-to-many relationship.

Now the conundrum, I’ve also included the unqiue ID for publications as a foreign key in tblLnkAction, but in general a task will not relate to both a web page and a publication. This means that for many of the action records, the foreign key will not link to one of the records in these tables.

This seems to go against the grain of my limited understanding of normalisation, but I’m struggling to find a better solution. There is also a possibility in future that we’ll need to extend the tasks we do and include a table similar to publications/web pages which would also have to be linked to an action.

I have thought about splitting off into two action lists – one for publications and one for web pages but since the linked table would include many of the same fields this also seemed unlikely to be a good choice – it would also make it more difficult to display a single list of the tasks relating to a tblRequest record in a continuous form cleanly.

Thanks for your time, your constructive advice would be much appreciated.

 

[spikepl]

Show your table structure - so much easier than inventing it in our heads to visualize what you are on about. Attach screenshots of Realations window in zip file.

 

[k!t]

I've edited my initial post and attached a zip with an image which shows the relationships I've attempted to describe.

 

[The_Doc_Man]

but in general a task will not relate to both a web page and a publication. This means that for many of the action records, the foreign key will not link to one of the records in these tables.

I'll reply "in general" because I cannot download posts at my work site. (Really nasty firewall rules...) Therefore I can't see what you are trying to do, but your description makes enough sense for me to take a stab at it anyway.

It doesn't matter what is "generally the case." If it EVER occurs that you would have to link both a web page and a publication at the same time, then the specific case trumps the general case. However, if you wish to retain "sparse" tables, there is this to consider as one way to manage the problem to avoid the null FKs.

Have two junction tables, one between your request and your web page items, and another between your request and your publication items. If a request points to many items, you have many entries in the junctions for the same request ID (but different web or publication items). To show ALL items of either flavor for a given request, make a UNION query that includes three fields, and I'm going to simplify this a LOT...

SELECT ReqID, "WEB", WebID FROM ReqWebJunction
UNION
SELECT ReqID, "PUB", PubID FROM ReqPubJunction ;

Now you can use THIS query for display purposes, and you can do more if you add stuff to the two "legs" of this UNION query by doing JOIN operations between the ReqXXXJunction tables and the individual Web or Publication tables.

To add or delete requests, you need two forms, one for each flavor of request - or one unbound smart form that dynamically changes a .ROWSOURCE that controls what it shows after you select WEB or PUB actions. Either way, you would only ever update one of the junction tables at a time.