database design অডিট লগিং জন্য ডাটাবেস নকশা




ডাটাবেস কি (4)

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

উদাহরণস্বরূপ, উদাহরণস্বরূপ, যদি আপনার বিক্রয় বিক্রয়গুলি ট্র্যাক করতে সুযোগগুলি বলা একটি টেবিল থাকে তবে আপনি আসলে দুটি পৃথক টেবিল তৈরি করবেন:

সুযোগ
সুযোগসুবিধাহীনতা (অথবা এরকম কিছু)

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

এখানে একটি সহজ উদাহরণ:

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

এবং বিষয়বস্তু:

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

আমি সম্ভবত বিষয়বস্তু টেবিলের PK পৃষ্ঠাআইডি থেকে মাল্টি-কলাম কী এবং পুনর্বিবেচনা প্রদান করা পুনর্বিবেচনা একটি পরিচয় প্রকার ছিল। আপনি সংশোধন কলামটি FK হিসাবে ব্যবহার করবেন। আপনি তারপর এইভাবে যোগদান দ্বারা সংহত রেকর্ড টান:

SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

সেখানে কিছু ত্রুটি থাকতে পারে ... এটি আমার মাথার উপরের অংশ। এটি আপনাকে একটি বিকল্প প্যাটার্ন একটি ধারণা দিতে হবে, যদিও।

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

কিছু প্রশ্ন ইতিমধ্যে এই সম্পর্কে এখানে জিজ্ঞাসা করা হয়েছে, কিন্তু আমি সব পরিস্থিতিতে জন্য একক সর্বোত্তম পদ্ধতি আছে যে একমত না:

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

আমার প্রশ্ন হল: কোনও রেফারেন্স যা আমি ব্যবহার করতে পারি, সম্ভবত একটি বই বা সিদ্ধান্তের গাছের মতো কিছু যা আমি কোনও ইনপুট ভেরিয়েবলগুলির উপর ভিত্তি করে কোন পথে যেতে পারি তা নির্ধারণ করতে উল্লেখ করতে পারি:

  • ডাটাবেস স্কিম মেয়াদপূর্তি
  • লগ কিভাবে জিজ্ঞাসা করা হবে
  • রেকর্ড পুনঃনির্মাণ করতে হবে যে সম্ভাবনা
  • কি আরো গুরুত্বপূর্ণ: রচনা বা কর্মক্ষমতা পড়া
  • লগ করা হয় যে মান প্রকৃতি (স্ট্রিং, সংখ্যা, blobs)
  • সংগ্রহস্থল স্থান উপলব্ধ

আমি জানি যে পন্থা হয়:

1. তৈরি এবং সংশোধিত তারিখ এবং ব্যবহারকারী জন্য কলাম যোগ করুন

টেবিল উদাহরণ:

  • আইডি
  • মান_1
  • মান_2
  • value_3
  • তৈরীর তারিখ
  • পরিবর্তিত তারিখ
  • দ্বারা সৃষ্টি
  • modified_by

মেজর কনস: আমরা পরিবর্তন ইতিহাস হারান। প্রতিশ্রুতি পরে রোলব্যাক করতে পারবেন না।

2. শুধুমাত্র টেবিল সন্নিবেশ করান

টেবিল উদাহরণ :

  • আইডি
  • মান_1
  • মান_2
  • value_3
  • থেকে
  • থেকে
  • মুছে ফেলা হয়েছে (বুলিয়ান)
  • ব্যবহারকারী

মেজর কনস: কিভাবে বিদেশী কী আপ টু ডেট রাখা? বিশাল স্থান প্রয়োজন

3. প্রতিটি টেবিলের জন্য একটি পৃথক ইতিহাস টেবিল তৈরি করুন

ইতিহাস টেবিল উদাহরণ:

  • আইডি
  • মান_1
  • মান_2
  • value_3
  • value_4
  • ব্যবহারকারী
  • মুছে ফেলা হয়েছে (বুলিয়ান)
  • টাইমস্ট্যাম্প

মেজর কনস: সমস্ত অডিট টেবিল নকল করতে হবে। স্কিমা পরিবর্তন হলে এটি সমস্ত লগ স্থানান্তর করতে হবে।

4. সমস্ত টেবিল জন্য একটি সংহত ইতিহাস টেবিল তৈরি করুন

ইতিহাস টেবিল উদাহরণ:

  • TABLE_NAME
  • ক্ষেত্র
  • ব্যবহারকারী
  • NEW_VALUE
  • মুছে ফেলা হয়েছে (বুলিয়ান)
  • টাইমস্ট্যাম্প

মেজর কনস: সহজে প্রয়োজন হলে রেকর্ড (রোলব্যাক) পুনরায় তৈরি করতে পারব? New_value কলামটি একটি বড় স্ট্রিং হতে হবে যাতে এটি বিভিন্ন কলামের ধরনগুলিকে সমর্থন করে।


Answer #1

আপনি SQL সার্ভার 2008 ব্যবহার করছেন, আপনি সম্ভবত পরিবর্তন তথ্য ক্যাপচার বিবেচনা করা উচিত। এটি 2008 এর জন্য নতুন এবং আপনাকে একটি কার্যকরী পরিমাণ সংরক্ষণ করতে পারে।


Answer #2

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

blog : একটি অনন্য পোস্ট আইডি, শিরোনাম, সামগ্রী এবং একটি মুছে ফেলা পতাকা সংরক্ষণ করে। audit : একটি রেকর্ড আইডি, ব্লগ পোস্ট আইডি, পরিবর্তনের ধরন (নতুন, সম্পাদনা বা মুছে ফেলা) এবং সেই পরিবর্তনের তারিখ / সময় সহ ঐতিহাসিক পরিবর্তনগুলির একটি প্রাথমিক সেট সঞ্চয় করে। নিচের এসকিউএল blog তৈরি করে এবং মুছে ফেলা কলাম সূচী করে:

CREATE TABLE `blog` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `title` text,
    `content` text,
    `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `ix_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts';

নিচের এসকিউএল audit টেবিল তৈরি করে। সমস্ত কলাম সূচী করা হয়েছে এবং একটি বিদেশী কী audit.blog_id এর জন্য সংজ্ঞায়িত করা হয়েছে যা ব্লগ.আইডি.কে উল্লেখ করে। অতএব, যখন আমরা শারীরিকভাবে ব্লগ এন্ট্রি মুছে ফেলি, তখন এটি সম্পূর্ণ অডিট ইতিহাসও মুছে ফেলা হয়।

CREATE TABLE `audit` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `blog_id` mediumint(8) unsigned NOT NULL,
    `changetype` enum('NEW','EDIT','DELETE') NOT NULL,
    `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ix_blog_id` (`blog_id`),
    KEY `ix_changetype` (`changetype`),
    KEY `ix_changetime` (`changetime`),
    CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Answer #3

আমি কোন রেফারেন্স জানি না, কিন্তু আমি নিশ্চিত যে কেউ কিছু লিখেছেন।

যাইহোক, যদি উদ্দেশ্যটি কেবলমাত্র কী ঘটেছে তার একটি রেকর্ড থাকা-অডিট লগের সবচেয়ে সাধারণ ব্যবহার-তাহলে কেন কেবল সবকিছুই রাখবেন না:

timestamp
username
ip_address
procedureName (if called from a stored procedure)
database
table
field
accesstype (insert, delete, modify)
oldvalue
newvalue

সম্ভবত এটি একটি ট্রিগার দ্বারা রক্ষণাবেক্ষণ করা হয়।





audit