database - تعريف - مفاتيح بديلة مقابل مفاتيح طبيعية/تجارية




تعريف foreign key (13)

هنا نذهب مرة أخرى ، لا تزال الحجة القديمة تنشأ ...

هل من الأفضل أن يكون لدينا مفتاح عمل كمفتاح أساسي ، أم أننا سنحصل على معرّف بديل (أي هوية SQL Server) مع قيد فريد في حقل مفتاح العمل؟

من فضلك ، قدم أمثلة أو دليل لدعم نظريتك.


أريد أن أشارك تجربتي معك في هذه الحرب التي لا نهاية لها: D على معضلة أساسية بديلة مقابل بديلة. أعتقد أن كلا المفاتيح البديلة (تلك المولدة آليًا) والمفاتيح الطبيعية (المكونة من عمود (أعمدة) ذات معنى المجال) لها مزايا وعيوب . بناءً على موقفك ، قد يكون الأمر أكثر ملاءمة لاختيار طريقة واحدة أو أخرى.

كما يبدو أن العديد من الناس يقدمون مفاتيح بديلة كحل كامل ومفاتيح طبيعية تمامًا مثل الطاعون ، سأركز على حجج وجهة النظر الأخرى:

مساوئ مفاتيح بديلة

مفاتيح بديلة هي:

  1. مصدر مشاكل الأداء:
    • يتم تنفيذها عادةً باستخدام أعمدة متدرجة تلقائيًا مما يعني:
      • رحلة ذهابًا وإيابًا إلى قاعدة البيانات في كل مرة تريد فيها الحصول على معرف جديد (أعلم أنه يمكن تحسين ذلك باستخدام التخزين المؤقت أو خوارزميات hilo المتشابهة تمامًا ولكن لا تزال تلك الطرق لها عيوبها الخاصة).
      • إذا كنت في يوم من الأيام تحتاج إلى نقل بياناتك من مخطط إلى آخر (يحدث ذلك بشكل منتظم في شركتي على الأقل) ، فقد تواجه مشكلات في تصادم معرف. ونعم ، أعلم أنه يمكنك استخدام UUIDs ولكن تلك الأعمدة تتطلب 32 رقمًا سداسيًا عشريًا! (إذا كنت تهتم بحجم قاعدة البيانات ، فقد تكون مشكلة).
      • إذا كنت تستخدم تسلسلاً واحدًا لكل مفاتيحك البديلة ، فمن المؤكد أنك ستنتهي إلى نزاع في قاعدة بياناتك.
  2. معرض للخطأ. يحتوي التسلسل على حد أقصى لـ max_value ، لذا - كمطور - عليك أن تنتبه إلى النقاط التالية:
    • يجب أن تقوم بتدوير التسلسل الخاص بك (عندما يتم الوصول إلى القيمة القصوى فإنه يعود إلى 1،2 ، ...).
    • إذا كنت تستخدم التسلسل كطلب (مع مرور الوقت) لبياناتك ، فيجب عليك التعامل مع حالة ركوب الدراجات (قد يكون العمود الذي يحمل الرقم التعريفي 1 أحدث من الصف الذي يحتوي على القيمة القصوى للمعرف - 1).
    • تأكد من أن التعليمة البرمجية الخاصة بك (وحتى واجهات العميل الخاصة بك والتي لا ينبغي أن تحدث كما يفترض أن تكون معرف داخلي) تدعم الأعداد الصحيحة 32b / 64b التي استخدمتها لتخزين قيم التسلسل الخاصة بك.
  3. وهي لا تضمن بيانات غير متكررة. يمكنك دائمًا الحصول على صفين باستخدام جميع قيم الأعمدة نفسها ولكن مع قيمة مولدة مختلفة. بالنسبة لي هذه هي مشكلة مفاتيح بديلة من وجهة نظر تصميم قاعدة البيانات.
  4. المزيد في ويكيبيديا ...

أساطير على المفاتيح الطبيعية

  1. المفاتيح المركبة أقل فاعلية من المفاتيح البديلة. لا! يعتمد على محرك قاعدة البيانات المستخدمة:
  2. لا توجد مفاتيح طبيعية في الحياة الحقيقية. عذرا لكنها موجودة! في صناعة الطيران ، على سبيل المثال ، ستكون الصفات التالية فريدة دائمًا فيما يتعلق برحلة طيران محددة (مثل شركة الطيران ، والمغادرة ، ورقم الرحلة ، والعمليات التشغيلية). بشكل عام ، عندما تكون مجموعة من بيانات العمل مضمونة لتكون فريدة وفقًا لمعيار معين ، فإن هذه المجموعة من البيانات هي مرشح أساسي طبيعي [جيد].
  3. مفاتيح طبيعية "تلوث مخطط" جداول الطفل. بالنسبة لي ، هذا شعور أكثر من كونه مشكلة حقيقية. قد يكون كل من مفتاح أساسي مكون من 4 أعمدة وب 2 بايت أكثر كفاءة من عمود واحد من 11 بايت. إلى جانب ذلك ، يمكن استخدام الأعمدة الأربعة للاستعلام عن الجدول الفرعي مباشرة (باستخدام الأعمدة الأربعة في جملة حيث) دون الانضمام إلى الجدول الأصل.

استنتاج

استخدم المفاتيح الطبيعية عندما تكون مناسبة للقيام بذلك واستخدم مفاتيح بديلة عندما يكون من الأفضل استخدامها.

نأمل أن هذا ساعد شخص ما!


أنا أكره مفاتيح بديلة بشكل عام. يجب استخدامها فقط عندما لا يتوفر مفتاح طبيعي جيد. من السخف إلى حد ما عندما تفكر في ذلك ، أن تعتقد أن إضافة بيانات لا معنى لها إلى طاولتك يمكن أن يجعل الأمور أفضل.

وهنا اسباب بلدي:

  1. عند استخدام المفاتيح الطبيعية ، يتم تجميع الجداول بالطريقة التي يتم بها البحث عنها في الغالب ، مما يجعل الاستعلامات أسرع.

  2. عند استخدام مفاتيح بديلة يجب عليك إضافة فهارس فريدة على أعمدة المفاتيح المنطقية. لا تزال بحاجة إلى منع البيانات المكررة المنطقية. على سبيل المثال ، لا يمكنك السماح لمؤسستين لهما نفس الاسم في جدول المؤسسة على الرغم من أن pk هو عمود معرّف بديل.

  3. عند استخدام مفاتيح بديلة كمفتاح أساسي ، يكون من غير الواضح ما هي المفاتيح الأساسية الطبيعية. عند التطوير ، تريد معرفة مجموعة الأعمدة التي تجعل الجدول فريدًا.

  4. في واحد إلى العديد من سلاسل العلاقة ، وسلاسل المفاتيح المنطقية. على سبيل المثال ، لدى المؤسسات العديد من الحسابات والحسابات لديها العديد من الفواتير. لذلك المفتاح المنطقي للمنظمة هو OrgName. المفتاح المنطقي للحسابات هو OrgName ، AccountID. المفتاح المنطقي للفاتورة هو OrgName ، AccountID ، InvoiceNumber.

    عند استخدام مفاتيح بديلة ، يتم اقتطاع سلاسل المفاتيح فقط من خلال وجود مفتاح خارجي للوالد المباشر. على سبيل المثال ، لا يحتوي الجدول الفاتورة على عمود OrgName. يحتوي فقط على عمود لـ AccountID. إذا كنت تريد البحث عن فواتير لمؤسسة معينة ، فستحتاج إلى الانضمام إلى جداول المنظمة والحساب والفاتورة. إذا كنت تستخدم مفاتيح منطقية ، فيمكنك حينئذٍ الاستعلام عن جدول المؤسسة مباشرةً.

  5. يؤدي تخزين قيم مفاتيح بديلة لجداول البحث إلى ملء الجداول بأعداد صحيحة لا معنى لها. لعرض البيانات ، يجب إنشاء طرق عرض معقدة تنضم إلى جميع جداول البحث. يهدف جدول البحث إلى الاحتفاظ بمجموعة من القيم المقبولة للعمود. لا ينبغي أن يتم تقنينه عن طريق تخزين مفتاح بديل صحيح بدلاً من ذلك. لا يوجد شيء في قواعد التسوية التي تقترح عليك تخزين عدد صحيح بديل بدلاً من القيمة نفسها.

  6. لدي ثلاثة كتب قاعدة بيانات مختلفة. لا أحد منهم يظهر باستخدام مفاتيح بديلة.


استخدم دائمًا مفتاحًا ليس له معنى عمل. إنها مجرد ممارسة جيدة.

تعديل: كنت أحاول العثور على رابط له عبر الإنترنت ، ولكن لم أستطع. ولكن في "أنماط Archtecture للمؤسسة" [Fowler] لديه تفسير جيد لماذا لا يجب عليك استخدام أي شيء آخر غير المفتاح مع عدم وجود معنى آخر غير المفتاح. انها تتلخص في حقيقة أنه ينبغي أن يكون لها وظيفة واحدة ووظيفة واحدة فقط.


حصان للدورات. اذكر انحيازى أنا مطور أولاً ، لذلك فأنا مهتم بشكل أساسي بمنح المستخدمين تطبيقًا عمليًا.

لقد عملت على أنظمة مزودة بمفاتيح طبيعية ، واضطررت إلى قضاء الكثير من الوقت في التأكد من أن تغيرات القيمة ستنتقل.

لقد عملت على أنظمة مزودة بمفاتيح بديلة فقط ، وكان العيب الوحيد هو عدم وجود بيانات غير قابلة للتجزئة للتقسيم.

معظم مطوري PL / SQL التقليديين الذين عملت معهم لم يعجبهم المفاتيح البديلة بسبب عدد الجداول في كل صلة ، ولكن قواعد الاختبار والإنتاج لدينا لم تثير أي عرق مطلقًا. الصلات الإضافية لم تؤثر على أداء التطبيق. باستخدام لهجات قواعد البيانات التي لا تدعم جمل مثل "X inner join Y on Xa = Yb" ، أو المطورين الذين لا يستخدمون هذا النحو ، فإن الإضافات الإضافية للمفاتيح البديلة تجعل الاستعلامات أكثر صعوبة في القراءة ، وأطول للكتابة و تحقق: انظرTony Andrews آخر. ولكن إذا استخدمت ORM أو أي إطار عمل جيل SQL آخر ، فلن تلاحظ ذلك. الكتابة باللمس أيضا التخفيف.


ربما لا علاقة تماما لهذا الموضوع ، ولكن صداع لدي التعامل مع مفاتيح بديلة. تعمل تحليلات Oracle pre-delivered على إنشاء SKs التي تم إنشاؤها تلقائيًا على جميع جداول الأبعاد الخاصة بها في المستودع ، كما تقوم بتخزين تلك الموجودة على الحقائق. لذلك ، في أي وقت تحتاج (أبعاد) إلى إعادة تحميلها عندما تتم إضافة أعمدة جديدة أو تحتاج إلى ملء كل العناصر في البعد ، فإن SKs المعينة أثناء التحديث تجعل SKS غير متزامنة مع القيم الأصلية المخزنة إلى الحقيقة ، مما يؤدي إلى إجبار إعادة تحميل كاملة لجميع جداول الحقائق التي تنضم إليها. أنا أفضل أنه حتى لو كان SK عدد لا معنى له ، سيكون هناك بعض الطريقة التي لا يمكن أن تتغير للسجلات الأصلية / القديمة. وكما يعلم الكثيرون ، نادرًا ما يخدم الصندوق خارج نطاق احتياجات المؤسسة ، ويجب علينا تخصيصه باستمرار. لدينا الآن 3 سنوات من البيانات في مستودعنا ، وعمليات إعادة التحميل الكاملة من أنظمة Oracle Financial كبيرة جدًا. لذلك في حالتي ، لا يتم إنشاءها من إدخال البيانات ، ولكن يتم إضافتها في مستودع للمساعدة في إعداد تقارير الأداء. أحصل عليه ، لكننا نتغير ، وهو كابوس.


على حد سواء. هل لديك كعكة الخاص بك وأكله.

تذكر أنه لا يوجد شيء خاص حول المفتاح الأساسي ، إلا أنه تم تصنيفه على هذا النحو. إنه لا شيء أكثر من قيد غير فارغ NULL ، ويمكن أن يحتوي الجدول على أكثر من واحد.

إذا كنت تستخدم مفتاح بديل ، فستظل بحاجة إلى مفتاح عمل لضمان التفرد وفقًا لقواعد العمل.


فقط بعض الأسباب لاستخدام مفاتيح بديلة:

  1. الاستقرار : تغيير المفتاح بسبب الأعمال أو الحاجة الطبيعية يؤثر سلبًا على الجداول المرتبطة. نادراً ما تحتاج مفاتيح بديلة ، في أي وقت ، إلى تغيير لأنه لا يوجد أي معنى يرتبط بقيمة.

  2. الاتفاقية : يتيح لك الحصول على اصطلاح تسمية عمود أساسي قياسي بدلاً من التفكير في كيفية ضم الجداول بأسماء مختلفة لملفات PK الخاصة بهم.

  3. السرعة : اعتماداً على قيمة ونوع PK ، قد يكون مفتاح بديل لعدد صحيح أصغر وأسرع للفهرسة والبحث.


في حالة قاعدة البيانات في الوقت المناسب فمن الأفضل أن يكون الجمع بين مفاتيح بديلة والطبيعية. على سبيل المثال ، تحتاج إلى تعقب معلومات عضو للنادي. بعض سمات العضو لا تتغير. على سبيل المثال تاريخ الميلاد ولكن الاسم يمكن أن يتغير. لذلك إنشاء جدول عضو بمفتاح بديل member_id ويكون عمود لـ DOB. إنشاء جدول آخر يسمى اسم الشخص ولديك أعمدة member_id ، member_fname ، member_lname ، date_updated. في هذا الجدول ، سيكون المفتاح الطبيعي member_id + date_updated.


مفاتيح بديلة (الأعداد الصحيحة عادة) لها قيمة مضافة لجعل علاقات الجدول أسرع ، وأكثر اقتصادية في التخزين وسرعة التحديث (حتى أفضل ، لا تحتاج المفاتيح الخارجية للتحديث عند استخدام مفاتيح بديلة ، على النقيض من الحقول الرئيسية للأعمال ، التي تتغير الآن وبعد ذلك).

يجب استخدام المفتاح الأساسي للجدول لتعريف الصف بشكل فريد ، خاصة لأغراض الانضمام. فكر في جدول "الأشخاص": يمكن أن تتغير الأسماء ، وهي غير مضمونة فريدة.

أعتقد الشركات: كنت شركة ميركين سعيدة بالقيام بأعمال تجارية مع شركات أخرى في Merkia. أنت ذكي بما يكفي لعدم استخدام اسم الشركة كمفتاح أساسي ، لذلك يمكنك استخدام معرف الشركة الفريد لحكومة Merkia في 10 أرقام أبجدية رقمية بالكامل. ثم تغير Merkia معرفات الشركة لأنها تعتقد أنها ستكون فكرة جيدة. كل شيء على مايرام ، يمكنك استخدام ميزة التحديثات المتتالية الخاصة بمشغل db الخاص بك ، للتغيير الذي لا يجب أن يشركك في المقام الأول. في وقت لاحق ، يتوسع نشاطك التجاري ، والآن تعمل مع شركة في فريدونيا. معرف الشركة فريديوني ما يصل إلى 16 حرفا. تحتاج إلى تكبير المفتاح الأساسي لمعرف الشركة (أيضًا الحقول الأساسية الخارجية في الأوامر ، القضايا ، MoneyTransfers وغيرها) ، إضافة حقل البلد في المفتاح الأساسي (أيضًا في المفاتيح الخارجية). أوتش! الحرب الأهلية في فريدونيا ، تنقسم إلى ثلاثة بلدان. يجب تغيير اسم البلد الخاص بالزميلة إلى اسم جديد ؛ تحديثات متتالية للانقاذ. راجع للشغل ، ما هو مفتاحك الأساسي؟ (البلد ، CompanyID) أو (CompanyID ، البلد)؟ يساعد هذا الأخير على الانضمام ، ويتجنب السابق مؤشرًا آخر (أو ربما كثيرًا ، إذا كنت تريد أن يتم تجميع الطلبات الخاصة بك حسب البلد أيضًا).

كل هذه ليست دليلا ، ولكن إشارة إلى أن مفتاح بديل لتحديد صف فريد لجميع الاستخدامات ، بما في ذلك عمليات الانضمام ، هو أفضل من مفتاح العمل.


مفاتيح بديلة مفيدة جدا إذا كنت تخطط لاستخدام أداة ORM للتعامل مع / توليد فئات البيانات الخاصة بك. على الرغم من أنه يمكنك استخدام مفاتيح مركبة مع بعض مصممي الخرائط الأكثر تقدمًا (مثل: hibernate) ، فإنها تضيف بعض التعقيد إلى شفرتك.

(وبالطبع ، سوف يجادل أخصائيو قواعد البيانات بأن فكرة مفتاح بديل هي حتى مجرد مكروه).

أنا من المعجبين باستخدام uids للمفاتيح بديلة عندما تكون مناسبة. الفوز الرئيسي معهم هو معرفة المفتاح مقدمًا ، على سبيل المثال ، يمكنك إنشاء مثيل لفئة معينة باستخدام المعرّف الذي تم تعيينه بالفعل وضمان كونه فريدًا ، في حين أنه على سبيل المثال ، سيكون لديك مفتاح صحيح يلزمك الافتراضي إلى 0 أو - 1 وتحديث إلى قيمة مناسبة عند حفظ / تحديث.

تحتوي معرفات UID على عقوبات من حيث سرعة البحث والانضمام ، لذلك يعتمد الأمر على التطبيق المعني على ما إذا كانت مرغوبة.


مفتاح بديل لن يكون لديك سبب للتغيير. لا أستطيع أن أقول الشيء نفسه عن المفاتيح الطبيعية. الأسماء الأخيرة ورسائل البريد الإلكتروني ورقم ISBN الخاص بنا - كلهم ​​يمكنهم تغيير يوم واحد.


هذه واحدة من الحالات التي يكون فيها مفتاح بديل منطقيًا دائمًا . هناك حالات يمكنك إما اختيار الأفضل لقاعدة البيانات أو ما هو الأفضل لنموذج الكائن الخاص بك ، ولكن في كلتا الحالتين ، يعد استخدام مفتاح بدون معنى أو GUID فكرة أفضل. يجعل الفهرسة أسهل وأسرع ، وهي هوية للكائن الذي لا يتغير.


الحالة 1: الجدول الخاص بك هو جدول البحث مع أقل من 50 نوعا (إدراج)

استخدم مفاتيح الأعمال / الطبيعية . فمثلا:

Table: JOB with 50 inserts
CODE (primary key)       NAME               DESCRIPTION
PRG                      PROGRAMMER         A programmer is writing code
MNG                      MANAGER            A manager is doing whatever
CLN                      CLEANER            A cleaner cleans
...............
joined with
Table: PEOPLE with 100000 inserts

foreign key JOBCODE in table PEOPLE
looks at
primary key CODE in table JOB

الحالة 2: الجدول الخاص بك هو جدول مع الآلاف من إدراج

استخدم مفاتيح بديلة / autoincrement . فمثلا:

Table: ASSIGNMENT with 1000000 inserts
joined with
Table: PEOPLE with 100000 inserts

foreign key PEOPLEID in table ASSIGNMENT
looks at
primary key ID in table PEOPLE (autoincrement)

في الحالة الأولى:

  • يمكنك تحديد جميع المبرمجين في الجدول PEOPLE دون استخدام الانضمام مع الجدول JOB ، ولكن فقط مع: "SELECT * FROM PEOPLE WHERE JOBCODE = 'PRG'"

في الحالة الثانية:

  • تكون استعلامات قاعدة البيانات أسرع لأن المفتاح الأساسي هو عدد صحيح
  • لا تحتاج إلى إزعاج نفسك بالعثور على المفتاح الفريد التالي لأن قاعدة البيانات نفسها تمنحك ميزة autoincrement التالية.




key