Forget about Referential checks??

[Cralis]

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. 'SubProgramDiar y', '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?

[Ed Murphy]

Re: Forget about Referential checks??

Cralis wrote:

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. 'SubProgramDiar y', '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?

Here's my best guess:

1) Create a single Sections table containing whatever data is common
to all three sections. If they have some data that isn't in
common, then that can be kept in separate tables (e.g. Programs,
SubPrograms) with a foreign key reference to the Sections table.

2) Create a single Diaries table containing whatever data diaries have
that's independent of which sections they're linked to. Then
create a separate table DiarySections with foreign key references
to the Diaries and Sections tables; this will be one-to-many (one
Diaries row to many Sections rows).

3) Enforce "every section must contain an entry linked to every diary"
at the application layer.

Alternatively, give the Diaries table three foreign key references (e.g.
one to Programs, one to SubPrograms). If/when you add a new section:

a) Add a new foreign key reference to Diaries, but allow it to be
null.

b) Populate it for all existing rows.

c) Now make it non-null.