sql - রচন - শিক্ষাক্ষেত্রে তথ্য ও যোগাযোগ প্রযুক্তির গুরুত্ব




একটি রিলেশনাল ডাটাবেস মধ্যে হায়ারারিক্যাল তথ্য সংরক্ষণের জন্য বিকল্প কি কি? (5)

Adjacency মডেল + নেস্টেড মডেল সেট

আমি এটির জন্য গিয়েছিলাম কারণ আমি সহজেই গাছের নতুন আইটেমগুলি সন্নিবেশ করিয়ে দিতে পারি (আপনি এটিতে একটি নতুন আইটেম ঢোকানোর জন্য শুধুমাত্র একটি শাখার আইডি প্রয়োজন) এবং এটি খুব দ্রুত অনুসন্ধান করুন।

+-------------+----------------------+--------+-----+-----+
| category_id | name                 | parent | lft | rgt |
+-------------+----------------------+--------+-----+-----+
|           1 | ELECTRONICS          |   NULL |   1 |  20 |
|           2 | TELEVISIONS          |      1 |   2 |   9 |
|           3 | TUBE                 |      2 |   3 |   4 |
|           4 | LCD                  |      2 |   5 |   6 |
|           5 | PLASMA               |      2 |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |      1 |  10 |  19 |
|           7 | MP3 PLAYERS          |      6 |  11 |  14 |
|           8 | FLASH                |      7 |  12 |  13 |
|           9 | CD PLAYERS           |      6 |  15 |  16 |
|          10 | 2 WAY RADIOS         |      6 |  17 |  18 |
+-------------+----------------------+--------+-----+-----+
  • প্রতিটি সময় আপনি কোন পিতামাতার সব শিশুদের প্রয়োজন আপনি শুধু parent কলাম জিজ্ঞাসা।
  • যদি আপনি কোন পিতামাতার সমস্ত বংশধরদের প্রয়োজন হয় তবে আপনি তাদের আইটেমগুলির জন্য অনুসন্ধান করুন যা তাদের lft lft এবং rgt এর মধ্যে lft আছে।
  • যদি আপনি গাছের রুটি পর্যন্ত কোনও নোডের সমস্ত পিতামাতার প্রয়োজন বোধ করেন, তবে নোডের lft তুলনায় lft চেয়ে ছোট এবং নোডের rgt বড় এবং আপনি parent সাজানোর জন্য অনুসন্ধান করেন।

প্রবেশের চেয়ে দ্রুত গাছটি অ্যাক্সেস এবং ক্যোয়ারী করার প্রয়োজন ছিল, তাই আমি এইটিকে বেছে নিলাম

শুধুমাত্র নতুন সমস্যা সন্নিবেশ করা যখন left এবং right কলাম সংশোধন করা হয়। ভাল আমি এটির জন্য একটি সংরক্ষিত পদ্ধতি তৈরি করেছিলাম এবং প্রত্যেক সময় আমি একটি নতুন আইটেম সন্নিবেশ করলাম যা আমার ক্ষেত্রে বিরল ছিল কিন্তু এটি সত্যিই দ্রুত। আমি জো সেলকোর বই থেকে ধারণাটি পেয়েছি, এবং সংরক্ষিত পদ্ধতি এবং আমি কিভাবে এটি নিয়ে এসেছি তা এখানে DBA SE এ ব্যাখ্যা করা হয়েছে https://dba.stackexchange.com/q/89051/41481

ভাল সংক্ষিপ্ত বিবরণ

সাধারণভাবে বলতে গেলে, আপনি দ্রুত পড়ার সময় (উদাহরণস্বরূপ, নেস্টেড সেট) বা দ্রুত লেখার সময় (সমান্তরাল তালিকা) এর মধ্যে একটি সিদ্ধান্ত নিচ্ছেন। সাধারণত, আপনি নীচের বিকল্পগুলির সমন্বয়ের সাথে শেষ হয়ে যা আপনার প্রয়োজনগুলির জন্য উপযুক্ত। নিম্নলিখিত কিছু গভীরভাবে পড়া উপলব্ধ:

বিকল্প

ওজন আমি সচেতন এবং সাধারণ বৈশিষ্ট্য:

  1. Adjacency তালিকা :
    • কলাম: আইডি, parentID
    • বাস্তবায়ন সহজ।
    • সস্তা নোড প্যাচসমূহ, সন্নিবেশ, এবং মুছে ফেলা।
    • স্তর, বংশধর এবং বংশধর, পথ খুঁজে পেতে ব্যয়বহুল
    • তাদের সমর্থন যে ডাটাবেস মধ্যে সাধারণ টেবিল এক্সপ্রেশন মাধ্যমে N + 1 এড়ান
  2. নেস্টেড সেট (উর সংশোধিত Preorder ট্রি ট্র্যাভেলার্স )
    • কলাম: বাম, ডান
    • সস্তা বংশধর, বংশধর
    • অত্যন্ত ব্যয়বহুল O(n/2) প্যাচসমূহ, সন্নিবেশ, অস্থির এনকোডিং কারণে মুছে ফেলা
  3. সেতু টেবিল (উকো ক্লোজার টেবিল / w ট্রিগার )
    • টেবিলের সাথে পৃথক যোগদান ব্যবহার করে: পূর্বপুরুষ, বংশধর, গভীরতা (ঐচ্ছিক)
    • সস্তা বংশধর এবং বংশধর
    • সন্নিবেশ, আপডেট, মুছে ফেলার জন্য O(log n) খরচ O(log n) (সাবট্রি আকার O(log n) লিখে
    • সাধারন এনকোডিং: RDBMS পরিসংখ্যান এবং যোগদানের পরিকল্পনাকারীর জন্য ভাল
    • নোড প্রতি একাধিক সারি প্রয়োজন
  4. বংশবৃদ্ধি কলাম (উকিল বস্তুগত পথ , পথ পরিমাপ)
    • কলাম: বংশবৃদ্ধি (উদাহরণস্বরূপ / পিতামাতা / শিশু / পিতামহ / ইত্যাদি ...)
    • প্রিফিক্স প্রশ্নের মাধ্যমে সস্তা বংশধর (উদাহরণস্বরূপ LEFT(lineage, #) = '/enumerated/path' )
    • সন্নিবেশ, আপডেট, মুছে ফেলার জন্য O(log n) খরচ O(log n) (সাবট্রি আকার O(log n) লিখে
    • অ-সম্পর্কযুক্ত: অ্যারে ডাটাটাইপ বা সিরিয়ালাইজড স্ট্রিং বিন্যাসে নির্ভর করে
  5. নেস্টেড অন্তর্বর্তীকালীন
    • নেস্টেড সেটের মতো, কিন্তু প্রকৃত / ভাসমান / দশমিকের সাথে যাতে এনকোডিংটি অস্থির হয় না (সস্তা পদক্ষেপ / সন্নিবেশ / মুছুন)
    • আসল / ভাসা / দশমিক উপস্থাপনা / স্পষ্টতা সমস্যা আছে
    • ম্যাট্রিক্স এনকোডিং বৈকল্পিক "মুক্ত" এর জন্য পূর্বপুরুষ এনকোডিং (বস্তুগত পথ) যোগ করে, তবে রৈখিক বীজগণিত যুক্ত ট্রিকস যুক্ত করে।
  6. ফ্ল্যাট টেবিল
    • একটি সংশোধিত অ্যাডজ্যাকেন্সি লিস্ট যা প্রতিটি রেকর্ডে লেভেল এবং র্যাঙ্ক (যেমন অর্ডারিং) কলাম যোগ করে।
    • পুনরাবৃত্তি সস্তা / উপর paginate
    • ব্যয়বহুল সরানো এবং মুছে দিন
    • ভাল ব্যবহার: থ্রেডেড আলোচনা - ফোরাম / ব্লগ মন্তব্য
  7. একাধিক বংশধর কলাম
    • কলাম: প্রতিটি বংশধর স্তরটির জন্য একটি, মূলত সকল পিতামাতার বোঝায়, আইটেমের স্তর থেকে নিম্ন স্তরের নুলে সেট করা হয়
    • সস্তা পূর্বপুরুষ, বংশধর, স্তর
    • সস্তা সন্নিবেশ, মুছে ফেলুন, পাতা সরানো
    • ব্যয়বহুল সন্নিবেশ, মুছে ফেলুন, অভ্যন্তরীণ নোড সরানো
    • অনুক্রম কত গভীর হতে পারে হার্ড সীমা

ডাটাবেস নির্দিষ্ট নোট

মাইএসকিউএল

আকাশবাণী

পোস্টগ্রি

SQL সার্ভার

  • সাধারণ সারাংশ
  • 2008 HierarchyId ডেটা প্রকার প্রস্তাব করে যা বংশবৃদ্ধি কলাম পদ্ধতির সাথে সাহায্য করে এবং গভীরতাটি উপস্থাপন করতে পারে তা প্রকাশ করতে পারে।

আপনার ডাটাবেস অ্যারে সমর্থন করে, আপনি একটি বংশবৃদ্ধি কলাম বা পিতৃত আইডির একটি অ্যারের হিসাবে বস্তুগত পাথ বাস্তবায়ন করতে পারেন।

বিশেষ করে পোস্টগ্রের সাথে আপনি সেট অপারেটরদের অনুক্রমের প্রশ্ন করতে এবং জিআইএন সূচকগুলির সাথে চমৎকার কর্মক্ষমতা পেতে পারেন। এটি একটি একক প্রশ্নের মধ্যে বাবা, শিশু, এবং গভীরতার বেশ তুচ্ছ খুঁজে বের করে তোলে। আপডেট পাশাপাশি বেশ manageable হয়।

আপনি অদ্ভুত যদি আমি বস্তুগত পাথের জন্য অ্যারে ব্যবহার করে একটি সম্পূর্ণ লেখা আছে।


আমি আমার অনুক্রমের জন্য ক্লোজার টেবিল সহ PostgreSQL ব্যবহার করছি। আমার সম্পূর্ণ ডাটাবেসের জন্য একটি সর্বজনীন সংরক্ষিত পদ্ধতি রয়েছে:

CREATE FUNCTION nomen_tree() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
DECLARE
  old_parent INTEGER;
  new_parent INTEGER;
  id_nom INTEGER;
  txt_name TEXT;
BEGIN
-- TG_ARGV[0] = name of table with entities with PARENT-CHILD relationships (TBL_ORIG)
-- TG_ARGV[1] = name of helper table with ANCESTOR, CHILD, DEPTH information (TBL_TREE)
-- TG_ARGV[2] = name of the field in TBL_ORIG which is used for the PARENT-CHILD relationship (FLD_PARENT)
    IF TG_OP = 'INSERT' THEN
    EXECUTE 'INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT $1.id,$1.id,0 UNION ALL
      SELECT $1.id,ancestor_id,depth+1 FROM ' || TG_ARGV[1] || ' WHERE child_id=$1.' || TG_ARGV[2] USING NEW;
    ELSE                                                           
    -- EXECUTE does not support conditional statements inside
    EXECUTE 'SELECT $1.' || TG_ARGV[2] || ',$2.' || TG_ARGV[2] INTO old_parent,new_parent USING OLD,NEW;
    IF COALESCE(old_parent,0) <> COALESCE(new_parent,0) THEN
      EXECUTE '
      -- prevent cycles in the tree
      UPDATE ' || TG_ARGV[0] || ' SET ' || TG_ARGV[2] || ' = $1.' || TG_ARGV[2]
        || ' WHERE id=$2.' || TG_ARGV[2] || ' AND EXISTS(SELECT 1 FROM '
        || TG_ARGV[1] || ' WHERE child_id=$2.' || TG_ARGV[2] || ' AND ancestor_id=$2.id);
      -- first remove edges between all old parents of node and its descendants
      DELETE FROM ' || TG_ARGV[1] || ' WHERE child_id IN
        (SELECT child_id FROM ' || TG_ARGV[1] || ' WHERE ancestor_id = $1.id)
        AND ancestor_id IN
        (SELECT ancestor_id FROM ' || TG_ARGV[1] || ' WHERE child_id = $1.id AND ancestor_id <> $1.id);
      -- then add edges for all new parents ...
      INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT child_id,ancestor_id,d_c+d_a FROM
        (SELECT child_id,depth AS d_c FROM ' || TG_ARGV[1] || ' WHERE ancestor_id=$2.id) AS child
        CROSS JOIN
        (SELECT ancestor_id,depth+1 AS d_a FROM ' || TG_ARGV[1] || ' WHERE child_id=$2.' 
        || TG_ARGV[2] || ') AS parent;' USING OLD, NEW;
    END IF;
  END IF;
  RETURN NULL;
END;
$_$;

তারপরে প্রতিটি টেবিলে যেখানে আমার একটি আধিপত্য আছে, আমি একটি ট্রিগার তৈরি করি

CREATE TRIGGER nomenclature_tree_tr AFTER INSERT OR UPDATE ON nomenclature FOR EACH ROW EXECUTE PROCEDURE nomen_tree('my_db.nomenclature', 'my_db.nom_helper', 'parent_id');

বিদ্যমান অনুক্রম থেকে একটি ক্লোজার টেবিল populating জন্য আমি এই সংরক্ষিত পদ্ধতি ব্যবহার করুন:

CREATE FUNCTION rebuild_tree(tbl_base text, tbl_closure text, fld_parent text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    EXECUTE 'TRUNCATE ' || tbl_closure || ';
    INSERT INTO ' || tbl_closure || ' (child_id,ancestor_id,depth) 
        WITH RECURSIVE tree AS
      (
        SELECT id AS child_id,id AS ancestor_id,0 AS depth FROM ' || tbl_base || '
        UNION ALL 
        SELECT t.id,ancestor_id,depth+1 FROM ' || tbl_base || ' AS t
        JOIN tree ON child_id = ' || fld_parent || '
      )
      SELECT * FROM tree;';
END;
$$;

ক্লোজার টেবিল 3 কলামের সাথে সংজ্ঞায়িত করা হয়েছে - ANCESTOR_ID, DESCENDANT_ID, DEPTH। এটি সম্ভাব্য (এবং আমি এমনকি পরামর্শ) ANCESTOR এবং DESCENDANT এর জন্য একই মান সহ রেকর্ড এবং DEPTH এর জন্য শূন্যের মান সংরক্ষণ করতে পারি। এই অনুক্রম পুনরুদ্ধারের জন্য প্রশ্নের সহজ হবে। এবং তারা সত্যিই খুব সহজ:

-- get all descendants
SELECT tbl_orig.*,depth FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth <> 0;
-- get only direct descendants
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth = 1;
-- get all ancestors
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON ancestor_id = tbl_orig.id WHERE descendant_id = XXX AND depth <> 0;
-- find the deepest level of children
SELECT MAX(depth) FROM tbl_closure WHERE ancestor_id = XXX;

এই নকশা এখনো উল্লেখ করা হয় নি:

একাধিক বংশধর কলাম

যদিও এটির সীমাবদ্ধতা রয়েছে তবে আপনি যদি এটি সহ্য করতে পারেন তবে এটি খুব সহজ এবং খুব কার্যকরী। বৈশিষ্ট্য:

  • কলামগুলি: প্রত্যেক বংশধর স্তরটির জন্য একটি, রুট পর্যন্ত সমস্ত অভিভাবকের বোঝায়, বর্তমান আইটেমের স্তরগুলির নীচে স্তরগুলি NULL এ সেট করা আছে
  • অনুক্রম কত গভীর হতে পারে সীমাবদ্ধ
  • সস্তা পূর্বপুরুষ, বংশধর, স্তর
  • সস্তা সন্নিবেশ, মুছে ফেলুন, পাতা সরানো
  • ব্যয়বহুল সন্নিবেশ, মুছে ফেলুন, অভ্যন্তরীণ নোড সরানো

এখানে একটি উদাহরণ অনুসরণ করা হয়েছে - পাখির করণীয় গাছ যাতে শ্রেণীক্রম / আদেশ / পরিবার / জিন / প্রজাতি - প্রজাতি সর্বনিম্ন স্তর, 1 সারি = 1 ট্যাক্সন (পাতা নোডের ক্ষেত্রে প্রজাতির সাথে সম্পর্কিত):

CREATE TABLE `taxons` (
  `TaxonId` smallint(6) NOT NULL default '0',
  `ClassId` smallint(6) default NULL,
  `OrderId` smallint(6) default NULL,
  `FamilyId` smallint(6) default NULL,
  `GenusId` smallint(6) default NULL,
  `Name` varchar(150) NOT NULL default ''
);

এবং তথ্য উদাহরণ:

+---------+---------+---------+----------+---------+-------------------------------+
| TaxonId | ClassId | OrderId | FamilyId | GenusId | Name                          |
+---------+---------+---------+----------+---------+-------------------------------+
|     254 |       0 |       0 |        0 |       0 | Aves                          |
|     255 |     254 |       0 |        0 |       0 | Gaviiformes                   |
|     256 |     254 |     255 |        0 |       0 | Gaviidae                      |
|     257 |     254 |     255 |      256 |       0 | Gavia                         |
|     258 |     254 |     255 |      256 |     257 | Gavia stellata                |
|     259 |     254 |     255 |      256 |     257 | Gavia arctica                 |
|     260 |     254 |     255 |      256 |     257 | Gavia immer                   |
|     261 |     254 |     255 |      256 |     257 | Gavia adamsii                 |
|     262 |     254 |       0 |        0 |       0 | Podicipediformes              |
|     263 |     254 |     262 |        0 |       0 | Podicipedidae                 |
|     264 |     254 |     262 |      263 |       0 | Tachybaptus                   |

এটি দুর্দান্ত কারণ এভাবে আপনি সমস্ত প্রয়োজনীয় ক্রিয়াকলাপগুলি খুব সহজ ভাবে সম্পন্ন করেন, যতক্ষণ অভ্যন্তরীণ বিভাগগুলি গাছের স্তরগুলি পরিবর্তন করে না।


এটি সত্যিই একটি বর্গাকার খাঁজ, বৃত্তাকার গর্ত প্রশ্ন।

রিলেশনাল ডেটাবেস এবং এসকিউএল আপনার একমাত্র হ্যামার বা ব্যবহার করতে ইচ্ছুক হলে, তারপরে এই পোস্টগুলি এতদূর পোস্ট করা হয়েছে তা যথেষ্ট। তবে, কেন হায়ারার্কিক্যাল তথ্য পরিচালনা করার জন্য ডিজাইন করা একটি টুল ব্যবহার করবেন না? গ্রাফ ডাটাবেস জটিল অনুক্রমিক তথ্য জন্য আদর্শ।

একটি সম্পর্কযুক্ত মডেলের উপরে গ্রাফ / হায়ারার্কিক্যাল মডেলের মানচিত্রের জন্য কোনও কোড / কোয়েরি সমাধানগুলির জটিলতার সাথে সম্পর্কযুক্ত মডেলের অযোগ্যতাগুলি গ্রাফ ডাটাবেস সমাধান একই সমস্যার সমাধান করতে পারে এমন সহজতার তুলনায় প্রচেষ্টার পক্ষে মূল্যহীন নয়।

একটি সাধারণ হায়ারার্কিক্যাল তথ্য কাঠামোর হিসাবে উপকরণ বিল বিবেচনা।

class Component extends Vertex {
    long assetId;
    long partNumber;
    long material;
    long amount;
};

class PartOf extends Edge {
};

class AdjacentTo extends Edge {
};

দুটি উপ-সমাহারগুলির মধ্যে সংক্ষিপ্ততম পথ : সরল গ্রাফ ট্রান্সভারাল অ্যালগরিদম। গ্রহণযোগ্য পাথ মানদণ্ড উপর ভিত্তি করে যোগ্যতাসম্পন্ন হতে পারে।

সাদৃশ্যঃ দুটি সমাহারগুলির মধ্যে মিলটির মিল কি? দুইটি উপ-গাছের সংযোগ বিচ্ছিন্নকরণ এবং ইউনিয়ন উভয় উপ-গাছগুলিতে একটি ট্র্যাভার্সাল সঞ্চালন করুন। অনুরূপ শতাংশ ইউনিয়ন দ্বারা বিভক্ত ছেদ হয়।

ট্রান্সটিভ ক্লোজার : সাব-ট্রি এ যান এবং আগ্রহের ক্ষেত্র (গুলি) যোগ করুন, যেমন "একটি অ্যালুমিনিয়াম কত উপ-সমাবেশে?"

হ্যাঁ, আপনি SQL এবং একটি সম্পর্কীয় ডাটাবেসের সাথে সমস্যাটি সমাধান করতে পারেন। তবে, যদি আপনি কাজের জন্য সঠিক সরঞ্জামটি ব্যবহার করতে ইচ্ছুক হন তবে আরও ভাল পদ্ধতি রয়েছে।





hierarchical-data