طراحی دیتابیس رابطه‌ای؛ نرمال‌سازی، کلیدها و مدل‌سازی عملی

دیتابیس از کدی که از آن استفاده می‌کند عمر طولانی‌تری دارد. فریم‌ورک‌ها عوض می‌شوند و فرانت‌اندها بازنویسی می‌شوند، اما ساختار دیتابیس — و داده‌های داخلش — سال‌ها باقی می‌ماند. طراحی خوب جلوی داده‌ی تکراری و ناسازگار را می‌گیرد، کوئری‌ها را ساده و دیتابیس را سریع نگه می‌دارد. این راهنما اصول را با یک مثال عملی، یعنی یک فروشگاه اینترنتی، مرور می‌کند.

قدم ۱: شناسایی موجودیت‌ها و روابط

از زبان بیزینس شروع کنید. در یک فروشگاه می‌شنویم: مشتری‌ها سفارش ثبت می‌کنند؛ هر سفارش شامل محصولاتی است؛ محصولات به دسته‌بندی‌ها تعلق دارند.

• موجودیت‌ها: Customer، Order، Product، Category، OrderItem
• روابط:
  • Customer ۱ — N Order
  • Order ۱ — N OrderItem
  • Product ۱ — N OrderItem
  • Category ۱ — N Product (یا N — N اگر هر محصول چند دسته داشته باشد)

کشیدن یک نمودار ER ساده قبل از نوشتن هر خط SQL، بیشتر مشکلات طراحی را زود پیدا می‌کند.

قدم ۲: انتخاب کلیدها

کلید اصلی (Primary Key)

هر جدول به کلید اصلی‌ای نیاز دارد که هر ردیف را یکتا شناسایی کند.

• کلید جانشین (Surrogate) (INT IDENTITY، BIGINT، GUID ترتیبی) — عددی بی‌معنا که برای همیشه ثابت می‌ماند. انتخاب پیش‌فرض.
• کلید طبیعی (Natural) (کد ملی، ایمیل، شابک) — معنادار است اما در دنیای واقعی ممکن است تغییر کند یا تکراری شود. آن را با یک Unique Constraint تضمین کنید، اما کلید اصلی‌اش نکنید.

CREATE TABLE Customers (
    Id         INT IDENTITY CONSTRAINT PK_Customers PRIMARY KEY,
    Email      NVARCHAR(256) NOT NULL CONSTRAINT UQ_Customers_Email UNIQUE,
    FullName   NVARCHAR(150) NOT NULL,
    CreatedAt  DATETIME2     NOT NULL CONSTRAINT DF_Customers_CreatedAt DEFAULT SYSUTCDATETIME()
);

کلید خارجی (Foreign Key)

کلید خارجی تضمین می‌کند روابط به ردیف‌های موجود اشاره کنند:

CREATE TABLE Orders (
    Id          INT IDENTITY PRIMARY KEY,
    CustomerId  INT NOT NULL CONSTRAINT FK_Orders_Customers REFERENCES Customers(Id),
    Status      TINYINT NOT NULL,
    CreatedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

ستون‌های کلید خارجی را همیشه ایندکس کنید؛ Joinها و حذف‌ها به آن‌ها وابسته‌اند.

قدم ۳: نرمال‌سازی

نرمال‌سازی داده را طوری سازمان می‌دهد که تکرار و ناهنجاری‌های به‌روزرسانی حذف شوند. این طراحی «همه‌چیز در یک جدول» را ببینید:

مشکلات:

• ناهنجاری به‌روزرسانی — سارا شماره‌اش را عوض می‌کند؛ باید همه‌ی سفارش‌ها را به‌روز کنید و جا انداختن یکی، ناسازگاری می‌سازد.
• ناهنجاری درج — تا مشتری سفارش ندهد نمی‌توانید او را ذخیره کنید.
• ناهنجاری حذف — حذف تنها سفارش او، اطلاعات مشتری را هم حذف می‌کند.
• مقادیر غیراتمی — «ماوس، کیبورد» قابل کوئری، شمارش یا Join نیست.

فرم نرمال اول (1NF)

هر ستون مقادیر اتمی نگه می‌دارد؛ بدون گروه‌های تکراری یا لیست‌های جداشده با کاما.

لیست محصولات را به ردیف‌های جدا تقسیم کنید؛ یک جدول OrderItems با یک ردیف برای هر محصول.

فرم نرمال دوم (2NF)

1NF، و هر ستون غیرکلیدی به کل کلید اصلی وابسته باشد (برای کلیدهای ترکیبی مهم است).

اگر OrderItems کلید ترکیبی (OrderId, ProductId) داشته باشد و ProductName را هم نگه دارد، آن نام فقط به ProductId وابسته است، نه به کل کلید. آن را به جدول Products منتقل کنید.

فرم نرمال سوم (3NF)

2NF، و ستون‌های غیرکلیدی فقط به کلید وابسته باشند، نه به ستون‌های غیرکلیدی دیگر.

اگر Orders هم CustomerId و هم CustomerPhone را نگه دارد، شماره تلفن به مشتری وابسته است، نه به سفارش. آن را در Customers نگه دارید.

نتیجه‌ی نرمال‌شده:

Customers(Id, FullName, Phone, Email)
Products(Id, Name, Price, CategoryId)
Categories(Id, Name)
Orders(Id, CustomerId, Status, CreatedAt)
OrderItems(OrderId, ProductId, Quantity, UnitPrice)

یک خلاصه‌ی به‌یادماندنی از 3NF: هر ویژگی غیرکلیدی به کلید وابسته است، به کل کلید، و به هیچ چیز جز کلید.

یک استثنای مهم: داده‌های تاریخی

به OrderItems.UnitPrice دقت کنید. آیا این تکرار Products.Price نیست؟ نه؛ قیمت را در لحظه‌ی خرید ثبت می‌کند. اگر فردا قیمت محصول تغییر کند، فاکتورهای گذشته نباید تغییر کنند. ثبت تصویر لحظه‌ای مقادیری که مربوط به یک زمان مشخص‌اند (قیمت، آدرس روی فاکتور، نرخ مالیات) طراحی درست است، نه دی‌نرمال‌سازی.

دی‌نرمال‌سازی؛ شکستن آگاهانه‌ی قوانین

گاهی خواندن آن‌قدر پرتکرار است که هر بار Join یا تجمیع کردن خیلی گران تمام می‌شود. دی‌نرمال‌سازی کنترل‌شده می‌تواند کمک کند:

• نگه داشتن CommentCount در Posts به‌جای شمردن نظرات در هر بازدید صفحه.
• یک جدول گزارش که هر شب بازسازی می‌شود.
• کش کردن مقادیر محاسبه‌شده (متن جستجو، مجموع‌ها).

قوانین: بعد از اندازه‌گیری یک مشکل واقعی دی‌نرمال کنید، منبع اصلی حقیقت را نرمال نگه دارید و نسخه‌ی تکراری را مطمئن به‌روز کنید (همان تراکنش، Trigger یا کار پس‌زمینه).

انتخاب نوع داده

زمان‌ها را به UTC ذخیره و در برنامه به منطقه‌ی زمانی کاربر (و تقویم شمسی) تبدیل کنید.

Constraintها مستنداتی هستند که خودشان را اجرا می‌کنند

ALTER TABLE OrderItems ADD CONSTRAINT CK_OrderItems_Quantity CHECK (Quantity > 0);
ALTER TABLE Products   ADD CONSTRAINT CK_Products_Price     CHECK (Price >= 0);

NOT NULL، UNIQUE، CHECK و کلیدهای خارجی باگ‌هایی را می‌گیرند که اعتبارسنجی برنامه از دست می‌دهد؛ مخصوصاً در شرایط هم‌زمانی یا وقتی چند برنامه از یک دیتابیس استفاده می‌کنند.

قراردادهای نام‌گذاری

• نام جدول جمع یا مفرد — یکی را انتخاب و یکدست استفاده کنید (Orders یا Order).
• Id برای کلید اصلی و {Entity}Id برای کلید خارجی (CustomerId).
• Constraintها و ایندکس‌ها را صریح نام‌گذاری کنید: PK_Orders، FK_Orders_Customers، IX_Orders_CustomerId.
• از کلمات رزروشده (مثل User و Order در برخی دیتابیس‌ها) پرهیز کنید یا همیشه آن‌ها را Quote کنید.

حذف نرم و ممیزی

داده‌های بیزینسی به‌ندرت واقعاً حذف می‌شوند. الگوهای رایج:

ALTER TABLE Customers ADD IsDeleted BIT NOT NULL DEFAULT 0, DeletedAt DATETIME2 NULL;

حذف نرم را با ایندکس یکتای فیلترشده (WHERE IsDeleted = 0) ترکیب کنید تا ایمیل کاربر حذف‌شده قابل استفاده‌ی دوباره باشد. به جدول‌های مهم ستون‌های CreatedAt، UpdatedAt (و CreatedBy) اضافه کنید یا برای تاریخچه‌ی کامل از Temporal Tableهای SQL Server استفاده کنید.

جمع‌بندی

از زبان بیزینس شروع کنید، موجودیت‌ها و روابط را شناسایی کنید، از کلید اصلی جانشین همراه با Unique Constraint برای کلیدهای طبیعی استفاده کنید، روابط را با کلیدهای خارجی ایندکس‌شده تضمین کنید، تا 3NF نرمال‌سازی کنید تا تکرار حذف شود، مقادیر لحظه‌ای را ثبت کنید، فقط بعد از اندازه‌گیری دی‌نرمال کنید، نوع داده‌ی دقیق انتخاب کنید و بگذارید Constraintها از داده محافظت کنند. یک ساختار خوب‌طراحی‌شده هر کوئری، گزارش و قابلیتی را که روی آن ساخته می‌شود ساده‌تر می‌کند.

پرسش‌های متداول

آیا همیشه هدف 3NF است؟

برای سیستم‌های تراکنشی (OLTP)، 3NF پیش‌فرض درست است. دیتابیس‌های گزارش‌گیری و تحلیلی اغلب عمداً از Star Schemaهای دی‌نرمال استفاده می‌کنند.

برای کلید اصلی GUID بهتر است یا INT؟

INT/BIGINT Identity فشرده و سریع است. GUID وقتی مفید است که شناسه‌ها در جاهای مختلف تولید شوند یا نباید قابل حدس باشند؛ GUID ترتیبی را ترجیح دهید یا GUID را به‌عنوان شناسه‌ی عمومی کنار یک کلید عددی داخلی نگه دارید.

آیا JSON را در یک ستون ذخیره کنم؟

برای داده‌ی واقعاً منعطف و کم‌کوئری (تنظیمات، Payloadها) ستون JSON مشکلی ندارد. اگر روی آن داده فیلتر، Join یا تجمیع انجام می‌دهید، آن را رابطه‌ای مدل کنید.