How to store multiple message threads in CRM database

Question

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.

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.

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   

Table tbl_Inquiry_History schema:

 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 

Table tbl_User_master schema:

 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)

Table tbl_Question schema:

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

Table tbl_Answer schema:

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

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?

Answer 1

I'm still unclear what goes into the text fields on master and history (and whether the history table should be part of this hierarchy) but - if you use the following as an example:-

create table tbl_Inquiry_master (
    id int,
    body varchar(1024)
);
create table tbl_Inquiry_history (
    id int,
    inquiry_id int,
    body varchar(1024)
);
create table tbl_question (
    id int,
    inquiry_id int,
    body varchar(1024)
);
create table tbl_answer (
    id int,
    question_id int,
    body varchar(1024)
);
insert into tbl_Inquiry_master values (1, 'inquiry one');
insert into tbl_Inquiry_master values (2, 'inquiry two');
insert into tbl_Inquiry_master values (3, 'inquiry three');
insert into tbl_Inquiry_master values (4, 'inquiry four');
insert into tbl_Inquiry_history values (1, 1,'history 1 (relates to inquiry 1)');
insert into tbl_Inquiry_history values (2, 3,'history 2 (relates to inquiry 3)');
insert into tbl_question values (1,1,'inquiry one question one')
insert into tbl_question values (2,1,'inquiry one question two')
insert into tbl_question values (3,2,'inquiry two question one')
insert into tbl_question values (4,4,'inquiry four question one')
insert into tbl_answer values (1,1,'answer 1 to question 1')
insert into tbl_answer values (2,1,'answer 2 to question 1')
insert into tbl_answer values (3,2,'answer 1 to question 2')
insert into tbl_answer values (4,4,'answer 1 to question 4')
insert into tbl_answer values (5,4,'answer 2 to question 4')
insert into tbl_answer values (6,4,'answer 3 to question 4')

select 
    i.id as inquiry_id,
    i.body as master_body,
    h.body as history_body,
    q.body as question_body,
    a.body as answer_body
from tbl_Inquiry_master i
left join tbl_Inquiry_history h on h.inquiry_id=i.id
left join tbl_question q on q.inquiry_id=i.id
left join tbl_answer a on a.question_id=q.id

you end up with:-

inquiry_id  master_body    history_body                      question_body              answer_body
----------- -------------- --------------------------------- -------------------------- ----------------------
1           inquiry one    history 1 (relates to inquiry 1)  inquiry one question one   answer 1 to question 1
1           inquiry one    history 1 (relates to inquiry 1)  inquiry one question one   answer 2 to question 1
1           inquiry one    history 1 (relates to inquiry 1)  inquiry one question two   answer 1 to question 2
2           inquiry two    NULL                              inquiry two question one   NULL
3           inquiry three  history 2 (relates to inquiry 3)  NULL                       NULL
4           inquiry four   NULL                              inquiry four question one  answer 1 to question 4
4           inquiry four   NULL                              inquiry four question one  answer 2 to question 4
4           inquiry four   NULL                              inquiry four question one  answer 3 to question 4

You can then load a repeater with this and, by suppressing repeated text, produce something that may make sense. The output would probably be easier to handle in a hierarchy (XML, etc.) rather than a rectangular result set - but that is an entirely different question.

Answer 2

Ok. Lets start again.

1. Drop the table tbl_Question from your database and erase any memory you have of it ever existing.

2. Drop the table tbl_Answer from your database and erase any memory you have of it ever existing.

3. When a user asks a question, add a row to tbl_Inquiry_master that records the question with a NEW Id. If a user needs to ask multiple questions, add a new row to tbl_Inquiry_master to record each question (each with its own unique Id).

4. When a member of staff replies to a question add a row to tbl_Inquiry_History that records the answer. The Inquiry_id on this row should point back to the Id of the question it relates to in tbl_Inquiry_master. If a different member of staff wants to add a second or subsequent reply to the SAME question, add another row to tbl_Inquiry_History that records their answer with the Inquiry_id on this row pointing back to the SAME Id of the question in tbl_Inquiry_master.

Answer 3

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).

Something like:-

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)

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.