I need help with my table design

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Strategies » I need help with my table design

I need help with my table design

Rate Topic

Display Mode

Topic Options

Author

Message

dlam 18073

Posted Thursday, January 27, 2011 6:17 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 2:58 PM
Points: 38, Visits: 145

Hi All,

The database Iam working on is MSSQL2000.
The database got a call log table [tblCallLog] (with 500k rows), and now I need to store some update details in some of the calls.
The log table is not able to modify, so I think I need to add a table [tblLastCall] to store those changes.
the [tblLastCall] table is going to store the latest call for each phone numbers.
when I got a group of phone number details update, it will store into [tblLastCall] table, cuz only the latest call information will be use.

Below is the tables:

CREATE TABLE [user].[tblCallLog](
	[id] [int] IDENTITY(1,1) NOT NULL, << PK
	[dateCall] [datetime] NULL,
	[status] [nchar](10) NULL,
	[phNu] [int] NOT NULL,
	[firstName] [nvarchar](40) NULL,
	[lastName] [nvarchar](40) NULL,
	[address1] [nvarchar](40) NULL,
	[address2] [nvarchar](40) NULL,
	[address3] [nvarchar](40) NULL
)

CREATE TABLE [user].[tblLastCall](
	[id] [int] IDENTITY(1,1) NOT NULL, << PK
	[dateCall] [datetime] NULL,
	[status] [nchar](10) NULL,
	[phNu] [int] NOT NULL,
	[firstName] [nvarchar](40) NULL,
	[lastName] [nvarchar](40) NULL,
	[address1] [nvarchar](40) NULL,
	[address2] [nvarchar](40) NULL,
	[address3] [nvarchar](40) NULL
)

CREATE TABLE [user].[tblNewNumbers](
	[phNu] [int] NOT NULL,  <<PK
	[firstName] [nvarchar](40) NULL,
	[lastName] [nvarchar](40) NULL,
	[address1] [nvarchar](40) NULL,
	[address2] [nvarchar](40) NULL,
	[address3] [nvarchar](40) NULL,
	[IsNew] [nchar](2) NULL
)

the [tblCallLog] table and the [tblLastCall] table is the same, but the Last one is used to store update details without modify the row in the callLog table.

Question:
I need to create two store procedures,
one for Insert call logs - insert call log to [tblCallLog] (~2000 to 6000 rows need to insert)
- insert call log to [tblLastCall]
- delete old log for that phone number in [tblLastCall] (only store the latest call for each phone number)

another one for update details - check [tblNewDetails] (~2000 to 4000 rows need to check) if that is a existing number in [tblLastCall]
- if yes, update details
- if No, set [tblNewNumbers].[IsNew] = Y

Yes, I should provide some code, but my first question is - is this design OK? (two table contain some double information)
cuz what i want to achieve is - when some of the numbes in [tblNewnumbers] exists in [tblCallLog] table,
also has different details, and I need to store those new details.
Then I got the plan above.

Post #1055032

ChazMan

Posted Wednesday, March 02, 2011 7:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 12:40 PM
Points: 176, Visits: 237

To answer your question: "is this design OK?"

Yes and No. There are different approaches to storing historial data and different needs on accessing historical data. Not knowing your particulars, I can't give an actual opinion, but here are my current thoughts:

If users are going to frequently want the last record's information, then your approach could be ok. Your approach is speeding up inquiries on the last record at the cost of increased inserts due to the subsequent activity to keep only the most recent record in the table.

If, however, users do not frequently look for the most recent record and they need inserts to be as fast as possible, then your solution is focused on the wrong part of the problem. If this is the case, create a status flag that indicates it's the most recent record and call it good.

I see many people have viewed this post but without replies. Perhaps my posting will break the ice.

--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.

Post #1071924

Bill Hansen

Posted Sunday, November 20, 2011 6:10 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 565, Visits: 276

I wouldn’t create a separate table for LastCall and I would reduce the redundant fields. You could take tblNewNumbers and call it Contacts and add a ContactId IDENTITY column that will serve as a PRIMARY KEY to establish relationships.

Then I would create a CallLog table with a callLogId, contactId and callDate. You can create one stored proc to do the inserts and you can create another stored proc that takes contactId as input and returns the MAX(callDate) for the contactId.

Post #1208988

« Prev Topic | Next Topic »

Permissions