Bagaimana untuk mewakili warisan dalam pangkalan data?

Question

Saya sedang memikirkan cara untuk mewakili struktur kompleks dalam pangkalan data SQL Server.

Pertimbangkan aplikasi yang perlu menyimpan butiran tentang siri objek yang berkongsi beberapa sifat tetapi mempunyai banyak sifat luar biasa lain. Contohnya, pakej insurans komersial mungkin termasuk insurans liabiliti, motor, harta benda dan indemniti dalam rekod polisi yang sama.

Mencapai ini dalam, sebagai contoh, C# adalah mudah, kerana anda boleh membuat dasar yang mengandungi koleksi bahagian, dengan bahagian yang diwarisi seperti yang diperlukan untuk pelbagai jenis penggantian. Walau bagaimanapun, pangkalan data hubungan nampaknya tidak membenarkan ini.

Saya dapat melihat terdapat dua pilihan utama:

1. Buat jadual strategi dan kemudian jadual separa dengan semua medan yang diperlukan untuk semua variasi yang mungkin, kebanyakannya kosong.

2. Buat jadual polisi dan berbilang jadual separa, setiap jadual sepadan dengan jenis insurans.

Kedua-dua alternatif kelihatan tidak memuaskan, terutamanya kerana pertanyaan perlu ditulis di semua bahagian, yang akan melibatkan sama ada banyak cantuman atau banyak semakan nol.

Apakah amalan terbaik untuk senario ini?

Answer 1

Pilihan ketiga ialah membuat jadual "Dasar" dan kemudian jadual "SectionsMain" untuk menyimpan semua medan yang biasa merentas pelbagai jenis bahagian. Kemudian buat jadual tambahan untuk setiap jenis bahagian, yang mengandungi hanya medan yang tidak biasa.

Membuat keputusan yang mana satu yang terbaik bergantung terutamanya pada berapa banyak medan yang anda ada dan cara anda ingin menulis SQL anda. Mereka semua akan bekerja. Jika anda hanya mempunyai beberapa medan, maka saya mungkin akan menggunakan #1. Untuk "banyak" kawasan, saya akan condong ke arah #2 atau #3.

Answer 2

@Bill Karwin Dalam bukunya SQL Antipatterns, dia mencadangkan SQL Nilai Harta Entitianti-corak. Berikut ialah gambaran ringkas:

Warisan jadual tunggal (aka warisan jadual per-hierarki):

Menggunakan satu meja seperti pilihan pertama mungkin merupakan reka bentuk yang paling mudah. Seperti yang anda nyatakan, banyak sifat khusus subjenis mesti diberi nilai NULL pada baris di mana sifat ini tidak digunakan. Menggunakan model ini, anda akan mempunyai jadual strategi yang kelihatan seperti ini:

+------+---------------------+----------+----------------+------------------+
| id   | date_issued         | type     | vehicle_reg_no | property_address |
+------+---------------------+----------+----------------+------------------+
|    1 | 2010-08-20 12:00:00 | MOTOR    | 01-A-04004     | NULL             |
|    2 | 2010-08-20 13:00:00 | MOTOR    | 02-B-01010     | NULL             |
|    3 | 2010-08-20 14:00:00 | PROPERTY | NULL           | Oxford Street    |
|    4 | 2010-08-20 15:00:00 | MOTOR    | 03-C-02020     | NULL             |
+------+---------------------+----------+----------------+------------------+

\------ COMMON FIELDS -------/          \----- SUBTYPE SPECIFIC FIELDS -----/

Memastikan reka bentuk mudah adalah satu kelebihan, tetapi masalah utama dengan pendekatan ini adalah seperti berikut:

• Semasa anda menambah subjenis baharu, anda mesti menukar jadual untuk menampung sifat yang menerangkan objek baharu ini. Ini boleh menjadi masalah dengan cepat apabila anda mempunyai banyak subjenis atau apabila anda bercadang untuk menambah subjenis dengan kerap.

• Pangkalan data tidak akan dapat menguatkuasakan sifat mana yang terpakai dan mana yang tidak, kerana tiada metadata untuk menentukan sifat mana yang tergolong dalam subjenis mana.

• Anda juga tidak boleh menguatkuasakan NOT NULL pada sifat subjenis yang harus dikuatkuasakan. Anda perlu mengendalikan ini dalam aplikasi, yang biasanya tidak sesuai.

Warisan jadual khusus:

Cara lain untuk menyelesaikan masalah pewarisan ialah mencipta jadual baharu untuk setiap subjenis, mengulangi semua sifat biasa dalam setiap jadual. Contohnya:

--// Table: policies_motor
+------+---------------------+----------------+
| id   | date_issued         | vehicle_reg_no |
+------+---------------------+----------------+
|    1 | 2010-08-20 12:00:00 | 01-A-04004     |
|    2 | 2010-08-20 13:00:00 | 02-B-01010     |
|    3 | 2010-08-20 15:00:00 | 03-C-02020     |
+------+---------------------+----------------+
                          
--// Table: policies_property    
+------+---------------------+------------------+
| id   | date_issued         | property_address |
+------+---------------------+------------------+
|    1 | 2010-08-20 14:00:00 | Oxford Street    |   
+------+---------------------+------------------+

Reka bentuk ini pada asasnya akan menyelesaikan masalah yang dikenal pasti melalui pendekatan jadual tunggal:

• Atribut wajib kini boleh dikuatkuasakan melalui NOT NULL .

• Menambah subjenis baharu memerlukan penambahan jadual baharu, bukan menambah lajur pada jadual sedia ada.

• Tiada juga risiko menetapkan atribut yang tidak sesuai untuk subjenis tertentu, seperti medan vehicle_reg_no dasar atribut.

• Tidak perlu atribut type seperti dalam kaedah jadual tunggal. Jenis ini kini ditakrifkan oleh metadata: nama jadual.

Tetapi model ini juga mempunyai beberapa kelemahan:

• Hartanah awam bercampur dengan sifat khusus subjenis dan tiada cara mudah untuk mengenal pastinya. Pangkalan data pun tak tahu.

• Apabila mentakrifkan jadual, anda mesti mengulang sifat sepunya untuk setiap jadual subjenis. Ini pastinya tidak kering.

• Mencari semua strategi tanpa mengira sub-genre menjadi sukar dan memerlukan sekumpulan UNION.

Tidak kira jenis, anda mesti bertanya semua strategi melalui:

SELECT     date_issued, other_common_fields, 'MOTOR' AS type
FROM       policies_motor
UNION ALL
SELECT     date_issued, other_common_fields, 'PROPERTY' AS type
FROM       policies_property;

Sila ambil perhatian bahawa menambah subjenis baharu memerlukan pengubahsuaian pertanyaan di atas dengan tambahan UNION ALL untuk setiap subjenis. Jika anda terlupa melakukan ini, anda boleh menyebabkan ralat dalam aplikasi anda dengan mudah.

Pewarisan jadual kelas (aka pewarisan jadual setiap jenis):

Ini adalah penyelesaian yang disebut oleh @David dalam jawapan lain . Anda membuat jadual untuk kelas asas yang merangkumi semua sifat awam. Anda kemudian akan membuat jadual khusus untuk setiap subjenis, yang kunci utamanya juga berfungsi sebagai jadual asas. Contoh:

CREATE TABLE policies (
   policy_id          int,
   date_issued        datetime,

   -- // other common attributes ...
);

CREATE TABLE policy_motor (
    policy_id         int,
    vehicle_reg_no    varchar(20),

   -- // other attributes specific to motor insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

CREATE TABLE policy_property (
    policy_id         int,
    property_address  varchar(20),

   -- // other attributes specific to property insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

Penyelesaian ini menyelesaikan masalah yang terdapat dalam dua reka bentuk lain:

• Atribut wajib boleh dikuatkuasakan melalui NOT NULL .

• Menambah subjenis baharu memerlukan penambahan jadual baharu, bukan menambah lajur pada jadual sedia ada.

• Tiada risiko menetapkan sifat yang tidak sesuai untuk subjenis tertentu.

• Tidak perlu atribut type.

• Kini hartanah awam tidak lagi bercampur dengan sifat khusus subjenis.

• Kita akhirnya boleh kekal kering. Pembuatan jadual tidak memerlukan pertindihan sifat biasa untuk setiap jadual subjenis.

• Menguruskan autokenaikan polisi id menjadi lebih mudah kerana ini boleh dikendalikan oleh jadual asas dan bukannya setiap jadual subjenis menjananya secara bebas.

• Mencari semua strategi (tanpa mengira sub-jenis) kini sangat mudah: tidak perlu UNION - 只需 SELECT * FROM 策略.

Saya rasa pendekatan seperti jadual adalah yang paling sesuai dalam kebanyakan kes.

---

Nama ketiga-tiga model ini berasal daripada Martin Fowlersebuah buku Corak Seni Bina Aplikasi Perusahaan.