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.
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.
Attachments
Last edited: