2

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)

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Norbert Forgacs
  • 605
  • 1
  • 8
  • 27
  • 1
    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. – Gordon Linoff Feb 20 '17 at 13:43
  • @GordonLinoff Really? – Shnugo Feb 20 '17 at 13:54
  • 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 –  Feb 20 '17 at 13:59

2 Answers2

6

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
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • And what will you decide if for example Jim wasn't really wearing a Mask and a Scarf, but only a pair of glasses? – Norbert Forgacs Feb 20 '17 at 14:07
  • @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 Feb 20 '17 at 14:12
  • @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) – Shnugo Feb 20 '17 at 14:16
  • so a simple DELETE * from PersonAccessory WHERE ... and Insert into PersonAccessory will do. Thanks! – Norbert Forgacs Feb 20 '17 at 14:18
0

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
Rahul
  • 199
  • 8
  • It's a bit hard for me to understand the logic behind without any further explanations – Norbert Forgacs Feb 21 '17 at 07:01
  • Hi @ForgacsNorbert please read about STUFF sql function here https://msdn.microsoft.com/en-us/library/ms188043.aspx & CHARINDEX sql function here https://msdn.microsoft.com/en-IN/library/ms186323.aspx – Rahul Feb 21 '17 at 08:53