CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Dim
Dim

Reputation: 453

Store multiple ids into one column

The main idea is to store multiple ids from areas into one column. Example 

Area A  id=1
Area B  id=2

I want if it is possible to save into one column which area my customer can service. Example if my customer can service both of them to store into one column, I imagine something like:

ColumnArea
1,2 //....or whatever area can service

Then I want using an SQL query to retrieve this customer if contains this id.

Select * from customers where ColumnArea=1

Is there any technique or idea making that?

Upvotes: 0

Views: 4552

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

You really should not do that.
Storing multiple data points in a single column is bad design. For a detailed explanation, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

What you want to do in this situations is create a new table, with a relationship to the existing table. In this case, you will probably need a many-to-many relationship, since clearly one customer can service more than one area, and I'm assuming one area can be serviced from more than one customer.

A many-to-many relationship is generating by connection two tables containing the data with another table containing the connections between the data (A.K.A bridge table). All relationships directly between tables are either one-to-one or one-to-many, and the fact that there is a bridge table allows the relationship between both data tables to be a many-to-many relationship.

So the database structure you want is something like this:

Customers Table
    CustomerId (Primary key)
    FirstName
    LastName
    ... Other customer related data here

Areas Table
    AreaId (Primary key)
    AreaName
    ... Other area related data here

CustomerToArea table
    CustomerId
    AreaId
    (Note: The combination of both columns is the primary key here)

Then you can select customers for area 1 like this:

SELECT C.*
FROM Customers AS C
WHERE EXISTS
(
    SELECT 1
    FROM CustomerArea As CA
    WHERE CA.CustomerId = C.CustomerId
    AND AreaId = 1
)

Upvotes: 3

Related Questions