بخش اول : Jaywalking
در این بخش در حال توسعه ویژگی نرم افزاری هستیم که در آن هرکاربر به عنوان یک کاربر اصلی برای یک محصول تخصیص داده میشود. در طراحی اصلی ما فقط اجازه می‌دهیم یک کاربر متعلق به هر محصول باشد، اما امکان چنین تقاضایی وجود دارد که چند کاربر نیز به یک محصول اختصاص داده شوند.
در این صورت، اگر پایگاه داده را به نحوی تغییر دهیم که شناسه‌ی حساب کاربران را در لیستی که با کاما از یکدیگر جدا شده‌اند ذخیره نماییم، خیلی ساده‌تر به نظر می‌رسد نسبت به اینکه بصورت جداگانه آنها را ثبت نماییم.
برنامه نویسان معمولا برای جلوگیری از ایجاد جدول واسطی [1] که رابطه‌های چند به چند زیادی دارد از یک لیست که با کاما داده‌هایش از هم جدا شده‌اند، استفاده می‌کنند. بدین جهت اسم این بخش jaywalking ,antipatten می‌باشد، زیرا jaywalking نیز عملیاتی است که از تقاطع جلوگیری می‌کند.
1.1 هدف: ذخیره کردن چندین صفت
طراحی کردن جدولی که ستون آن فقط یک مقدار دارد، بسیار ساده و آسان می‌باشد. شما می‌توانید نوع داده‌ایی که متعلق به ستون می‌باشد را انتخاب نمایید. مثلا از نوع (int,date…)
چگونه میتوانیم مجموعه‌ایی از مقادیری که به یکدیگر مرتبط هستند را در یک ستون ذخیره نماییم ؟
در مثال ردیابی خطای پایگاه داده، ما یک محصول را به یک کاربر، با استفاده از ستونی که نوع آن integer است، مرتبط می‌نماییم. هر کاربر ممکن است محصولاتی داشته باشد و هر محصول به یک contact اشاره کند. بنابراین یک ارتباط چند به یک بین محصولات و کاربر برقرار است. برعکس این موضوع نیز صادق است؛ یعنی امکان دارد هر محصول متعلق به چندین کاربر باشد و یک ارتباط یک به چند ایجاد شود. در زیر جدول محصولات را به صورت عادی آورده شده است:
CREATE TABLE Products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(1000), account_id BIGINT UNSIGNED, -- . . . FOREIGN KEY (account_id) REFERENCES Accounts(account_id) ); INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder' , 12);
CREATE TABLE Products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(1000), account_id VARCHAR(100), -- comma-separated list -- . . . ); INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder' , '12,34' );
اکنون مشکلات کارایی و جامعیت داده‌ها را در این راه حل پیشنهادی بررسی می‌نماییم .
بدست آوردن محصولاتی برای یک کاربر خاص
بدلیل اینکه تمامی شناسه‌ی کاربران که بصورت کلید خارجی جدول Products می‌باشند به صورت رشته در یک فیلد ذخیره شده‌اند و حالت ایندکس بودن آنها از دست رفته است، بدست آوردن محصولاتی برای یک کاربر خاص سخت می‌باشد. به عنوان مثال بدست آوردن محصولاتی که کاربری با شناسه‌ی 12 خریداری نموده به‌صورت زیر می‌باشد:
SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]' ;
SELECT * FROM Products AS p JOIN Accounts AS a ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]' WHERE p.product_id = 123;
SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',' , '' )) + 1 AS contacts_per_product FROM Products;
UPDATE Products SET account_id = account_id || ',' || 56 WHERE product_id = 123;
<?php
$stmt = $pdo->query(
"SELECT account_id FROM Products WHERE product_id = 123");
$row = $stmt->fetch();
$contact_list = $row['account_id' ];
// change list in PHP code
$value_to_remove = "34";
$contact_list = split(",", $contact_list);
$key_to_remove = array_search($value_to_remove, $contact_list);
unset($contact_list[$key_to_remove]);
$contact_list = join(",", $contact_list);
$stmt = $pdo->prepare(
"UPDATE Products SET account_id = ?
WHERE product_id = 123");
$stmt->execute(array($contact_list)); INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder' , '12,34,banana' );
محدودیت طول لیست
UPDATE Products SET account_id = '10,14,18,22,26,30,34,38,42,46' WHERE product_id = 123;
UPDATE Products SET account_id = '101418,222630,343842,467790' WHERE product_id = 123;
CREATE TABLE Contacts ( product_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (product_id, account_id), FOREIGN KEY (product_id) REFERENCES Products(product_id), FOREIGN KEY (account_id) REFERENCES Accounts(account_id) );
جدول
Contacts یک جدول رابطه ایی بین جداول Products,Accounts
بدست آوردن محصولات برای کاربران و موارد مربوط به آن
ما براحتی میتوانیم تمامی محصولاتی که مختص به یک کاربر هستند را بدست آوریم. در این شیوه خاصیت ایندکس بودن شناسه‌ی کاربران حفظ میشود به همین دلیل queryهای آن برای خواندن و بهینه کردن راحت‌تر می‌باشند. در این روش به کاراکتری برای جدا کردن ورودی‌ها از یکدیگر نیاز نداریم چون هر کدام از آنها در یک سطر جداگانه ثبت می‌شوند. برای ویرایش کردن کاربرانی که یک محصول را خریداری نموده اند، کافیست یک سطر از جدول واسط را اضافه یا حذف نماییم. درنمونه کد زیر، ابتدا در جدول Contacts کاربری با شناسه‌ی 34 که محصولی با شناسه‌ی 456 را خریداری کرده، درج شده است و در خط بعد عملیات حذف با شرط آنکه شناسه‌ی کاربر و محصول به ترتیب 34،456 باشد روی جدول Contacts اعمال شده است.
INSERT INTO Contacts (product_id, account_id) VALUES (456, 34); DELETE FROM Contacts WHERE product_id = 456 AND account_id = 34;
ایجاد توابع تجمیعی
به عنوان نمونه در مثال زیر براحتی ما میتوانیم تعداد محصولات در هر حساب کاربری را بدست آوریم:
SELECT account_id, COUNT(*) AS products_per_account FROM Contacts GROUP BY account_id;
اعتبارسنجی شناسه محصولات
از آنجاییکه مقادیری که در جدول قرار دارند کلید خارجی می‌باشند میتوان صحت اعتبار آنها را بررسی نمود. بعنوان مثال Contacts.account_id به Account.account_id اشاره می‌کند. در ضمن برای هر فیلد نوع آن را میتوان مشخص کرد تا فقط همان نوع داده را بپذیرد.
محدودیت طول لیست
نسبت به روش قبلی تقریبا در این حالت محدودیتی برای تعداد کاراکتر‌های ورودی نداریم.
مزیت‌های دیگر استفاده از جدول واسط
کارایی روش دوم بهتر از حالت قبلی می‌باشد چون ایندکس بودن شناسه‌ها حفظ شده است. همچنین براحتی میتوانیم فیلدی را به این جدول اضافه نماییم مثلا (time, date… )
