如何在CRM数据库中存储多个消息线程 [英] How to store multiple message threads in CRM database
问题描述
我正在使用客户关系管理应用程序.我有两个表:tbl_Inquiry_master 用于存储最终用户提出的查询问题详细信息，tbl_Inquiry_History 用于存储查询答案详细信息.
I'm working with a Customer Relationship Management application. I have two tables: tbl_Inquiry_master for storing inquiry question details raised by end users, and tbl_Inquiry_History for storing inquiry answer details.
然而，这显示了将一个问题存储到表 tbl_Inquiry_master 中的知识，以及存储在表 tbl_Inquiry_History 中的员工对该查询给出的答案.
However this shows knowledge to store one question store to table tbl_Inquiry_master and answers that are given by staff to that inquiry stored in table tbl_Inquiry_History.
有关更多信息，我表示表 tbl_Inquiry_master 模式:
For more information I represent table tbl_Inquiry_master schema:
Column Name Data Type
_________________________________________
Id varchar(50)
Inquiry_subject varchar(100)
Status_id numeric(18, 0)
Created_date datetime
Priority_id numeric(18, 0)
User_id varchar(50)
Email_Address varchar(50)
Service_id numeric(18, 0)
Inquiry_Content varchar(1024)
TimeStamp datetime
表 tbl_Inquiry_History 架构:
Column Name Data Type
_________________________________________
Id numeric(18, 0)
Inquiry_id varchar(50)
Inquiry_subject varchar(50)
Service_id numeric(18, 0)
Priority_id numeric(18, 0)
User_id varchar(50)
Status_id numeric(18, 0)
Inquiry_desc varchar(1024)
IsDisplay bit
IsRead bit
IsReplied bit
TimeStamp datetime
Activity_start_time datetime
Activity_expire_time datetime
表 tbl_User_master 架构:
Column Name Data Type PK/FK Reg Table Ref Key
____________________________________________________________________________________
Id varchar(50) PK - -
User_id varchar(50) FK tbl_Login_master Id
Full_Name varchar(50)
.
.
Email_Address varchar(50)
表 tbl_Question 架构:
Column Name DatType PK/FK Ref Table Ref Key
____________________________________________________________________
Id int PK - -
UserId varchar(50) FK tbl_User_master Id
Body varchar(1024)
Inquiry_Id varchar(50) FK tbl_Inquiry_master Id
表 tbl_Answer 架构:
Column Name DatType PK/FK Ref Table Ref Key
____________________________________________________________________
Id int PK - -
QuestionId int FK tbl_Question Id
Body varchar(1024)
Inquiry_Id varchar(50) FK tbl_Inquiry_master Id
但是我不知道如何存储多个 Inquiry 的问题(由最终用户提出)和多个 Inquiry 的答案(由员工用户给出).
However I don't know how I can store multiple Inquiry's questions(raised by end users) and multiple Inquiry's answers (given by staff user).
我包含了这个存储过程，用于我如何插入从最终用户生成的新查询以及信息如何存储到表层次结构.
i include this stored procedure for how i insert new inquiry generated from End User and how info stores to table hierarchy.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertInquiry]
(
@Inquiry_subject VARCHAR(50),
@Service_name VARCHAR(50),
@Priority_type VARCHAR(25),
@User_id VARCHAR(50),
@Inquiry_desc VARCHAR(1024),
@email VARCHAR(50),
@NewId VARCHAR(50) OUT
)
AS
SET NOCOUNT ON;
declare @var1 int
declare @var2 int
declare @var3 int
declare @uniqueRef char(14)
set @uniqueRef = dbo.UniqueRefNum(rand(), rand(), rand(), rand())
BEGIN TRAN;
BEGIN TRY
SET @var1= (SELECT [Id] FROM [OmStocks].[dbo].[tbl_Status_master] WHERE (Status_name='Open'))
SET @var2= (SELECT [Id] FROM [OmStocks].[dbo].[tbl_Service_master] WHERE (Service_name=@Service_name))
SET @var3= (SELECT [Id] FROM [OmStocks].[dbo].[tbl_Priority_master] WHERE (Priority_name=@Priority_type))
INSERT INTO [OmStocks].[dbo].[tbl_Inquiry_master]
([Id]
,[Inquiry_subject]
,[Status_id]
,[Created_date]
,[Priority_id]
,[User_id]
,[Email_Address]
,[Service_id]
,[Inquiry_desc])
VALUES
(@uniqueRef,@Inquiry_subject,@var1,CONVERT(DATETIME,GETDATE(), 101),@var3,@User_id,@email,@var2,@Inquiry_desc)
INSERT INTO [OmStocks].[dbo].[tbl_Question]
([UserId],[Body],[Inquiry_Id])
VALUES
(@User_id,@Inquiry_desc,@uniqueRef)
INSERT INTO [OmStocks].[dbo].[tbl_Inquiry_History]
([Inquiry_id]
,[Inquiry_subject]
,[Service_id]
,[Priority_id]
,[User_id]
,[Status_id]
,[Inquiry_desc]
,[IsDisplay]
,[IsRead]
,[IsReplied]
,[Activity_start_time])
VALUES
(@uniqueRef,@Inquiry_subject,@var2,@var3,@User_id,@var1,@Inquiry_desc,0,0,0,CONVERT(DATETIME,GETDATE(), 101))
SET @NewId= @uniqueRef
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
-- Raise the error with the appropriate message and error severity
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
RAISERROR(@ErrMsg, @ErrSeverity, 1);
END CATCH;
有人可以建议我对架构进行哪些更改以存储此方案的数据吗?
Can someone suggest me the changes I need to make to my schema to store data for this scenario?
推荐答案
简单来说，您需要一个 User 表、一个 Question 表和一个 Answer 表，它们按顺序形成层次结构.每个 Answer 行都有一个返回到它相关的 Question 的外键.每个问题行都有一个返回给提出问题的用户的外键(我想用户会有一个返回到客户/合同等的外键).然后，您将使用仅与该实体相关的属性来装饰每个实体.您的历史记录表目前似乎更像是一个审计跟踪，而不是一个答案表(来自主表的许多非规范化列).
In simple terms you need a User table, a Question table and an Answer table which form a hierarchy in that order. Each Answer row has a foreign key back to the Question it relates to. Each Question row has a foreign key back to the User that asked the question (I imagine a User would have a foreign key back to a customer/contract, etc.). You would then decorate each entity with the attributes that ONLY relate to that entity. Your history table appears to be more of an audit trail at the moment, rather than an Answer table (lots of de-normalised columns from the master table).
类似:-
User table
---------------------------
Id int
Email varchar(50)
Question table
---------------------------
Id int
UserId int
Body varchar(1024)
Answer table
---------------------------
Id int
QuestionId int
Body varchar(1024)
然后在适当的级别添加架构中的列.例如，Status_id 是否描述了问题或答案的状态.我想 Priority_id 与问题相关，而不是与答案相关，所以它应该只存在于问题表中.
Then you add the columns from your schema at the appropriate level. Does Status_id describe the status of the question or the answer, for example. I imagine Priority_id relates to the Question and not to the Answer, so it should only exist in the Question table.
在不知道每一列的实际用途的情况下，很难给出与您的架构直接相关的更具体的答案.
Without knowing the actual use of each column, it's difficult to give a more specific answer that relates directly to your schema.
这篇关于如何在CRM数据库中存储多个消息线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
