SQL tip ไม่ควรเก็บข้อมูลแบบ comma separate values

เชื่อว่าหลายคนที่ใช้งาน SQL ไมว่าจะใช้ database server ค่ายใดก็ตาม เช่น MySQL , SQL Server, Oracle, etc คงเคยเก็บข้อมูลบาง column ในรูปแบบ comma separated values กันมาบ้าง เช่นข้อมูล list id ของ user เพื่อทำให้ลดการ join table และอื่นๆที่เราคิดว่าทำให้ระบบดูแลได้ง่าย หรือ performance ที่ดีขึ้น

แต่จริงๆ แล้วการเก็บข้อมูลในรูปแบบ comma separated values เป็นวิธีการที่เราไม่ควรทำครับ

ผมมีตัวอย่าง SQL ที่จะทำให้เห็นภาพมากขึ้นครับ เป็นการสร้าง table tasks ที่ column ที่ชื่อว่า assigned_to เก็บข้อมูล id ของ user ที่ได้รับมอบหมายงานนี้ โดยงานหนึ่งงาน สามารถถูกมอบหมายให้กับ user หลายคนได้

table tasks

CREATE TABLE tasks(
 id INT PRIMARY KEY,
 title VARCHAR(255),
 details VARCHAR(2000),
 assigned_to VARCHAR(100)
)

ถ้าดูกันดีๆ ข้อเสียที่เกิดขึ้นคือ

• query ที่ซับซ้อน และไม่ได้เป็นมาตรฐานเหมือนกับ database ทุกค่าย เนื่องจากเราจำเป็นต้องใช้ regular expression เข้ามาช่วยในการ query เช่น

SELECT * FROM tasks WHERE assigned_to REGEXP '[[:<:]]1[[:>:]]';

• ไม่สามารถที่จะใช้ความสามารถของ index ที่ column assigned_to เพื่อทำให้การค้นหาข้อมูลทำได้เร็วขึ้นได้

• การ update, insert, delete ทำได้ยุ่งยาก เนื่องจากต้อง mange กับ list ของ user id ในบางกรณี ไม่สามารถที่จะทำจาก SQL tool โดยตรง ต้องเขียน application code ช่วย

• การ group by, count by มีซับซ้อนในการเขียน query

• เราไม่สามารถจะกำหนดขนาดของ column ได้ถูกต้อง เช่นถ้าเราเปลี่ยน data type ของ assigned_to เป็น varchar(10) ถ้า user id เป็นตัวเลขน้อยๆ เราก็เก็บได้เยอะคน เช่น 1,2,3,4,5 แต่ถ้า id ของ user เป็นตัวเลขใหญ่ขึ้น ก็เก็บได้จำนวนน้อยลงเช่น 1234,4567

• ไม่มีตัวช่วย check ความถูกต้องของข้อมูล กล่าวคือ assigned_to สามารถใส่ค่าอะไรไปก็ได้ ทั้งๆ ที่ควรใส่ได้แค่ตัวเลขเท่านั้น

แนวทางแก้ไขที่ถูกต้อง

วิธีการแก้ไขการเก็บข้อมูลเป็น comma separated values ให้ใช้วิธีการสร้าง intersection table ขึ้นมาครับ โดยที่ table tasks และ users มีความสัมพันธ์แบบ many to many และมี table task_assigned_to เป็น intersection table อยู่ตรงกลางเป็นตัวเชื่อมความสัมพันธ์

ตัวอย่าง SQL

table tasks

CREATE TABLE tasks(
 id INT PRIMARY KEY,
 title VARCHAR(255),
 details VARCHAR(2000)
)

table users

CREATE TABLE users(
 id INT PRIMARY KEY,
 name VARCHAR(255)
)

insertion table

CREATE TABLE tasks_assigned_to(
 task_id INT NOT NULL,
 user_id INT NOT NULL,
 PRIMARY KEY ( task_id,user_id)
)

เพียงเท่านี้ก็สามารถแก้ไขปัญหาต่างๆ เหล่านั้นไปได้แล้วครับผม และที่ table tasks_assigned_to เราก็สามารถเพิ่ม column อื่นๆ เข้าไปใช่วันที่ assign งานก็ได้ครับ และการใช้คำสั่ง join ไม่ได้แย่เสมอไปครับ หาก join เพียง join เดียว และ join ผ่าน index column