Pertanyaan Skema PostgreSQL - Skenario Penggunaan / Kasus


Saya minta maaf untuk pertanyaan seperti newbie saya tetapi saya seorang pemula untuk PostgreSQl dan skema. Saya kesulitan memahami tujuan skema di PostgreSQL .. dan secara umum. Apa saja kasus penggunaan potensial untuk skema?

Buku saya menyatakan bahwa skema seperti direktori yang tidak dapat disarangkan. OK, jadi saya menganggap skema adalah sarana untuk mengelompokkan tabel dalam db. Buku ini tidak membahas bagaimana ini diterapkan, juga tidak menyebutkan potensi penggunaan kecuali satu contoh sepele (paragraf berikutnya).

Kasus penggunaan Manfaat & Potensi # 1 

Sejauh ini saya hanya memahami satu manfaat (yang tampaknya sepele) untuk skema. Manfaat ini adalah bahwa Anda dapat memiliki beberapa tabel dengan nama yang sama dan selama masing-masing tabel tersebut dalam skema yang berbeda, tidak akan ada konflik karena kualifikasi namespace (nama skema) dapat digunakan untuk mengatasi tabel tertentu yang diinginkan.

Saya tidak benar-benar mengerti mengapa Anda memiliki beberapa tabel dengan nama yang sama untuk memulai. Saya pikir ini adalah kasus yang sangat langka, namun dokumen itu datang kepada saya seolah-olah skema harus digunakan oleh semua orang. Memiliki beberapa tabel dengan nama yang sama sepertinya manajemen proyek yang buruk bagi saya dan memungkinkan untuk praktik buruk seperti itu tidak tampak seperti nilai. Pada akhirnya Anda hanya membiarkan kekacauan dibuat.

Satu-satunya skenario yang dapat saya pikirkan di mana Anda dapat memiliki konflik nama tabel yang seharusnya diperbolehkan adalah ketika Anda memiliki kelas yang sedang belajar SQL dan admin TI sekolah ingin setiap siswa dapat membuat tabel sesuai dengan keinginan mereka. Tentu saja, pertanyaan dalam pikiran saya adalah mengapa admin tidak membuat 1 db per siswa daripada 1 db untuk semua dan 1 skema untuk setiap siswa? Q1: Mengapa?

Apa saja kasus penggunaan lain yang menunjukkan manfaat skema?

IMPLEMENTASI - ATAU - SIFAT 

Saya juga bingung tentang implementasi / sifat skema.
Mari kita asumsikan bahwa kita memiliki 1 DB yang memiliki 3 skema. 1 skema per pengguna seperti:
user1 = 'admin'  - tableA, tableU, tableJ, tableK,
user2 = 'joe'------ tableU, tableJ,
user3 = 'kate ----- tableU, tableK.

Dalam contoh di atas, tujuan saya adalah untuk tableU untuk dibagikan di antara pengguna (menurut skema joe & skema kate). Mereka seharusnya tidak mendapatkan salinan meja mereka sendiri yang berdiri sendiri, mereka harus berbagi akses umum ke tabel ini. Jenis penggunaan ini masuk akal bagi saya. Pengguna harus menambahkan / mengubah / berpotensi menghapus rekaman ... tidak menambahkan / mengubah / menghapus tabel. Namun saya tidak yakin bahwa ini mungkin dengan skema PostgreSQL. Q2: Jika saya ingin berbagi tableU seperti yang saya jelaskan di atas, apakah schema joe & schema kate akan mendapatkan salinan tabel itu sendiri atau dapatkah saya menetapkan bahwa mereka seharusnya tidak mendapatkan salinannya sendiri dan sebagai gantinya hanya membagikan akses ke tabel yang sudah ada?

tableJ adalah samasebagai tableK... kecuali bahwa Kate tidak bisa menggunakan tableJ dan joe tidak bisa menggunakan tableK. Ini tampaknya menjadi esensi skema bagi saya sesuai dengan pemahaman saya yang terbatas tentang skema. Q3: Apa tujuan membuat salinan tabel untuk setiap pengguna? 2 tabel memiliki struktur yang sama (kolom dan batasan) dan itu adalah pemborosan ruang penyimpanan untuk membuat salinan berdiri sendiri dari tabel tersebut untuk setiap pengguna. Saya juga akan berpikir bahwa itu akan menjadi mimpi buruk jika setiap pengguna memiliki salinan masing-masing tabel. Kami akan melempar konsep kunci seperti normalisasi di luar jendela. Ini akan menjadi kekacauan yang tidak terawat. Dalam pikiran saya setiap pengguna harus menambahkan / mengubah / menghapus rekaman ke tabel umum dalam DB umum.

Saya telah melihat orang-orang dalam pencarian Google saya tentang skema membuat tabel + skema terpisah untuk setiap pelanggan online ke situs web mereka. Mereka punya sekitar 100.000 skema. Q4: Apa yang kulewatkan di sini? Ini tampaknya ekstrem untuk sedikitnya. Mereka harus menambahkan catatan (s) ke tabel standar (s) untuk setiap pelanggan tidak membuat skema dan tabel untuk setiap pelanggan. Ini hanya menambah kebingungan saya.

Bagaimanapun, saya harap saya telah menyatakan poin-poin kunci dari kebingungan saya dengan cukup jelas.

Yang saya cari adalah:
(1) Untuk menjernihkan manfaat skema melalui contoh kasus penggunaan yang realistis.
(2) Untuk menjernihkan detail implementasi skema.


EDIT v.3

Kasus penggunaan potensial # 2

Jika saya mengerti Neville K dengan benar, dia menyarankan sebagai use case:

1 DB yang memiliki 3 skema. 1 skema per pengguna seperti itu (nama pengguna == schema_name di ex.):
user1 = 'admin'  - tableA, tableLogin, tblFin1, tblFin2, tblFin3, tblPrj1, tblPrj2, tblPrj3.
user2 = 'joe'------ tableLogin, tblFin1, tblFin2, tblFin3
user3 = 'kate ----- tableLogin, tblPrj1, tblPrj2, tblPrj3.

Di sini, joe ada di departemen Fin, dan Kate adalah manajer proyek. Aplikasi yang digunakan joe dibatasi untuk tabel terkait Keuangan, aplikasi yang menggunakan kate dibatasi untuk tabel manajemen proyek. Pembatasan ini diberlakukan melalui nama pengguna mereka yang terikat pada skema yang terkait dengan jalur pencarian (diberlakukan pada tingkat DB).

Q5: Bukankah pembatasan yang sama berlaku tanpa skema? Tabel apa yang tersedia untuk aplikasi mana yang merupakan fungsi dari tabel apa yang ditetapkan untuk digunakan oleh aplikasi pada saat aplikasi dibuat oleh tim pengembang. Tidak? Atau apakah kita mengasumsikan bahwa kita menggunakan aplikasi rak yang Anda tunjuk ke db dan kami khawatir bahwa aplikasi tidak dapat dalam pengaturannya dibatasi untuk tabel tertentu (dan pembatasan ini dikunci dengan kata sandi di dalam rak-off aplikasi)?

Kasus penggunaan potensial # 3

Jika saya memahami Catcall dengan benar, dia menyarankan sebagai use case:

Skenario di mana Anda ingin menyewa / menyewakan layanan dari server database yang dihosting pada 1 sistem fisik ke beberapa pelanggan yang membutuhkan layanan basis data. Dengan demikian, skenario multi-tenant seperti muncul. Pada titik ini Anda dapat memilih untuk memiliki:
(1) Basis data terpisah untuk setiap penyewa (pelanggan)
  -Lebih aman dan nyaman tetapi dapat mendukung penyewa kurang per sistem.
(2) Satu basis data bersama dengan skema untuk setiap penyewa (pelanggan)
  -Lebih aman dan sedikit kurang nyaman tetapi dapat mendukung lebih banyak penyewa per sistem.

Kasus penggunaan potensial # 4

Jika saya memahami Scott Marlowe dan Catcall dengan benar, kasus penggunaan lainnya adalah:

Menggunakan skema untuk mengisolasi konten baru pengembang selama pengembangan. Kemudian dapat menggabungkan kerja ke skema lain.


32
2018-04-16 03:46


asal


Jawaban:


Saya telah melihat orang-orang di pencarian Google saya   tentang skema membuat terpisah   tabel + skema untuk setiap pelanggan online   ke situs web mereka. Mereka punya suka   100.000 skema. Q3: Apa yang saya lewatkan   sini? Ini sepertinya ekstrem untuk mengatakan   paling sedikit. Mereka harus menambahkan catatan (s)   ke tabel standar untuk setiap pelanggan   tidak membuat skema dan tabel untuk masing-masing   pelanggan.

Satu basis data per penyewa (pelanggan) mudah dibuat, dan ini memberi Anda isolasi terkuat antara penyewa, dan pemulihan bencana yang paling sederhana. Tapi harganya relatif mahal.

Satu skema per penyewa juga mudah dibangun. Ada tingkat isolasi yang lebih kecil di antara para penyewa. Sebuah dbms dapat mendukung lebih banyak penyewa per server dengan satu skema per penyewa dibandingkan dengan satu basis data per penyewa. Pemulihan bencana untuk satu penyewa lebih rumit daripada dengan satu basis data per penyewa.

Skema bersama mengharuskan setiap baris untuk memiliki pengenal penyewa. Hardware dan backup lebih murah; pemulihan bencana untuk satu penyewa bisa menjadi jalang yang nyata. (Untuk memulihkan data untuk penyewa tunggal, Anda harus memulihkan beberapa baris di setiap tabel. Kinerja menderita untuk semua penyewa ketika itu terjadi.) Isolasi lebih rumit. Karena penyewa berbagi tabel, pastikan tidak ada penyewa yang dapat mengakses data penyewa lain jauh lebih sulit daripada dengan satu basis data atau satu skema per penyewa.

Istilah pencarian untuk hal ini adalah "desain database multi-penyewa". SO juga memiliki  menandai.

Penggunaan umum lainnya adalah mengelompokkan objek basis data yang menjadi milik bersama. Misalnya, jika Anda mengembangkan basis data akuntansi, semua objek yang menerapkan fitur "hutang dagang" mungkin masuk dalam skema "ap". Saya juga menggunakan skema untuk ekstensi PostgreSQL. Dalam db saya, saya menginstal ekstensi hstore dalam skema "hst", ekstensi tablefunc dalam skema "tbf", dll.


17
2018-04-15 15:42



Jawaban Neville K menangkap esensi tetapi mungkin agak singkat.

Skema pada dasarnya adalah ruang nama. Mereka berguna dalam situasi yang sama yang namespaces berguna dalam pemrograman: di mana Anda memiliki banyak hal, begitu banyak yang ingin Anda partisi mereka menjadi sub-koleksi terpisah (banyak - tetapi lebih sedikit dari (katakanlah) 10.000 sub-seperti koleksi, mengingat bahwa ada satu tingkat), sementara mampu saling beroperasi di antara mereka. Menggunakan skema dapat memungkinkan lebih banyak standar penamaan 'alami' untuk objek database lainnya.

Sebagai tambahan, nilai ruang nama tidak dihargai oleh programmer baru. Manfaat yang tampaknya sepele dari memungkinkan beberapa objek untuk memiliki nama yang sama tidak begitu sepele, ternyata. Hanya ketika seseorang telah bekerja untuk sementara waktu pada proyek yang lebih besar (dengan ribuan 'objek kode': tabel, pandangan, indeks, prosedur tersimpan, domain, dll.) Yang satu ini memahami bahwa manfaat ruang nama melebihi biaya mereka.

Pada usia yang lebih dini (dan bukan dengan PostgreSQL), saya bekerja untuk biro komputer dengan sekitar 20 pelanggan, mulai dari 250 pengguna DBMS yang berunding hingga satu, masing-masing melalui saluran telepon sewa pribadi. (Ini sebelum internet.) Setiap pelanggan memiliki skema sendiri, dengan pengguna admin (peran) yang dapat membuat dan menjatuhkan pengguna lain saat karyawan datang dan pergi, memberikan dan mencabut hak istimewa, dan melakukan sejumlah pekerjaan definisi data terbatas dan impor / ekspor dalam skema mereka sendiri. Sebagai pengembang, saya harus menggunakan skema karena hanya ada satu contoh DBMS dan satu database. Jadi ... jika di atas tidak meyakinkan, Anda dapat mengatakan bahwa skema berada dalam standar SQL (karena alasan historis), oleh karena itu PostgreSQL memiliki dukungan untuk skema.

Saat ini, situasi yang agak mirip dengan tambang mungkin timbul di lingkungan laboratorium, di mana beberapa peneliti (atau ratusan siswa) masing-masing ingin bekerja pada data mereka sendiri sementara memiliki akses ke set umum dalam skema publik. Menggunakan skema membantu menghentikan kecelakaan: secara tidak sengaja menginjak-injak data satu sama lain.


7



Inilah penggunaan umum lainnya yang pernah saya lihat. Skema dan search_path izinkan dua atau lebih pengembang untuk mengerjakan satu salinan db besar tanpa perlu salinannya sendiri, tetapi tanpa saling menghalangi.

Katakanlah Joe bekerja pada tabel komentar, dan Jim sedang mengerjakan tabel alur kerja. Mereka berdua membutuhkan pengguna, kelompok, dll. Joe membuat skema, membuat tabel komentar versi kerjanya sendiri, dan dapat mengotak-atiknya sesuka hati:

create schema joe;
create table joe.comments (rest of new tabledef here);
set search_path='joe','public';

Sekarang ketika Joe alter table pada joe.comments tabel, Jim tidak melihat perubahan dan perkembangannya tidak terpengaruh oleh rusak joe.comments meja dll. Ketika kode Joe, memiliki path search_ 'joe','public' berjalan, ia melihat tabel komentar joe sementara orang lain melihat yang asli.

Setelah pengujian menyeluruh, tabel komentar Joe pada dasarnya dapat menggantikan yang asli dalam satu gerakan tanpa ada gangguan pada pengembangan Jim. Kalikan kali ini 40 pengembang dan itu memungkinkan semua 40 untuk bekerja pada dev db yang sama tanpa meniup barang-barang satu sama lain (atau kurang sering setidaknya).


7



Manfaat utama untuk skema adalah menyediakan pengelompokan logis untuk tabel database Anda. Kasus penggunaan yang paling mungkin adalah:

  • Untuk menjalankan aplikasi logis yang berbeda dalam DB yang sama - misalnya, Anda mungkin memiliki sistem perusahaan, dan ingin membuat skema yang disebut "profil pengguna", "proyek", "keuangan" dll.
  • Untuk membuat versi serupa dari kelompok tabel yang sama, misalnya untuk "pengembangan", "qa", dan "produksi"

4