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)

Solution

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

Comments

[Gordon Linoff]

What you have done is generally the best practice. Under some circumstances, storing the list as JSON, XML, array, or nested table might be appropriate. However, a separate table for the 1-n relationship is the SQLish way of storing the data.

[Shnugo]

@GordonLinoff Really?

[a_horse_with_no_name]

This is known as the anti-pattern "Entity-Attribute-Value" (search for it). In Postgres I would use a hstore column for that. In other DBMS you might store those key/value pairs as JSON as Gordon mentioned

[Norbert Forgacs]

And what will you decide if for example Jim wasn't really wearing a Mask and a Scarf, but only a pair of glasses?

[Shnugo]

@ForgacsNorbert Just add the pair of glasses as new item to the table Accessory, remove the two entries for Jim and add a pair of Jim's ID and the glasses' ID...

[Shnugo]

@ForgacsNorbert To answer your comment from above that means 3 queries for only one modify request): No, this is not one modify request but actually two or even three of them (remove some items, add glasses)

[Norbert Forgacs]

so a simple DELETE * from PersonAccessory WHERE ... and Insert into PersonAccessory will do. Thanks!

[Norbert Forgacs]

It's a bit hard for me to understand the logic behind without any further explanations

[Rahul]

Hi @ForgacsNorbert please read about STUFF sql function here msdn.microsoft.com/en-us/library/ms188043.aspx & CHARINDEX sql function here msdn.microsoft.com/en-IN/library/ms186323.aspx