Part 004 — Data Modelling for Enterprise Applications

1. Posisi Part Ini Dalam Seri

Part ini membahas data modelling PostgreSQL untuk aplikasi enterprise Java/JAX-RS.

Data modelling bukan kegiatan menggambar tabel agar aplikasi bisa menyimpan object Java. Data modelling adalah proses merancang struktur state bisnis agar benar, dapat berkembang, dapat di-query, dapat diaudit, dan dapat dioperasikan di production.

Di sistem CPQ, quote management, order management, atau quote-to-cash, model data yang buruk bisa membuat masalah menyebar ke banyak area:

• quote tidak bisa dibedakan antara draft, submitted, approved, expired,
• order lifecycle sulit ditelusuri,
• price berubah tetapi quote lama ikut berubah,
• audit tidak cukup untuk investigasi customer issue,
• event Kafka tidak bisa direkonsiliasi dengan database,
• migration menjadi berisiko karena constraint tidak jelas,
• query lambat karena cardinality dan index tidak dipikirkan sejak awal.

Prinsip utama: model data enterprise harus menjaga invariant bisnis sedekat mungkin dengan data, bukan hanya di service code.

2. Data Modelling Sebagai Correctness Boundary

Dalam aplikasi Java, kita bisa membuat validation di service layer:

if (quote.getStatus() != QuoteStatus.DRAFT) {
    throw new InvalidStateTransitionException();
}

Validasi seperti ini penting. Tetapi jika semua correctness hanya hidup di Java, database bisa tetap berisi data tidak valid karena:

• bug di service lain,
• migration script salah,
• manual data repair,
• batch job bypass validation,
• stored procedure/function,
• import data,
• race condition,
• deployment multi-version.

Database constraint membantu membatasi state yang tidak valid.

Contoh:

ALTER TABLE quote
ADD CONSTRAINT chk_quote_total_amount_non_negative
CHECK (total_amount >= 0);

Constraint bukan pengganti business logic. Constraint adalah last line of defense untuk invariant yang harus selalu benar.

3. Schema: Namespace dan Ownership Boundary

Schema di PostgreSQL adalah namespace untuk object database seperti table, view, function, sequence, dan type.

Contoh:

CREATE SCHEMA quote_order;
CREATE SCHEMA catalog;
CREATE SCHEMA audit;

Dalam enterprise system, schema dapat dipakai untuk:

• memisahkan domain area,
• membatasi privilege,
• mengelompokkan object migration,
• menandai ownership,
• menghindari nama table bentrok.

Schema Design Questions

• Apakah satu service memiliki satu schema?
• Apakah beberapa bounded context berbagi schema?
• Apakah schema dipakai untuk security boundary?
• Apakah migration tool mengelola semua schema?
• Apakah search_path dikontrol secara aman?

Java/JAX-RS Impact

Jika schema tidak eksplisit, query bisa bergantung pada search_path:

SELECT * FROM quote;

Dalam beberapa environment, ini bisa ambigu. Query eksplisit lebih aman jika policy internal mengizinkan:

SELECT * FROM quote_order.quote;

Namun style ini harus mengikuti convention internal. Beberapa tim memilih schema via connection config.

4. Table: Representasi State, Bukan Object Dump

Table harus merepresentasikan entity, relationship, event, snapshot, atau read model secara sadar.

Jenis table umum:

Bad Table Smell

• Satu table berisi terlalu banyak lifecycle berbeda.
• Banyak nullable column karena beberapa subtype dipaksa jadi satu table.
• Kolom data JSONB dipakai untuk semua hal tanpa governance.
• Tidak ada constraint selain primary key.
• Nama table terlalu teknis dan tidak merepresentasikan domain.
• Table dibuat mengikuti response API, bukan state bisnis.

5. Column: Data Type Adalah Contract

Pemilihan tipe kolom memengaruhi correctness, storage, index, query, dan mapping Java.

Contoh tipe umum:

Type Mapping ke Java

Beberapa mapping perlu dipastikan:

6. Primary Key

Primary key memberikan identity unik untuk row.

CREATE TABLE quote (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  quote_number text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

Primary key harus:

• immutable,
• unique,
• not null,
• tidak membawa informasi yang sering berubah,
• efisien untuk join,
• cocok dengan lifecycle entity.

Surrogate Key vs Natural Key

Surrogate key:

id bigint generated by default as identity primary key

Natural key:

quote_number text not null unique

Biasanya enterprise system memakai surrogate key sebagai PK dan natural key sebagai unique constraint.

CREATE TABLE quote (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  tenant_id uuid NOT NULL,
  quote_number text NOT NULL,
  CONSTRAINT uq_quote_tenant_quote_number UNIQUE (tenant_id, quote_number)
);

Why Both Matter

• Surrogate key bagus untuk FK dan join internal.
• Natural key bagus untuk business lookup, idempotency, dan external reference.

7. UUID vs Sequence/Identity

Sequence/Identity

Kelebihan:

• compact,
• index locality bagus,
• join efisien,
• mudah dibaca secara operasional.

Kekurangan:

• bisa menebak jumlah/urutan data jika terekspos,
• koordinasi antar database/shard lebih kompleks,
• external exposure perlu hati-hati.

UUID

Kelebihan:

• cocok untuk distributed id generation,
• aman sebagai public identifier relatif terhadap enumeration,
• bisa dibuat di aplikasi.

Kekurangan:

• index lebih besar,
• locality buruk untuk UUID random,
• payload lebih besar,
• lebih sulit dibaca manusia.

Pattern Umum

Sering kali dipakai dua identifier:

CREATE TABLE quote (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  public_id uuid NOT NULL DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL,
  quote_number text NOT NULL,
  CONSTRAINT uq_quote_public_id UNIQUE (public_id),
  CONSTRAINT uq_quote_tenant_quote_number UNIQUE (tenant_id, quote_number)
);

• id: internal join key.
• public_id: external API identifier.
• quote_number: business identifier.

Harus diverifikasi apakah pattern seperti ini cocok dengan standard internal.

8. Foreign Key

Foreign key menjaga referential integrity.

CREATE TABLE quote_item (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  quote_id bigint NOT NULL REFERENCES quote(id),
  product_offering_id bigint NOT NULL,
  quantity numeric(18, 4) NOT NULL
);

Apa yang Dijaga FK

FK memastikan child tidak menunjuk parent yang tidak ada.

Tanpa FK:

• orphan row mudah muncul,
• JOIN menghasilkan missing data,
• audit/reconciliation sulit,
• delete/update parent bisa merusak child.

FK dalam Microservices

Di microservices, FK lintas service boundary biasanya tidak cocok karena tiap service harus memiliki database/schema ownership sendiri.

Contoh yang perlu hati-hati:

order_service.order.customer_id -> customer_service.customer.id

Jika berbeda service, jangan otomatis membuat FK langsung. Pertimbangkan:

• reference id disimpan tanpa FK,
• local reference snapshot,
• event-driven replication,
• read model,
• validation via API/domain event,
• reconciliation job.

FK Delete Action

ON DELETE CASCADE
ON DELETE RESTRICT
ON DELETE SET NULL

Harus dipilih berdasarkan lifecycle bisnis, bukan convenience.

ON DELETE CASCADE bisa berbahaya jika menghapus parent menghapus banyak child tanpa sadar.

9. Unique Constraint

Unique constraint menjaga uniqueness invariant.

ALTER TABLE quote
ADD CONSTRAINT uq_quote_tenant_quote_number
UNIQUE (tenant_id, quote_number);

Use case:

• quote number unik per tenant,
• idempotency key unik per tenant,
• external reference unik,
• active config key unik,
• natural key reference table.

Unique Constraint untuk Race Condition

Jangan hanya validasi uniqueness di Java:

if (!repository.existsByQuoteNumber(quoteNumber)) {
    repository.insert(...);
}

Dua request concurrent bisa lolos check dan insert bersamaan. Unique constraint adalah boundary final.

Aplikasi harus menangani unique violation dan map ke error domain yang tepat.

Partial Unique Index

Untuk soft delete atau active record:

CREATE UNIQUE INDEX uq_quote_active_number
ON quote (tenant_id, quote_number)
WHERE deleted_at IS NULL;

Ini mengizinkan quote_number lama digunakan ulang setelah soft delete jika memang domain memperbolehkan. Jika tidak, gunakan unique constraint penuh.

10. Check Constraint

Check constraint membatasi nilai kolom atau kombinasi kolom.

ALTER TABLE quote_item
ADD CONSTRAINT chk_quote_item_quantity_positive
CHECK (quantity > 0);

Contoh status/timestamp:

ALTER TABLE quote
ADD CONSTRAINT chk_quote_submitted_at_required
CHECK (
  (status <> 'SUBMITTED') OR (submitted_at IS NOT NULL)
);

Kapan Check Constraint Tepat

Gunakan untuk invariant lokal dalam satu row:

• amount tidak negatif,
• quantity positif,
• start date sebelum end date,
• submitted_at wajib jika status submitted,
• mutually exclusive column.

Jangan gunakan check constraint untuk logic yang butuh query ke table lain. Untuk itu perlu FK, trigger, atau logic aplikasi.

11. NOT NULL

NOT NULL adalah constraint paling sederhana tetapi sangat penting.

customer_id bigint NOT NULL

Nullable column harus punya alasan domain yang jelas.

Nullable Column Smell

• Nullable karena “belum tahu nanti perlu apa”.
• Nullable karena beberapa subtype digabung paksa.
• Nullable karena migration belum selesai tetapi tidak ada contract final.
• Nullable karena aplikasi malas mengisi value.

Java Impact

Nullable DB column harus tercermin di Java model:

• Optional untuk read model jika sesuai,
• nullable annotation,
• validation di boundary,
• test untuk null handling.

Mismatch antara DB nullable dan Java non-null assumption sering menjadi runtime bug.

12. Default Value

Default value membantu menjaga insert tetap konsisten.

created_at timestamp with time zone NOT NULL DEFAULT now()

status text NOT NULL DEFAULT 'DRAFT'

Default Value Review

Tanyakan:

• Apakah default merepresentasikan domain yang benar?
• Apakah default aplikasi dan DB konsisten?
• Apakah default menyembunyikan bug caller?
• Apakah default aman saat migration backfill?

Default seperti status = 'DRAFT' bisa baik untuk quote baru. Tetapi default untuk field mandatory bisnis kadang menyembunyikan caller yang lupa mengirim data.

13. Enum Type

PostgreSQL enum dapat membatasi nilai status.

CREATE TYPE quote_status AS ENUM (
  'DRAFT',
  'SUBMITTED',
  'APPROVED',
  'REJECTED',
  'EXPIRED'
);

CREATE TABLE quote (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  status quote_status NOT NULL
);

Kelebihan

• nilai valid dibatasi di database,
• lebih kuat daripada text bebas,
• query lebih self-documenting.

Kekurangan

• perubahan enum perlu migration hati-hati,
• rename/remove value tidak sesederhana update text,
• deployment multi-version perlu dipikirkan,
• mapping Java enum harus sinkron.

Alternatif

• text + check constraint,
• reference table,
• domain type,
• application enum + DB constraint.

Untuk status yang sering berubah atau butuh metadata, reference table bisa lebih fleksibel.

14. Domain Type

Domain type membungkus tipe dasar dengan constraint.

CREATE DOMAIN positive_money AS numeric(18, 4)
CHECK (VALUE >= 0);

Lalu:

price positive_money NOT NULL

Kapan Domain Type Berguna

• constraint yang berulang di banyak table,
• semantic type yang penting,
• mengurangi duplikasi check constraint.

Caution

Domain type menambah dependency migration. Pastikan:

• migration order benar,
• tool migration mendukung perubahan domain,
• Java/MyBatis mapping tidak bermasalah,
• team memahami penggunaannya.

15. Generated Column

Generated column menyimpan nilai hasil ekspresi dari kolom lain.

CREATE TABLE quote_item (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  unit_price numeric(18, 4) NOT NULL,
  quantity numeric(18, 4) NOT NULL,
  line_total numeric(18, 4) GENERATED ALWAYS AS (unit_price * quantity) STORED
);

Kapan Berguna

• derived value sederhana,
• expression sering dipakai query/index,
• menghindari inkonsistensi antara source column dan derived column.

Caution

Tidak semua business calculation cocok jadi generated column. Pricing CPQ sering punya rules kompleks, discount, tax, rounding, effective date, eligibility, dan audit. Logic seperti itu biasanya lebih baik dikelola di domain/service/rules engine atau snapshot eksplisit, bukan generated column sederhana.

16. Normalization

Normalization memecah data untuk mengurangi duplikasi dan anomaly.

Contoh normalized:

quote
  id
  customer_id
  status

quote_item
  id
  quote_id
  product_offering_id
  quantity

Manfaat:

• mengurangi data duplication,
• menjaga referential integrity,
• update lebih aman,
• storage lebih efisien,
• model domain lebih jelas.

Normalization Bukan Agama

Normalization bagus untuk OLTP correctness. Tetapi query tertentu mungkin butuh read model denormalized.

Prinsipnya:

• normalized model untuk write/source-of-truth,
• denormalized model untuk read/reporting jika terbukti perlu.

17. Denormalization

Denormalization menyimpan data duplikat untuk mempercepat read atau membekukan snapshot.

Contoh:

CREATE TABLE quote_item_snapshot (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  quote_id bigint NOT NULL,
  product_offering_id bigint NOT NULL,
  product_name text NOT NULL,
  price_amount numeric(18, 4) NOT NULL,
  currency_code text NOT NULL,
  captured_at timestamp with time zone NOT NULL DEFAULT now()
);

Ini bisa benar jika quote harus menyimpan snapshot product/price pada saat quote dibuat.

Denormalization yang Sehat

Denormalization harus punya:

• source-of-truth jelas,
• refresh/update strategy,
• consistency expectation,
• reconciliation mechanism,
• migration/backfill plan,
• observability.

Denormalization yang Berbahaya

• data duplicate tanpa owner,
• tidak jelas kapan diperbarui,
• tidak ada audit,
• tidak ada reconciliation,
• dipakai untuk write decision kritikal tetapi stale.

18. Reference Table

Reference table menyimpan nilai referensi yang relatif stabil.

CREATE TABLE currency (
  code text PRIMARY KEY,
  name text NOT NULL,
  decimal_places integer NOT NULL,
  active boolean NOT NULL DEFAULT true
);

Dibanding enum, reference table lebih fleksibel jika butuh:

• label/display name,
• active/inactive flag,
• metadata,
• effective date,
• tenant-specific configuration,
• audit.

FK ke Reference Table

currency_code text NOT NULL REFERENCES currency(code)

Cocok jika reference data dimiliki database/service yang sama. Jika reference data berasal dari service lain, pikirkan ownership boundary.

19. Audit Columns

Audit columns umum:

created_at timestamp with time zone NOT NULL DEFAULT now(),
created_by text,
updated_at timestamp with time zone NOT NULL DEFAULT now(),
updated_by text

Pertanyaan Review

• Apakah created_at immutable?
• Apakah updated_at selalu berubah saat update?
• Apakah created_by/updated_by berisi user, service account, atau system actor?
• Apakah timezone policy jelas?
• Apakah audit column cukup, atau perlu audit table/history?

Audit Column vs Audit Trail

Audit columns hanya menunjukkan state terakhir. Untuk investigasi, sering perlu audit trail:

quote_status_history
quote_audit_log
order_state_transition

Audit column bukan pengganti history jika sistem perlu trace perubahan.

20. Soft Delete

Soft delete menyimpan data tetapi menandainya sebagai deleted.

deleted_at timestamp with time zone,
deleted_by text

Query aktif harus menambahkan:

WHERE deleted_at IS NULL

Kelebihan

• data masih bisa diaudit,
• restore lebih mudah,
• tidak langsung merusak FK,
• mendukung retention workflow.

Kekurangan

• semua query harus konsisten,
• unique constraint lebih kompleks,
• table terus tumbuh,
• reporting harus tahu deleted semantics,
• privacy/right-to-delete requirement mungkin tidak terpenuhi hanya dengan soft delete.

Partial Unique Index dengan Soft Delete

CREATE UNIQUE INDEX uq_active_quote_number
ON quote (tenant_id, quote_number)
WHERE deleted_at IS NULL;

Gunakan hanya jika business rule mengizinkan reuse setelah delete.

21. Temporal Validity dan Effective Date

Enterprise system sering perlu data yang valid pada periode tertentu.

Contoh harga:

CREATE TABLE product_price (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_offering_id bigint NOT NULL,
  price_amount numeric(18, 4) NOT NULL,
  currency_code text NOT NULL,
  valid_from timestamp with time zone NOT NULL,
  valid_to timestamp with time zone,
  CONSTRAINT chk_product_price_valid_range
    CHECK (valid_to IS NULL OR valid_to > valid_from)
);

Query harga aktif:

SELECT *
FROM product_price
WHERE product_offering_id = #{productOfferingId}
  AND valid_from <= #{asOfTime}
  AND (valid_to IS NULL OR valid_to > #{asOfTime})
ORDER BY valid_from DESC
LIMIT 1;

Correctness Concern

Perlu mencegah overlapping validity range jika business rule menuntut satu harga aktif per waktu. Ini tidak selalu trivial dengan constraint biasa. PostgreSQL exclusion constraint bisa relevan, tetapi perlu dibahas lebih lanjut di part lanjutan.

Java Impact

Service harus jelas memakai:

• current time,
• quote creation time,
• order submission time,
• customer-requested effective date,
• processing time.

Bug temporal sering muncul karena aplikasi memakai waktu yang salah.

22. Multi-Tenant Data Model

Multi-tenancy bisa didesain dengan beberapa pendekatan:

Shared Table Tenant Column

CREATE TABLE quote (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  tenant_id uuid NOT NULL,
  quote_number text NOT NULL,
  status text NOT NULL,
  CONSTRAINT uq_quote_tenant_number UNIQUE (tenant_id, quote_number)
);

Tenant predicate harus menjadi bagian dari hampir semua query aplikasi:

WHERE tenant_id = #{tenantId}

Tenant Boundary Risk

Bug paling serius di multi-tenant system adalah data leak antar tenant.

Mitigasi:

• tenant_id wajib di unique/index design,
• query review wajib cek tenant predicate,
• test tenant isolation,
• RLS jika policy internal mengizinkan,
• repository API mewajibkan tenant context,
• observability untuk query tanpa tenant predicate jika mungkin.

23. Data Model untuk State Machine

Order/quote lifecycle sering berupa state machine.

Simplistic model:

status text NOT NULL

Lebih lengkap:

CREATE TABLE order_status_history (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES customer_order(id),
  from_status text,
  to_status text NOT NULL,
  reason_code text,
  changed_at timestamp with time zone NOT NULL DEFAULT now(),
  changed_by text NOT NULL
);

Current State vs History

Biasanya entity utama menyimpan current state:

customer_order.status

History table menyimpan transition:

order_status_history

Perlu dijaga agar current state dan history konsisten. Ini bisa dilakukan oleh aplikasi dalam satu transaksi, trigger, atau stored procedure. Pilihan harus mengikuti policy internal dan observability kebutuhan.

24. Snapshot Modelling

Snapshot menyimpan salinan state pada waktu tertentu.

Dalam CPQ/order management, snapshot penting karena product catalog atau price bisa berubah setelah quote/order dibuat.

Contoh:

CREATE TABLE quote_snapshot (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  quote_id bigint NOT NULL REFERENCES quote(id),
  snapshot_version integer NOT NULL,
  snapshot_payload jsonb NOT NULL,
  captured_at timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT uq_quote_snapshot_version UNIQUE (quote_id, snapshot_version)
);

Snapshot Questions

• Apa yang dibekukan?
• Kapan snapshot dibuat?
• Apakah snapshot immutable?
• Apakah snapshot dipakai untuk pricing/audit/customer document?
• Apakah JSONB schema version disimpan?
• Bagaimana snapshot dimigrasi jika format berubah?

Snapshot sering lebih tepat daripada join live ke catalog untuk quote/order historis.

25. Outbox Table as Data Model

Outbox adalah table domain integration, bukan hanya queue lokal.

CREATE TABLE outbox_event (
  id uuid PRIMARY KEY,
  aggregate_type text NOT NULL,
  aggregate_id text NOT NULL,
  event_type text NOT NULL,
  event_version integer NOT NULL,
  payload jsonb NOT NULL,
  status text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  published_at timestamp with time zone
);

Design Questions

• Apakah event dibuat dalam transaksi yang sama dengan state change?
• Apakah payload cukup untuk consumer?
• Apakah event schema version jelas?
• Apakah status lifecycle event jelas?
• Apakah idempotency consumer didukung?
• Apakah retry/replay bisa dilakukan?
• Apakah WAL/CDC impact dipantau?

Outbox akan dibahas lebih dalam di part CDC/event consistency, tetapi model datanya harus dipikirkan sejak awal.

26. Read Model and Reporting Table

Read model adalah struktur data yang dioptimalkan untuk query baca.

CREATE TABLE order_summary_read_model (
  tenant_id uuid NOT NULL,
  order_id bigint PRIMARY KEY,
  order_number text NOT NULL,
  customer_name text NOT NULL,
  status text NOT NULL,
  total_amount numeric(18, 4) NOT NULL,
  item_count integer NOT NULL,
  last_status_changed_at timestamp with time zone,
  updated_at timestamp with time zone NOT NULL
);

Kapan Read Model Berguna

• query list terlalu banyak JOIN,
• reporting butuh aggregate cepat,
• API read path sangat high traffic,
• data berasal dari beberapa service,
• eventual consistency dapat diterima.

Review Questions

• Source-of-truth table apa?
• Bagaimana read model di-refresh?
• Apakah stale data acceptable?
• Apakah ada reconciliation?
• Apakah read model ikut backup/restore strategy?
• Apakah consumer tahu consistency model?

27. Modelling Relationship

One-to-One

Contoh quote dengan metadata tambahan:

CREATE TABLE quote_metadata (
  quote_id bigint PRIMARY KEY REFERENCES quote(id),
  metadata jsonb NOT NULL
);

Gunakan jika data optional/besar/jarang diakses dan ingin dipisah.

One-to-Many

Quote ke quote item:

quote 1 -> many quote_item

Child table harus punya FK dan index pada FK jika sering query by parent.

CREATE INDEX idx_quote_item_quote_id
ON quote_item (quote_id);

Many-to-Many

Gunakan junction table:

CREATE TABLE product_bundle_item (
  bundle_id bigint NOT NULL,
  product_id bigint NOT NULL,
  PRIMARY KEY (bundle_id, product_id)
);

Hindari menyimpan list id dalam text/JSON jika relasi perlu query, constraint, dan join.

28. Modelling Money

Jangan memakai floating point untuk uang.

Gunakan numeric dengan precision/scale yang jelas:

amount numeric(18, 4) NOT NULL,
currency_code text NOT NULL

Money Modelling Questions

• Apakah amount bisa negatif?
• Apakah currency wajib?
• Apakah precision cukup untuk telco/pricing use case?
• Apakah rounding policy ada?
• Apakah tax/discount disimpan terpisah?
• Apakah original amount dan adjusted amount perlu disimpan?
• Apakah currency conversion perlu audit rate?

Constraint Example

ALTER TABLE quote_item
ADD CONSTRAINT chk_quote_item_amount_non_negative
CHECK (amount >= 0);

Tetapi refund/credit mungkin membutuhkan negative amount. Jangan membuat constraint tanpa memahami domain.

29. Modelling Status

Status bisa dimodelkan dengan:

• text + check constraint,
• PostgreSQL enum,
• reference table,
• state transition table,
• current status + history.

Text + check:

status text NOT NULL,
CONSTRAINT chk_quote_status
CHECK (status IN ('DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED', 'EXPIRED'))

Reference table:

CREATE TABLE quote_status_ref (
  code text PRIMARY KEY,
  description text NOT NULL,
  terminal boolean NOT NULL DEFAULT false
);

Status Design Questions

• Apakah status berubah sering?
• Apakah status butuh metadata?
• Apakah transition valid perlu disimpan?
• Apakah terminal state immutable?
• Apakah audit transition wajib?
• Apakah status dipakai di event contract?

30. Modelling Versioning

Version column mendukung optimistic locking.

version integer NOT NULL DEFAULT 0

Update:

UPDATE quote
SET status = #{newStatus},
    version = version + 1,
    updated_at = now()
WHERE id = #{quoteId}
  AND version = #{expectedVersion}
RETURNING id, version;

Jika row count 0, ada conflict atau precondition gagal.

Versioning untuk Catalog

Catalog/versioned product data bisa membutuhkan version yang lebih domain-specific:

product_offering.version
catalog_version.effective_from
price.version

Jangan campur optimistic locking technical version dengan business version tanpa alasan jelas.

31. Modelling Idempotency

API command penting sering membutuhkan idempotency key.

CREATE TABLE idempotency_request (
  tenant_id uuid NOT NULL,
  idempotency_key text NOT NULL,
  request_hash text NOT NULL,
  response_payload jsonb,
  status text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, idempotency_key)
);

Design Questions

• Apakah key scoped per tenant/user/API?
• Apakah request hash disimpan untuk mendeteksi reuse berbeda payload?
• Apakah response disimpan?
• Apakah status in-progress perlu?
• Bagaimana handling concurrent same key?
• Berapa retention period?

Idempotency adalah data modelling problem sekaligus transaction/concurrency problem.

32. Naming Convention

Naming convention bukan kosmetik. Ia membantu review dan operations.

Contoh convention:

pk_<table>
fk_<child>_<parent>
uq_<table>_<columns>
chk_<table>_<rule>
idx_<table>_<columns>

Contoh:

CONSTRAINT fk_quote_item_quote FOREIGN KEY (quote_id) REFERENCES quote(id)
CONSTRAINT uq_quote_tenant_number UNIQUE (tenant_id, quote_number)
CONSTRAINT chk_quote_item_quantity_positive CHECK (quantity > 0)

Why It Matters

Saat error production muncul:

duplicate key value violates unique constraint "uq_quote_tenant_number"

Nama constraint yang jelas langsung membantu mapping ke domain error.

33. Index Is Part of the Model

Index sering dianggap performance-only. Padahal index juga bagian dari model akses data.

Jika query utama selalu:

WHERE tenant_id = ?
  AND quote_number = ?

Maka model harus mencerminkan natural lookup:

CREATE UNIQUE INDEX uq_quote_tenant_number
ON quote (tenant_id, quote_number);

Foreign Key Index

PostgreSQL tidak otomatis membuat index pada referencing column FK. Jika child sering dicari by parent, buat index:

CREATE INDEX idx_quote_item_quote_id
ON quote_item (quote_id);

Tanpa index, delete/update parent atau query child by parent bisa mahal.

Index strategy akan dibahas lebih dalam di part indexing, tetapi data modelling harus memberi sinyal access pattern.

34. Model Evolution

Model data akan berubah. Desain harus mempertimbangkan evolusi.

Contoh perubahan:

• tambah kolom nullable,
• backfill data,
• ubah kolom menjadi NOT NULL,
• tambah constraint,
• split table,
• merge table,
• ubah enum/status,
• tambah index concurrently,
• ubah JSONB schema,
• tambah read model.

Expand-Contract Mindset

Untuk zero-downtime deployment:

1. Expand schema secara backward-compatible.
2. Deploy aplikasi yang menulis/membaca format baru.
3. Backfill data.
4. Validasi dan reconcile.
5. Contract schema lama setelah aman.

Data model yang terlalu rigid tanpa migration path akan menyulitkan deployment enterprise.

35. Data Modelling and MyBatis

MyBatis membuat mapping eksplisit antara table/query dan Java object.

Data model yang baik membantu mapper menjadi sederhana:

<resultMap id="QuoteResultMap" type="QuoteRecord">
  <id property="id" column="id" />
  <result property="tenantId" column="tenant_id" />
  <result property="quoteNumber" column="quote_number" />
  <result property="status" column="status" />
  <result property="createdAt" column="created_at" />
</resultMap>

Model yang buruk membuat mapper penuh workaround:

• banyak nullable branch,
• dynamic SQL kompleks,
• JSON parsing untuk data yang seharusnya relational,
• manual dedup karena join explosion,
• mapping status tanpa constraint,
• custom TypeHandler berlebihan.

Mapper Boundary

Jangan biarkan bentuk table selalu sama dengan response API. Kadang perlu:

• persistence record,
• domain model,
• DTO response,
• read model query.

36. Data Modelling and JDBC

JDBC concern yang dipengaruhi data model:

• generated key retrieval,
• UUID binding,
• JSONB binding,
• enum mapping,
• timestamp mapping,
• numeric precision,
• array handling,
• large object handling,
• SQLState mapping dari constraint.

Constraint name yang jelas membantu error mapping:

uq_idempotency_tenant_key -> HTTP 409 Conflict / cached idempotent response
chk_quote_item_quantity_positive -> HTTP 400 Bad Request / domain validation error
fk_quote_item_quote -> HTTP 404 or consistency error depending context

Mapping harus mengikuti API contract internal.

37. Data Modelling in Microservices

Dalam microservices, data modelling tidak hanya soal table. Ia soal ownership.

Database-per-Service

Service memiliki schema/database sendiri. Service lain tidak boleh query langsung table internal.

quote-service owns quote schema
order-service owns order schema
catalog-service owns catalog schema

Shared Database Anti-Pattern

Jika banyak service membaca/menulis table yang sama:

• ownership kabur,
• migration sulit,
• coupling tinggi,
• data invariant tidak jelas,
• incident blast radius lebih besar.

Cross-Service Reference

Order mungkin menyimpan customer_id dari customer service. Itu bukan berarti harus FK langsung ke database customer service.

Alternatif:

• store external reference id,
• store snapshot field penting,
• consume customer events into local read model,
• reconcile periodic.

38. Data Modelling in Event-Driven Architecture

Event-driven architecture membutuhkan model data yang mendukung consistency.

Pertanyaan:

• State change mana yang menghasilkan event?
• Apakah event dibuat dalam transaksi yang sama?
• Apakah event payload berasal dari source-of-truth atau read model?
• Apakah event id unik?
• Apakah aggregate version disertakan?
• Apakah consumer bisa idempotent?
• Apakah replay aman?

Table state dan outbox harus dirancang bersama, bukan terpisah.

39. Data Modelling in Kubernetes, Cloud, and On-Prem

Kubernetes

Jika aplikasi scale out, data model harus tahan terhadap concurrency lebih tinggi:

• unique constraint untuk race,
• optimistic locking,
• idempotency key,
• short transaction,
• predictable index.

AWS/Azure Managed PostgreSQL

Managed DB memberi backup/HA/monitoring, tetapi model data tetap tanggung jawab aplikasi. Constraint buruk tetap menghasilkan data buruk.

On-Prem

Operational cost lebih besar. Model yang menghasilkan table besar tanpa retention/partition strategy akan menjadi beban storage, backup, vacuum, dan restore.

Hybrid

Jika ada latency antar network, model yang menyebabkan chatty query atau cross-service lookup synchronous akan lebih bermasalah.

40. Failure Modes of Bad Data Modelling

41. How to Detect Data Modelling Problems

Sinyal di codebase dan production:

• banyak query memakai DISTINCT untuk memperbaiki duplicate,
• banyak nullable check di Java,
• mapper terlalu kompleks,
• JSONB diparse di banyak tempat,
• constraint violation sering muncul tetapi tidak dipahami,
• data repair manual sering dibutuhkan,
• reporting query sangat kompleks,
• migration sulit karena table terlalu besar/tidak jelas owner,
• audit tidak cukup untuk RCA,
• duplicate data antar table tidak bisa direkonsiliasi.

42. How to Debug Data Modelling Problems

Workflow:

1. Identifikasi invariant bisnis yang dilanggar.
2. Temukan table dan kolom yang merepresentasikan invariant itu.
3. Cek apakah ada constraint DB.
4. Cek apakah service layer validation konsisten.
5. Cek apakah ada path lain yang menulis data.
6. Cek migration/backfill/manual repair history.
7. Cek concurrency race yang mungkin melewati validation.
8. Cek audit/history untuk asal perubahan.
9. Cek apakah model perlu constraint, index, history table, atau redesign.
10. Buat reconciliation query untuk mengukur luas masalah.

43. Data Modelling Review Checklist

Entity and Ownership

• Entity apa yang dimodelkan?
• Siapa owner table/schema?
• Apakah table ini source-of-truth atau read model?
• Apakah table ini internal service atau shared?

Identity

• Apakah PK immutable?
• Apakah butuh public id?
• Apakah natural key punya unique constraint?
• Apakah id strategy cocok untuk distributed system?

Constraints

• Apakah NOT NULL sudah tepat?
• Apakah unique constraint mencerminkan invariant?
• Apakah FK diperlukan dan sesuai boundary?
• Apakah check constraint bisa melindungi invariant lokal?
• Apakah constraint name jelas?

Lifecycle

• Apakah status/lifecycle jelas?
• Apakah transition history diperlukan?
• Apakah soft delete diperlukan?
• Apakah retention/deletion policy jelas?
• Apakah audit cukup?

Temporal

• Apakah timestamp type benar?
• Apakah UTC/as-of-time policy jelas?
• Apakah effective date diperlukan?
• Apakah overlap validity dicegah?

Queryability

• Query utama apa?
• Apakah index strategy terlihat?
• Apakah JSONB dipakai untuk data yang perlu query/filter?
• Apakah reporting butuh read model?

Migration

• Apakah perubahan backward-compatible?
• Apakah perlu backfill?
• Apakah table besar terdampak?
• Apakah rollback/roll-forward jelas?

Security/Privacy

• Apakah tenant boundary ada?
• Apakah PII diklasifikasi?
• Apakah data sensitive perlu masking/encryption?
• Apakah access/audit perlu?

44. Internal Verification Checklist

Cek hal berikut di internal CSG/team sebelum menganggap pattern tertentu valid.

Repository and Migration

• Folder migration yang membuat schema/table.
• Naming convention constraint/index.
• Tool migration yang digunakan: Liquibase, Flyway, atau custom.
• Migration yang menambah NOT NULL/unique/FK/check.
• Migration untuk enum/domain/function.

Actual Schema

• Table utama quote/order/catalog/customer/account.
• Primary key strategy.
• Public identifier strategy.
• Tenant/account boundary.
• FK dan unique constraint.
• Check constraint.
• Soft delete column.
• Audit column.
• History table.
• Snapshot table.
• Outbox table.
• Read model/reporting table.

MyBatis/JDBC

• Mapper untuk entity utama.
• ResultMap dan TypeHandler.
• Mapping enum/status.
• Mapping timestamp/timezone.
• JSONB mapping.
• Constraint violation mapping.

Operations

• Table growth rate.
• Largest tables.
• Retention/archival policy.
• Backup/restore implication.
• Vacuum/bloat dashboard.
• Slow queries terkait model.

Team Discussion

• Siapa owner schema/table?
• Apakah DBA review required?
• Apakah shared DB diperbolehkan?
• Apakah FK lintas service diperbolehkan?
• Apakah enum PostgreSQL diperbolehkan?
• Apakah JSONB governance ada?
• Apakah RLS digunakan atau dilarang?

45. Senior Engineer Heuristics

1. Table bukan object Java; table adalah state contract.
2. Constraint adalah dokumentasi executable untuk invariant penting.
3. Nullable column harus punya alasan domain, bukan alasan convenience.
4. Unique constraint adalah cara mencegah race, bukan sekadar validasi data.
5. FK bagus di dalam ownership boundary, berbahaya jika melanggar service ownership.
6. JSONB bagus untuk fleksibilitas, buruk jika menjadi tempat membuang semua modelling decision.
7. Audit columns bukan audit trail.
8. Soft delete menambah complexity ke semua query.
9. Effective-dated data harus dirancang sejak awal untuk pricing/catalog/order history.
10. Read model harus punya refresh, stale-data, dan reconciliation story.
11. Data model yang tidak bisa dimigrasi dengan aman belum production-ready.
12. Model data harus bisa dijelaskan dengan invariant bisnis, bukan hanya diagram ERD.

46. Ringkasan

Data modelling enterprise di PostgreSQL adalah disiplin untuk membuat state bisnis menjadi eksplisit, benar, queryable, evolvable, dan operable.

Setelah part ini, Anda harus mampu:

• membedakan entity, reference, history, audit, snapshot, outbox, dan read model,
• memilih primary key, natural key, UUID, sequence/identity secara sadar,
• menggunakan FK, unique, check, NOT NULL, default, enum, domain, dan generated column dengan tepat,
• memahami normalization dan denormalization sebagai trade-off,
• mendesain soft delete, audit, temporal validity, dan multi-tenancy,
• melihat hubungan data model dengan Java/JAX-RS, MyBatis, JDBC, microservices, event-driven architecture, Kubernetes/cloud/on-prem,
• melakukan review schema/migration dengan production mindset.

Part berikutnya akan masuk ke CPQ and Order Management Data Modelling Context, yaitu bagaimana pola modelling ini muncul pada customer, account, product catalog, price, quote, order, approval, fulfillment, state transition, snapshot, outbox, dan reporting model.