473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Forget about Referential checks??

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?
Jun 27 '08 #1
1 1386
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. '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?
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.
Jun 27 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
6
by: Greg Stark | last post by:
There's another poster complaining about referential integrity checks causing deadlocks. Unfortunately I've deleted the message so this response (and the archives aren't responding) isn't going to...
7
by: Jimmie H. Apsey | last post by:
Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential...
80
by: Andrew R | last post by:
Hi I'm creating a series of forms, each with with around 15-20 text boxes. The text boxes will show data from tables, but are unbound to make them more flexible. I want the form to be used...
2
by: ApexData | last post by:
Access2000, using a continuous form. I’m getting a message that say “you cannot add or change a record because a related record is required in table Employee”. This occurs in all my combobox...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.