Looking for a suggestion - Normalised database

Question

Looking for a suggestion - Normalised database

Ananda Saravanan

Grasshopper

Points: 16
More actions
May 15, 2006 at 5:58 am

#67421

Hi
I am having some master tables referred by a transaction table. In one case the user want to permanently delete master table records. Because of referential integrity I am moving these records into a history table where the description is shown in the transaction table itself and then allow the user to delete the records.
For eg.
Master table - Student, Subject
Transaction table - Marks (foreign key student id, subject id)
If I want to remove Student or Subject permanently, can I create table called MarksHistory with no RI with the master tables( it has the description field in the MarksHistory itself) and allow user to delete student and Marks master.
Is it a right design? Some one can suggest me a alternate one.
Thanks in Advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Just wondering why do you want these Marks History records for that are orphon? One way you may want to keep your records intact even after deletion of Master records is to have Either Active/Inactive BIT Datatype column and when you want to delete the Master record just mark it as Inactive and while retrieving the Marks retrieves data only for Student/Subject that are active. Alternatively, you can a have column like RecDeleted with DATETIME Datatype and update the field with delete command execution time and while retrieving the child records use WHERE parenttable.RecDeleted ISNULL. This way you can audit the deleted date and time.

Prasad Bhogadi
www.inforaise.com

RLSQL Say Hey Kid Points: 713 More actions · Answer 2

Hi Ananda,

In my professional opinion every table involved in a parent/child relationship should have a primary/foreign key constraint. I see a lot of databases these days, and in every one I see, there is at least one table with orphaned child rows. For that reason, I strongly suggest that you create primary and foreign key constraints on the "archive" tables, should that be the approach you want to take.

That being said, I suggest that there is a much better approach. Using your approach, you will have to:

Create an archive table for the parent
Create an archive table for the child
Whenever you change the design of the active parent table, you will likely have to change the design of the archive parent table
Whenever you change the design of the active child table, you will likely have to change the design of the archive child table
Whenever you want to archive data, you should start a transaction, move the rows from both active tables to the archive tables, and commit.
Whenever you want to report on archived data, you will likely need to do a union between the active and archive tables.

Why make all that work for yourself only to separate active data from archive data? Of what real benefit is it? To improve the speed of select statements?

I suggest that you:

Create a column (named for example Archived) in the parent and child tables signifying whether the data is active or archived.
Create views ActiveParents and ActiveChildren, that selects from the table where Archived is true.
Modify the column to "true" when you want to archive data.

This approach saves you a lot of work, of debatable benefit. It also prepares these tables better for data mining.

That's my opinion Anada, based on 10 years of experience with SQL Server.

Richard