Unlimited Plugins, WordPress themes, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Code
  2. Databases
Code

শিক্ষানবিসদের জন্য এসকিউএল: তৃতীয় ভাগ - ডাটাবেজ রিলেশনশিপস

by
Length:MediumLanguages:

Bengali (বাংলা) translation by Syeda Nur-E-Royhan (you can also view the original English article)

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

আপনি চাইলে এনভাটো মার্কেটে এসকিউএল স্ক্রিপ্ট, অ্যাপ্লিকেশন এবং অ্যাড-অন পরখ করে এসকিউএল ডাটাবেজের কাজ দেখে নিতে পারেন।

ক্যাচ আপ

ভূমিকা

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

ডাটাবেজ রিলেশনশিপ বিভিন্ন ধরণের হয়। আজ আমরা নিচেরগুলো সম্পর্কে জানবো:

  • একের সাথে একের পারস্পরিক ক্রিয়া
  • একের সাথে অনেকের এবং অনেকের সাথে একের পারস্পরিক ক্রিয়া 
  • অনেকের সাথে অনেকের পারস্পরিক ক্রিয়া 
  • সেলফ রেফারেন্সিং রিলেশনশিপস

পারস্পরিক ক্রিয়াযুক্ত বিভিন্ন টেবিল থেকে ডাটা সিলেক্ট করার সময় আমরা জয়েন কোয়েরি ব্যবহার করবো। কয়েক ধরণের জয়েন রয়েছে। আজ আমরা নিচের ধরণটি সম্পর্কে জানবো:

  • ক্রস জয়েনস
  • ন্যাচারাল জয়েনস
  • ইনার জয়েনস
  • লেফট (আউটার) জয়েনস
  • রাইট (আউটার) জয়েনস

আমরা অন ক্লজ এবং ইউজিং ক্লজ সম্পর্কেও জানবো।

একের সাথে একের পারস্পরিক ক্রিয়া 

ধরে নেই, আপনি কাস্টমারদের জন্য একটি টেবিল তৈরি করেছেন:

আমরা কাস্টমারদের ঠিকানা আরেকটি ভিন্ন টেবিলে নিয়ে আসতে পারি:

এখন আমাদের কাস্টমার টেবিল আর অ্যাড্রেস টেবিলের মধ্যে একটি পারস্পরিক ক্রিয়া তৈরি হয়ে গিয়েছে। যদি প্রতিটি অ্যাড্রেস একজন মাত্র কাস্টমারের হয়ে থাকে তাহলে এই পারস্পরিক ক্রিয়াটি হচ্ছে "ওয়ান টু ওয়ান" বা একের সাথে একের। মনে রাখবেন যে এই ধরণের পারস্পরিক ক্রিয়া খুব বেশি দেখা যায় না। আমাদের আগের টেবিলটিতে যেখানে অ্যাড্রেস কাস্টমারের নামের সাথেই থাকতো সেটি দিয়েও বেশিরভাগ ক্ষেত্রেই ভালোভাবে কাজ সারা যেতো। 

খেয়াল করে দেখুন যে এখন এখানে "address_id" নামে কাস্টমারস টেবিলে একটি জায়গা তৈরি হয়েছে। এটি অ্যাড্রেস টেবিলে ম্যাচিং রেকর্ডকে ইঙ্গিত করে। এটার নাম "ফরেন কী"। এটি সব ধরণের ডাটাবেজ রিলেশনশিপের জন্য ব্যবহৃত হয়। এই আর্টিকেলের শেষে আমরা এই বিষয়টি নিয়ে আলাপ করবো।

আমরা কাস্টমার এবং অ্যাড্রেসের রেকর্ডগুলোর মধ্যে পারস্পরিক ক্রিয়া নিচের উদাহরণের আকারে দেখতে পারব:

মনে রাখবেন যে পারস্পরিক ক্রিয়ার থাকা না থাকাটা অত্যাবশ্যকীয় কিছু নয়। যেমন, এমন কোন কাস্টমার থাকতে পারে যার সাথে কোন অ্যাড্রেস দেওয়া নেই।

একের সাথে অনেকের এবং অনেকের সাথে একের পারস্পরিক ক্রিয়া

এটি সবচাইতে বহুল ব্যবহৃত পারস্পরিক ক্রিয়া। নিচের তথ্যসহ একটি ই-কমার্স ওয়েবসাইট কল্পনা করে নিন:

  • কাস্টমার চাইলে অনেকগুলো অর্ডার করতে পারেন।
  • সেই অর্ডারে আবার অনেকগুলো আইটেম থাকতে পারে।
  • আইটেমগুলোর বিবরণ বিভিন্ন ভাষায় দেওয়া থাকতে পারে।

এক্ষেত্রে আমাদের "একের সাথে অনেকের" পারস্পরিক ক্রিয়া তৈরি করতে হবে। এখানে একটি উদাহরণ দেওয়া হল:

প্রতিটি কাস্টমারের শূন্য থেকে শুরু করে অনেকগুলো অর্ডার থাকতে পারে। কিন্তু একটি অর্ডার শুধুমাত্র একজন কাস্টমারেরই হতে পারে।

অনেকের সাথে অনেকের পারস্পরিক ক্রিয়া

কিছু কিছু ক্ষেত্রে, পারস্পরিক ক্রিয়ার উভয় পক্ষেই আপনার একাধিক পরিস্থিতি থাকতে পারে। যেমন, প্রতিটি অর্ডারে একাধিক আইটেম থাকতে পারে। আবার প্রতিটি আইটেমের একাধিক অর্ডার থাকতে পারে।

এইসব পারস্পরিক ক্রিয়ার জন্য আমাদের বাড়তি আরেকটি টেবিল দরকার হবে:

Items_Order টেবিলের একটাই কাজ আর তা হচ্ছে আইটেম এবং অর্ডারের মধ্যে "অনেকের সাথে অনেকের" পারস্পরিক ক্রিয়া তৈরি করা।

এই ধরণের পারস্পরিক ক্রিয়া দেখতে হলে যা করতে হবে:

Items_orders রেকর্ডগুলো গ্রাফে যোগ করতে চাইলে তা অনেকটা নিচের দৃশ্যের মতো দেখাবে:

সেলফ রেফারেন্সিং রিলেশনশিপস

একটা টেবিলে যখন নিজের সাথেই পারস্পরিক ক্রিয়া তৈরি করতে হয় তখন এই পদ্ধতি ব্যবহৃত হয়। যেমন, ধরুন আপনার একটি রেফারাল প্রোগ্রাম রয়েছে। কাস্টমাররা চাইলে অন্যান্য কাস্টমারদের কাছে আপনার ওয়েবসাইটের কথা রেফার করতে পারে। সেক্ষেত্রে টেবিলটা অনেকটা এমন দেখাবে:

১০২ এবং ১০৩ নাম্বার কাস্টমার এসেছেন ১০১ নাম্বার কাস্টমারের রেফারেন্স অনুযায়ী।

অন্যভাবে বলতে গেলে এটা "একের সাথে অনেকের" পারস্পরিক ক্রিয়ার মতোই, যেহেতু একজন কাস্টমার অনেকজন কাস্টমারকে রেফার করতে পারেন। এই পারস্পরিক ক্রিয়া ট্রি স্ট্রাকচার দিয়েও বুঝানো যায়:

একজন কাস্টমার শূন্য, এক বা একাধিক কাস্টমারকে রেফার করতে পারেন। তবে প্রতিটি কাস্টমার একজন মাত্র কাস্টমারের মাধ্যমে রেফার পেতে পারেন। আর নইলে একজনও না।

সেলফ রেফারেন্সিং "অনেকের সাথে অনেকের" পারস্পরিক ক্রিয়া তৈরি করতে চাইলে গত সেকশনে যেভাবে বলেছিলাম ঠিক সেভাবে একটি বাড়তি টেবিল তৈরি করতে হবে।

ফরেন কীজ

এখন পর্যন্ত আমরা কয়েকটি প্রজেক্ট সম্পর্কে জানতে পেরেছি মাত্র। এখন এসকিউএলের মাধ্যমে এগুলোকে কাজে লাগাতে হবে। এই কাজের জন্য ফরেন কী সম্পর্কে বুঝতে হবে।

উপরের পারস্পরিক ক্রিয়ার উদাহরণ অনুযায়ী, অন্য একটি টেবিলে উল্লেখিত কলামের এই "****_id" স্থানগুলো সব সময়ই ছিল। এই উদাহরণে দেখা যায়, অর্ডার টেবিলের customer_id কলামটি মূলত একটি ফরেন কী কলাম:

মাইএসকিউএলের মতো ডাটাবেজ দিয়ে দুই পদ্ধতিতে ফরেন কীজ কলাম তৈরি করা যায়: 

ফরেন কী সম্পর্কে বিশদ বিবরণ দিয়ে

চলুন একটি সাধারণ কাস্টমার টেবিল তৈরি করি:

এখন ফরেন কী রয়েছে এমন একটি অর্ডার টেবিল তৈরি করি:

দুইটি কলামেই (customers.customer_id এবং orders.customer_id) হুবহু একই রকম ডাটা স্ট্রাকচার থাকতে হবে। যেমন, একটা যদি INT হয়ে থাকে তাহলে আরেকটা কোনভাবেই BIGINT হতে পারবে না।

মনে রাখবেন যে মাইএসকিউএলে শুধুমাত্র ইনোডিবি ইঞ্জিনই ফরেন কীর জন্য পুরোপুরি সহায়ক। তবে অন্যান্য স্টোরেজ ইঞ্জিনও কোন রকম এরর ছাড়াই এগুলোকে নির্দিষ্ট করতে সহায়তা করবে। আবার, ফরেন কী কলামটি স্বয়ংক্রিয়ভাবে তালিকাভুক্ত হয়ে যায় যদি না আপনি আরেকটি তালিকায় এটিকে যুক্ত করেন।

কোনরকম বিশদ বিবরণ ছাড়াই

Customer_id কলামটিকে ফরেন কী হিসেবে নির্দিষ্ট না করেই একই রকম অর্ডারের টেবিল তৈরি করা যায়:

ডাটাবেজ ইঞ্জিন এই ধরণের পারস্পরিক ক্রিয়া সম্পর্কে অবগত না থাকলেও আপনি জয়েন কোয়েরির সাহায্যে ডাটা উদ্ধার করার সময় এই কলামটিকে ফরেন কী হিসেবে কাজে লাগাতে পারেন।

আমরা এই আর্টিকেলে জয়েন কোয়েরি সম্পর্কে আরও জানবো।

পারস্পরিক ক্রিয়া উন্মোচিত করা

এই মুহূর্তে ডাটাবেজ ডিজাইন করার জন্য এবং ফরেন কীর মধ্যে পারস্পরিক ক্রিয়া উন্মোচন করার জন্য সবচাইতে পছন্দের সফটওয়্যার হচ্ছে মাইএসকিউএল ওয়ার্কবেঞ্চ

একবার আপনার ডাটাবেজ ডিজাইন করা হয়ে গেলে সহজেই এসকিউএল এক্সপোর্ট করে আনতে পারবেন এবং আপনার সার্ভারে চালু করতে পারবেন। আরও বড় ও জটিল ডাটাবেজ ডিজাইন করতে গেলে এটি খুব কাজে দেয়।

জয়েন কোয়েরিজ

পারস্পরিক ক্রিয়া রয়েছে এমন ডাটাবেজ থেকে ডাটা উদ্ধার করতে গেলে আমাদের প্রায়ই জয়েন কোয়েরি ব্যবহার করার দরকার হয়।

শুরু করার আগে চলুন কাজ করার জন্য কিছু টেবিল এবং স্যাম্পল ডাটা তৈরি করে ফেলি।

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

ক্রস জয়েন

এটা এক ধরণের ডিফল্ট জয়েন কোয়েরি যেখানে কোন ধরণের শর্তাবলী নির্দিষ্ট করা নেই।

এর ফলাফল হবে টেবিলগুলোর এক ধরণের " কার্টেসিয়ান প্রোডাক্ট"। তার মানে প্রথম টেবিলের প্রতিটি সারি দ্বিতীয় টেবিলের প্রতিটি সারির সাথে মিলে যাবে। যেহেতু প্রতিটি টেবিলে ৪টি করে সারি রয়েছে, কাজেই আমরা সবশেষে মোট ১৬টি সারি পাবো। 

জয়েন কীওয়ার্ডগুলো কমা দিয়ে প্রতিস্থাপন করা যায়।

অবশ্যই এই ধরণের ফলাফল সব সময় কার্যকরী না। কাজেই চলুন অন্যান্য জয়েন কোয়েরিগুলো পরীক্ষা করে দেখি।

ন্যাচারাল জয়েন

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

আপনি দেখতে পাচ্ছেন যে customer_id কলামটি এখানে মাত্র একবার দেখাচ্ছে। এর কারণ হচ্ছে ডাটাবেজ ইঞ্জিন এটিকে কমন কলাম হিসেবে দেখছে।  অ্যাডাম যেই দুইটি অর্ডার করেছে তা আমরা দেখতে পাচ্ছি। জো এবং স্যান্ডি যে অর্ডারগুলো করেছে তাও আমরা দেখতে পাচ্ছি। অবশেষে আমরা কিছু দরকারি তথ্য দেখতে পাচ্ছি।

ইনার জয়েন

কোন জয়েন কন্ডিশন যদি নির্দিষ্ট করা থাকে তাহলে সেখানে ইনার জয়েন কাজ করে। সেই ক্ষেত্রে, customer_id জায়গাটি দুই টেবিলেই ম্যাচ করা গেলে ভালো। এর ফলাফল ন্যাচারাল জয়েনের মতোই হবে।

একটা ছোট্ট পার্থক্য ছাড়া ফলাফল একই হবে। Customer_id কলামটি প্রতি টেবিলে একবার করে দুইবার দেখাবে।  এর কারণ হচ্ছে, আমরা এই দুই কলামের ভ্যালু ম্যাচ করানোর জন্য ডাটাবেজকে নির্দেশ দেইনি। কাজেই এরা যে মূলত একই তথ্য প্রদান করছে তা বুঝতে পারে না।

চলুন কোয়েরিতে আরকিছু কন্ডিশন যোগ করি।

এবার আমরা শুধুমাত্র ১৫ ডলারের উপরের অর্ডার নিয়েছি।

অন ক্লজ

অন্যান্য রকমের জয়েন নিয়ে কাজ করার আগে অন ক্লজের দিকে খেয়াল করতে হবে। জয়েন কন্ডিশনগুলোকে আলাদা ক্লজে উপস্থাপন করলে তা কার্যকরী হবে।

এখন আমরা হ্যোয়ার ক্লজ কন্ডিশনের সাথে জয়েন কন্ডিশনের পার্থক্য নির্ধারণ করতে পারবো। আবার এদের কাজের প্রক্রিয়াতেও সামান্য পার্থক্য রয়েছে। আমরা সেটা লেফট জয়েনের দৃষ্টান্তে দেখতে পাবো।

ইউজিং ক্লজ

ইউজিং ক্লজ ইনার ক্লজের মতোই তবে অপেক্ষাকৃত ছোট। একটা কলামের যদি দুই টেবিলেই একই নাম থাকে তাহলে সেটা এখানে উল্লেখ করা যাবে।

বরং এটা অনেকটা ন্যাচারাল জয়েনের মতোই। কাজেই ফলাফলে জয়েন কলামটি (customer_id) দুইবার দেখাবে না।

লেফট (আউটার) জয়েন

লেফট জয়েন এক ধরণের আউটার জয়েন। এই কোয়েরিগুলোতে, যদি দ্বিতীয় টেবিল থেকে কোন ম্যাচ না পাওয়া যায় তাহলে প্রথম টেবিল থেকে পাওয়া রেকর্ড এরপরেও দেখাবে।

যদিও অ্যান্ডির কোন অর্ডার নেই, তারপরেও তার রেকর্ড এখানে দেখাবে। দ্বিতীয় টেবিলের কলামগুলোর ভ্যালুতে খালি দেখাচ্ছে।

যেসব রেকর্ডের কোন পারস্পরিক ক্রিয়া নেই সেগুলো খুঁজে বের করতেও এটা কাজে লাগে। যেমন, যেসব কাস্টমার এখনও কোন অর্ডার দেইনি তাদেরও খুঁজে বের করা যাবে।

যা করতে হবে তা হল, order_id-তে খালি ভ্যালুগুলো খুঁজে বের করতে হবে।

সাথে এটাও মনে রাখবেন যে আউটার কীওয়ার্ডটি অপশনাল। আপনি চাইলে লেফট আউটার জয়েনের বদলে শুধুমাত্র লেফট জয়েন ব্যবহার করতে পারবেন।

কন্ডিশনালস/ শর্তাবলী 

এখন চলুন কন্ডিশন রয়েছে এমন একটি কোয়েরি দেখে নেই।

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

এভাবে আমরা অ্যান্ডিকে পাবো, কিন্তু স্যান্ডিকে না। তারপরও সবকিছু কেমন খাপছাড়া লাগছে। আমরা যা খুঁজছি তা পেতে হলে অন ক্লজ ব্যবহার করতে হবে।

এবার আমরা সবাইকেই খুঁজে পাবো। সাথে ১৫ ডলারের উপরে সমস্ত অর্ডারও খুঁজে পাবো। আমি আগেই বলেছি, অন ক্লজের কাজ হ্যোয়ার ক্লজের কাজের থেকে একটু আলাদা। এটির মতো একটি আউটার জয়েনে অন ক্লজ কন্ডিশনের সাথে ম্যাচ না করলেও সারিগুলোকে যুক্ত করা হয়।

রাইট (আউটার) জয়েন

একটি রাইট আউটার জয়েন ঠিক একইভাবে কাজ করে, শুধুমাত্র টেবিলগুলোর অর্ডার উল্টানো থাকে।

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

এখন যেহেতু কাস্টমার টেবিলটি জয়েনের ডান দিকে রয়েছে কাজেই খালি ভ্যালুগুলো পাওয়া গেছে।

উপসংহার

আর্টিকেলটি পড়ার জন্য আপনাকে অনেক ধন্যবাদ। আশা করি ভালো লেগেছে! অনুগ্রহ করে আপনার মন্তব্য বা কোন প্রশ্ন থাকলে জানাবেন। সুন্দর হোক আপনার দিনটি!

এনভাটো মার্কেটে এসকিউএল স্ক্রিপ্ট, অ্যাপ্লিকেশন, অ্যাড-অন ইত্যাদি পরখ করে দেখতে ভুলবেন না। এসকিউএল ডাটাবেজ দিয়ে কী কী করা সম্ভব সে সম্পর্কে হয়তো কিছুটা জানতে পারবেন। আর আপনার বর্তমান প্রজেক্টের জন্য একদম উপযুক্ত সমাধানটিও হয়তো এখানেই খুঁজে পাবেন।

টুইটারে আমাদের ফলো করতে পারেন, অথবা ওয়েবের সেরা ওয়েব ডেভেলপমেন্ট সংক্রান্ত টিউটোরিয়াল পেতে সাবস্ক্রাইব করুন নেটটুটস+ আরএসএস ফিডে

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.