CODEWITHSUNDEEP
sqldatabaset-sqlmultivalue
Norbert Forgacs
Norbert Forgacs

Reputation: 593

SQL Database multiple values for same attribute - Best practices?

I have found myself that some attributes from my Person table, need to hold multiple values/choices, which is not a good SQL practice so I created a second table, like this:

Before:

Person table
-ID (ex. 101)
-Name (ex. John)
-Accessories (ex. Scarf, Mask, Headband, etc..) - One person can have a combination of this

After:

Person Table
-ID
-Name

PersonDetails Table
-PersonID (FK to Person table)
-Attribute type
-Attribute value

and an example:

Person: 
ID:13; Name: John Snow
PersonDetails:
PersonID: 13; Attribute type: Accessories; Attribute value: Scarf
PersonID: 13; Attribute type: Accessories; Attribute value: Mask

You can see that person with ID 13 has both Scarf and Mask.

Is this a good practice? What other ways are there to do this the most efficiently?

Also, what ways are there if an update comes up and Person with 13 doesn't have Scarf and Mask but only Glasses? (Delete the 2 separately and insert a new one? that means 3 queries for only one modify request)

Upvotes: 2

Views: 14785

Answers (2)

Rahul
Rahul

Reputation: 209

Here is a working example. Check this out 

;with tmp(Personid, name,AttributeType, DataItem, Data) as (
    select Personid, name,'Accessories' AttributeType, LEFT(Accessories, CHARINDEX(',',Accessories +',')-1),
        STUFF(Accessories , 1, CHARINDEX(',',Accessories +','), '')
    from Person
    union all
    select Personid, name,'Accessories' AttributeType, LEFT(Data, CHARINDEX(',',Data+',')-1),
        STUFF(Data, 1, CHARINDEX(',',Data+','), '')
    from tmp
    where Data > ''
    )
    select Personid, name,AttributeType, DataItem
    from tmp
    order by Personid

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

I think this is rather n:m-related. You'd need one table Person holding ID, name and other person's details. Another table Accessory with ID, name and more accessory's details. And a third table PersonAccessory to store pairs of PersonID and AccessoryID (this is called mapping table)

Working example (SQL-Server syntax)

CREATE TABLE Person(ID INT IDENTITY PRIMARY KEY,Name VARCHAR(100));
INSERT INTO Person VALUES('John'),('Jim');

CREATE TABLE Accessory(ID INT IDENTITY PRIMARY KEY,Name VARCHAR(100));
INSERT INTO Accessory VALUES('Scarf'),('Mask');

CREATE TABLE PersonAccessory(PersonID INT NOT NULL FOREIGN KEY REFERENCES Person(ID)
                            ,AccessoryID INT NOT NULL FOREIGN KEY REFERENCES Accessory(ID));
INSERT INTO PersonAccessory VALUES(1,1),(2,1),(2,2);

SELECT p.Name
      ,a.Name 
FROM PersonAccessory AS pa
INNER JOIN Person AS p ON pa.PersonID=p.ID
INNER JOIN Accessory AS a ON pa.AccessoryID=a.ID;
GO

--DROP TABLE PersonAccessory;
--DROP TABLE Accessory;
--DROP TABLE Person

The result

John    Scarf
Jim     Scarf
Jim     Mask

Upvotes: 7

Related Questions