 Cralis
|
| Joined: 18 Jun 2008 |
| Total Posts: 1 |
| |
|
Referential Integrity
Posted: 18 Jun 2008 11:10 AM |
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?
|
|
|
 |
|
urid
|
 |
| Joined: 18 Jun 2007 |
| Total Posts: 34 |
| |
|
Re: Referential Integrity
Posted: 18 Jun 2008 10:11 PM |
Cralis
How about
CREATE TABLE Programms
(
ProgID INT NOT NULL PRIMARY KEY,
PrgName VARCHAR(100) NOT NULL
)
CREATE TABLE SubProgramms
(
SubProgID INT NOT NULL PRIMARY KEY,
SubPrgName VARCHAR(100) NOT NULL
)
CREATE TABLE Diary
(
DiaryID INT NOT NULL,
SubProgID INT NULL FOREIGN KEY REFERENCES SubProgramms([SubProgID],
ProgID INT NULL FOREIGN KEY REFERENCES Programms([ProgID])
)
|
Best Regards,
Uri Dimant
SQL Server MVP
http://dimantdatabasesolutions.blogspot.com/
|
|
|
|