Unnecessary complexity? Advice requested

[rogerpearson]

Hi all,

I'd like some advice on a solution I'm working on.

The database i'm creating has the following elements:

Artefacts: these can be e.g. podcasts, e-tutorials, books etc

Artefacts are grouped by module, i'e a module has a number of artefacts:

Projects and tasks: this part (Left of graph) is straight from the Filemaker solution.

I originally started with Artefacts purely for tracking production and have subsequently joined it to the Projects and Tasks.

The purpose of the database:

I wish to allocate users tasks and join the artefacts with the tasks. This can be done individually or by module. I used the table Task Artefact Join to accomplish this

One regular task is to create a new cohort on our VLE. Each cohort will have a different name; I created Tasks Module join for this purpose. It is essentially a checklist for each module that is uploaded to the VLE.

I also wish to track progress of each artefact's production, e.g. who wrote it and when, who is editing it, who voiceover etc.

Tables: Ignoring the left of the graph, the tables i originally created were Modules, Artefacts, Artefact types, Task artefact join, Task Module join. Some of the TOs on the graph are for hierarchical dropdown menus.

So far so good - the database did what i wanted it to.

I then realised that I needed to create versions of each Artefact for amendments, corrections, updates etc. (V1, V2 etc).

I then created a copy of the Artefacts table: Artefacts Versions as one Artefact can have many versions. I also removed many of the fields in Artefacts to avoid duplication.

This is where my problem lies: I think that the table Task artefact join may be now superfluous and that Artefacts Versions could be used as the many to many link between Artefacts and Tasks. Or have I got the solution completely wrong?

I be very grateful if someone could point me in the right direction.

Roger

 

Edited September 10, 2015 by rogerpearson
Additional information