January 27, 2011 at 6:17 pm
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 .[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 .[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 .[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.
March 2, 2011 at 7:42 am
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.
November 20, 2011 at 6:10 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply