Hi guys.
We have an application. There are 3 (at the moment) different
sections, each has access to it's own 'Diary'.
A section is like, 'Sub Program', which contains numerous sub
programs.
Programs, which contains numerous Programs, each with numerous Sub
Programs.
So, Each Sub Program has numerous Diarty Entries. Each Program has
Numerous diary entries.
The diaries for each section are identical.
I think the best option is to have ONE diary table, with a Type ID of
the section (Eg, 1 for Sub Program, 2 for Program...) type that the
diary is linked to. And then an ID to that specific record it's linked
to.
Problem is, with this method, I can't enforced database integrity, as
the ID that the diary item links to, can be in any of the Section
tables (Sub Program table, or Program table.... etc).
The other (And I feel, incorrect) metod, is to have a Diarty table for
each section. 'SubProgramDiary', 'ProgramDiary' etc. In this way,
referential integrity can be maintained. The trade off is, I think
it's repeated code, repeated tables... and would be more difficult to
handle in Reporting later on.
Hope I'm being clear. Can someone add to this? Which would be the best
method?