Storing multiple customers

Question

I am trying to create a custom technical support tracking database in sql server. I am familiar with Access and can't shake the multivalued column out of my head. I have searched over and over and can only find that the solution would be a many to many relationship but I can't quite work that out, so I am coming here for help.

tblTask

Column Name	Data Type	Notes
TaskID	int	Primary key, identity specification = yes
TaskName	nchar(100)
TaskDescription	nchar(255)
CustomerID	int	Foreign Key goes back to Customer Table

Most of the time there will be only one customer in a tblTask entry. However, there are some instances where multiple customers are requesting assistance in one task. How do I store that information? Do I need to create another table? Please help the uninitiated!

Many thanks.

Answer 1

A many-to-many relationship between tasks and customers is usually modeled with an association table, where the primary key is a composite key consisting of the PK from both tables (identifying relationship).

Below is an example including the customers table you presumably have. I suggest using nvarchar instead of nchar for variable-length character data.

CREATE TABLE dbo.tblTask(
      TaskID int IDENTITY NOT NULL
        CONSTRAINT PK_tblTask PRIMARY KEY CLUSTERED
    , TaskName nvarchar(100)    
    , TaskDescription nvarchar(255) 
);

CREATE TABLE dbo.tblCustomer(
      CustomerID int IDENTITY NOT NULL
        CONSTRAINT PK_tblCustomer PRIMARY KEY CLUSTERED
    , CustomerName nvarchar(100)    
);

CREATE TABLE dbo.tblTaskCustomer(
      TaskID    int NOT NULL
        CONSTRAINT FK_tblTaskCustomer_tblTask FOREIGN KEY REFERENCES dbo.tblTask(TaskID)
    , CustomerID    int NOT NULL
        CONSTRAINT FK_tblTaskCustomer_tblCustomer FOREIGN KEY REFERENCES dbo.tblCustomer(CustomerID)
    --include other attributes relevant for this customer's association with the task
    , CONSTRAINT PK_tblTaskCustomer PRIMARY KEY CLUSTERED (TaskID, CustomerID)
);

If you often query tasks associated with customers, you probably want a non-clustered index (or unique constraint) on CustomerID and TaskID (in that order) to help optimize those queries.

CREATE UNIQUE NONCLUSTERED INDEX idx_tblTaskCustomer_CustomerID_TaskID 
    ON dbo.tblTaskCustomer(CustomerID, TaskID);