To delete or not to delete - DESIGN ISSUE!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

To delete or not to delete - DESIGN ISSUE!

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-04-03 : 04:27:02

Hi all

Need some advice solving a little problem I have with my database!

Current setup:

I have a person table that is made up of 39 columns. I also allow for person records to be deleted but I do this by having another table I call LogicallyDeletedrecords. This table is made up of the PersonId, Reason for deletion/suppression and a date time stamp. To access Live records I created a view based on my Person table which contains a WHERE clause to exclude records that exist in the LogicallyDeletedrecords. Similarly, I have another view DeadPersonData which contains Person records that have been removed. Hope it all makes sense so far! Now on to my worries!

The problem:

My Person table contains 9+ million records. The LogicallyDeletedrecords table has 500k+ but I anticipate further growth over the coming weeks/months. My worry is that my LivePersonData view will be too slow to access as my LogicallyDeletedrecords table grows. What’s more, as part of my Load routine, I have to make sure that Person data loaded on to the system is excluded if that same person exists as a deleted member. Both of these actions could slow down my system as the deleted table grows.

My thoughts:

I’ve been thinking of physically deleting dead Person records from my person table (possibly creating an archive table to hold them). But then if I delete them how do I cross check the details when new Person details get loaded?! As I said, my current LogicallyDeletedrecords table holds the PersonId, ReasonDeleted and CreationStamp. The only way is to add further columns which I use to match Person Details?

Any design suggestions would be welcome!

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-04-04 : 04:29:32

Hi again,

Okay maybe I didn't explain myself properly in my initial post so I will try again in the hope that somebody somewhere will be able to help me out with this little problem!

As I mentioned in the original post. I have a person table as follows :


create table dbo.Person (
	PersonId		int
	,Forename		varchar(50)
	,Surname		varchar(50)
	,DOB			datetime
	,Addr1			varchar(255)
	,Addr2			varchar(255)
	,Addr3			varchar(255)
	,County			varchar(50)
	,Postcode		varchar(8)
	)
go

The actual table is wider but for the sake of this question I will pretend that it's 9 columns wide.

When I want to remove a person I add their details to the following table:


create table dbo.LogicallyDeletedRecord (
	PersonId		int
	,DeletedReason	char(1)
	,CreationStamp	datetime default getdate()
	)

When I want to view live person records (i.e. Person records that have not been deleted), I use the following view:


create view vw_LivePersonData
as
select
	a.PersonId
	,a.Forename
	,a.Surname
	,a.DOB
	,a.Addr1
	,a.Addr2
	,a.Addr3
	,a.County
	,a.Postcode
from
	dbo.Person as a
where not exists (select 1
					from dbo.LogicallyDeletedRecord as b
					where a.PersonId = b.PersonId)
go

Similarly, to view dead person records (i.e. records that have logically deleted), I use the following view:


create view vw_DeadPersonData
as
select
	a.PersonId
	,a.Forename
	,a.Surname
	,a.DOB
	,a.Addr1
	,a.Addr2
	,a.Addr3
	,a.County
	,a.Postcode
from
	dbo.Person as a
where exists (select 1
					from dbo.LogicallyDeletedRecord as b
					where a.PersonId = b.PersonId)
go

This looks like a good design perhaps but my worry is that both the Person table and LogicallyDeletedRecord table will grow and performance will be bad when accessnig the views.

Can anybody suggest a different design? I don't want to physically remove person records that are no longer active.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 04:36:56

i would normally have just 1 table, with a deleted flag

Em

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-04-04 : 05:24:42

Thanks for your reply. I think that is probably the way to go forward with this. Seems like wasted space to have another table to hold these deleted records.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources