SQL Database multiple values for same attribute - Best practices?

Question

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)

Answer 1

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