Owing to the fantastic support and advice I have received as a result of posting questions here, I have now completed the first stage of my Editorial CMS DB - subject to testing etc. This is a request for advice on how to define the best architecture for my DB requirements moving forward.
The end game is to create a data collection tool and the ultimate requirement is to build a solution that features multiple editorial content types, such as News and Features etc. This will be web-hosted, sit behind a log-in process and to which around 25 individuals will be granted access. Given their geographical time-zone spread the concurrent access will be minimal.
Currently, and as you will see from the schema below, I currently have one table in the middle, that relates to an editorial content type - called Study_Papers.
The tables that sit around Study_Papers are all relevant to the other editorial content types which need to be added and this needs to be catered for. Furthermore, these additional editorial tables will also have different field requirements to Study_Papers and in addition will result in the creation of additional related tables to store images, video and audio.
My questions is -
What would be the best schema/relationships to accommodate the additional editorial types?
- Do I replicate the above relationships and create a new table instance in place of Study_Papers, say for News? I understand the basic concept of normalisation but am not clear as to whether duplicate table instances of the surrounding tables shown above contradict this.
- Or do I change the name (_PK etc) of Study_Papers to Editorial_Content and add the required fields for the additional editorial types?
In terms of size I don't expect the total editorial record count, irrespective of approach, to go beyond 3,000 records and providing all of the bigger file types etc - pics, audio, video - are stored separately and referenced via a relationship - only text will be stored but I can't say what the resulting record sizes might be.
There's no question the heavy lifting will be small but any advice on structure - that would also be good for the eventual web deployment - will be hugely appreciated as always.