การจัดเก็บข้อมูลลงฐานข้อมูล 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
หมายเหตุ การออกแบบฐานข้อมูลที่เหมาะสมให้คำนึงถึง
  1. จัดการกับข้อมูลได้ง่าย
  2. นำไปใช้งานได้ง่าย
  3. สามารถสอบถามข้อมูลได้รวดเร็ว
ยกตัวอย่างเช่น การเก็บข้อมูลสถานะของสมาชิกแบบที่สมาชิกแต่ละคนสามารถมีได้หลายอย่าง การเก็บเป็นข้อมูลเดียวกัน สามารถใช้งานได้ง่ายกว่าการแยกเก็บ เพราะปกติ เราจะร้องขอข้อมูลนี้แค่รอบเดียว เอามาเก็บลงในแอเรย์ไว้ และจะไม่มีการใช้ข้อมูลนี้ที่อื่นอีก
ผู้เขียน Goragod โพสต์เมื่อ 10 มิ.ย. 2561 เปิดดู 1,263 ป้ายกำกับ My SqlPHPSQL

เรื่องที่เกี่ยวข้อง

^