การจัดเก็บข้อมูลลงฐานข้อมูล SQL แบบ one-to-many และ เทคนิคการ Query ข้อมูล

วิธีการจัดเก็บข้อมูลแบบ one-to-many หรือ การเก็บข้อมูลแบบที่มีความสัมพันธ์ของข้อมูลจากตารางหลักหนึ่งรายการไปยังข้อมูลของตารางรองหลายรายการ ยกตัวอย่างเช่น ตารางหมวดหมู่ ที่แต่ละหมวดหมู่ มีหมวดหมู่ย่อยอีกหลายรายการเป็นต้น

ยกตัวอย่างในบทความนี้คือ ตารางสมาชิก (ตารางด้านซ้าย) และ ตารางสัตว์เลี้ยงของสมาชิก (ตารางด้านขวา) ที่สมาชิกแต่ละคนสามารถมีสัตว์เลี้ยงได้มากกว่า 1 ชนิด ซึ่งข้อมูลที่มีความสัมพันธ์กันคือ id ของตารางสมาชิก เชื่อมกับ user_id ของตารางสัตว์เลี้ยง

ตารางที่ 1

การเรียกดูข้อมูลเบื้องต้น ใช้การ JOIN ในการรวมข้อมูล 2 ตารางเข้าด้วยกัน

SELECT U.`id`,U.`name`,A.`animal`

FROM `tbl_user` AS U

INNER JOIN `tbl_animal` AS A ON A.user_id =U.`id`

ได้ผลลัพท์ตามตารางด้านล่าง

ตารางที่ 2

ในทางปฏิบัติ ทั่วๆไป การ Query ที่ใช้บ่อยมักจะเป็นการสอบถามว่า สมาชิกแต่ละคนมีสัตว์เลี้ยงอะไรบ้าง ซึ่งจะเขียน Query ได้ว่า

SELECT U.`id`,U.`name`,GROUP_CONCAT(A.`animal`) AS `animal`

FROM `tbl_user` AS U

INNER JOIN `tbl_animal` AS A ON A.user_id =U.`id`

GROUP BY U.`id`

ผลลัพท์ ก็จะเป็นดังตารางด้านล่าง ซึ่งตารางนี้จะเป็นรูปแบบข้อมูลที่มักจะถูก query บ่อยที่สุด

ตารางที่ 3

จะเห็นว่า การเก็บข้อมูลข้างต้นมีความยุ่งยากในการจัดเก็บ แถมยังต้องใช้การ JOIN ร่วมด้วย ซึ่งแน่นอนว่ามันช้ากว่าการไม่ JOIN ซึ่งถ้าเราเก็บข้อมูลสัตว์เลี้ยงลงในตารางข้อมูลสมาชิกเลยจะดีกว่ามั้ย เพราะการเรียกดูข้อมูลไม่ต้อง JOIN อีก

ตารางที่ 4

ตารางด้านบนเป็นตารางข้อมูลใหม่ที่ใช้จัดเก็บข้อมูลสัตว์เลี้ยงลงในตารางเดียวกันเลย ซึ่งข้อมูลในตารางก็เป็นข้อมูลในรูปแบบที่มักใช้บ่อยอยู่แล้ว ทำให้สามารถ Query ไปใช้งานได้ทันที

ข้อสังเกตุ ข้อมูลในตารางมี , (ลูกน้ำ หรือตัวคั่นอื่นใด) ครอบทั้งด้านหน้าและด้านหลังอยู่ ซึ่งมันมีประโยชน์ใช้ในการ Query ได้

การเก็บข้อมูลตามตารางที่ 1 ข้อดีของมันคือ มันสามารถสรุปข้อมูล ค้นหาข้อมูลได้ง่าย เช่น การสอบถามว่ามีใครบ้างที่มีสัตว์เลี้ยงเป็น dog

SELECT U.`id`,U.`name`,A.`animal`

FROM `tbl_user` AS U

INNER JOIN `tbl_animal` AS A ON A.user_id =U.`id`

WHERE A.`animal`='dog'

ตารางที่ 5

หรือการนับจำนวนสัตว์เลี้ยงของแต่ละคน

SELECT U.`id`,U.`name`,COUNT(A.`animal`) AS `animal`

FROM `tbl_user` AS U

INNER JOIN `tbl_animal` AS A ON A.user_id =U.`id`

GROUP BY U.`id`

ตารางที่ 6

ทีนี้เราจะมาดูกันว่า หากเราจัดเก็บข้อมูลตามรูปแบบในตารางที่ 4 เราจะสอบถามข้อมูลเหมือนการเก็บข้อมูลตามตารางที่ 1 ได้หรือไม่
การสอบถามว่ามีใครบ้างที่มีสัตว์เลี้ยงเป็น dog บ้าง

SELECT U.`id`,U.`name`,'dog' AS `animal`

FROM `tbl_user` AS U

WHERE U.`animal` LIKE '%,dog,%'

'%,dog,%' เป็นข้อมูลที่ใช้ในการสอบถาม ตัวคั่น , ที่เพิ่มเข้าไป ใช้เพื่อให้การสอบถามได้ข้อมูลที่ตรงตามเงื่อนไขออกมา ยกตัวอย่างเช่น dog และ dogger ถ้าค้นหาเพียงคำว่า dog ก็จะได้ข้อมูลทั้งสองรายการออกมา แต่ถ้าเราใช้ ,dog, ในการค้นหา เราก็จะได้เพียงรายการ dog ออกมาเท่านั้น
'dog' AS `animal` คำสั่งนี้เป็นแค่การคืนค่าข้อมูลที่ใช้ในการค้นหาออกมาเท่านั้น

ผลลัพท์การ Query จะเป็นรูปแบบเดียวกันกับ ตารางที่ 5

ส่วนการนับจำนวนสัตว์เลี้ยงของแต่ละคน จะอาศัยการ Query ข้อมูลออกมาตามปกติ และอาศัยการนับจำนวนข้อมูลด้วย PHP แทน

// ตัวแปรแอเรย์เก็บผลลัพท์การ Query ที่แปลงข้อมูลแล้ว

$result = array();

// ประมวลผลคำสั่ง SQL (คำสั่งสมมุติ หากใช้งานจริงให้ใช้คำสั่งของ Framework หรือ PHP ที่ใช้ในการเชื่อมต่อฐานข้อมูล)

$db_conn->query('SELECT * FROM `tbl_user`);

foreach($db_conn->exexute() as $item) {

    // ตัดตัวคั่น , ด้านหน้า หลังออก

    $animal = trim($item->animal, ',');

    // แยก animal ออกเป็น Array (สามารถนำไปใช้ประโยชน์อื่นได้ เช่นการสรุปข้อมูลตามตารางที่ 2)

    $animals = explode(',', $animal);

    // คืนค่าผลลัพท์ที่ต้องการ

    $result[] = array(

        'id' => $item->id,

        'name' => $item->name,

        // นับจำนวน animal จากจำนวนข้อมูลในแอเรย์

        'animal' => sizeof($animals);

}

ผลลัพท์ที่ $result ก็จะได้ตามตารางที่ 6

หมายเหตุ การออกแบบฐานข้อมูลที่เหมาะสมให้คำนึงถึง

จัดการกับข้อมูลได้ง่าย
นำไปใช้งานได้ง่าย
สามารถสอบถามข้อมูลได้รวดเร็ว

ยกตัวอย่างเช่น การเก็บข้อมูลสถานะของสมาชิกแบบที่สมาชิกแต่ละคนสามารถมีได้หลายอย่าง การเก็บเป็นข้อมูลเดียวกัน สามารถใช้งานได้ง่ายกว่าการแยกเก็บ เพราะปกติ เราจะร้องขอข้อมูลนี้แค่รอบเดียว เอามาเก็บลงในแอเรย์ไว้ และจะไม่มีการใช้ข้อมูลนี้ที่อื่นอีก