SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I need help with my table design


I need help with my table design

Author
Message
dlam 18073
dlam 18073
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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.
ChazMan
ChazMan
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 247
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.
Bill Hansen
Bill Hansen
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 336
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search