Database Overview¶

The database is the system's source of truth. There is no application server — every till and back-office PC talks straight to Microsoft SQL Server. This page covers the naming conventions and the table catalogue; the mechanics of stock and posting have their own pages.

Where this comes from

There is no standalone DDL / .sql schema file in the codebase. The schema below is reconstructed from the FrameWork's typed data-access classes (one class per table/view). Column names are authoritative; column data types are inferred from the VB field types (String→varchar, Double→numeric, date fields→datetime).

Naming conventions¶

Table names encode their role via a prefix, and every column is prefixed with a short per-table tag.

Prefix	Meaning	Examples
`tbMaster_`	Master / reference data (long-lived entities)	`tbMaster_Barang`, `tbMaster_Supplier`, `tbMaster_Stock`
`tbTr_`	Transactional documents	`tbTr_BackOffice_H`, `tbTr_Penjualan_H`, `tbTr_PurchaseOrder_H`
`tbHist_`	Historical / period snapshots	`tbHist_StockGudang`, `tbHist_HargaBeli`
`tbRekap_`	Recap / aggregate summaries	`tbRekap_SalesHarian`
`tbTabel_` / `tbTable_`	Small lookup / code / config tables	`tbTabel_Parameter`, `tbTabel_JenisTransaksi`
`tbTemp_`	Temporary / working (session) tables	`tbTemp_BackOffice`, `tbTemp_Penjualan`
`tbMonitoring_`	Monitoring / tracking (with `_H`/`_D`)	`tbMonitoring_Barang_H/D`
`tv_` (`tvTr_`)	SQL views (read-only joins)	`tv_Master_Barang`, `tv_BackOffice_H`
`REF_`	System / infrastructure tables	`REF_SYSTEM`, `REF_LICENSE`, `REF_ERRORLOG`
`AuditTrail_`	Audit logging	`AuditTrail_UserAccess`

Header/detail suffixes on document tables:

Suffix	Meaning
`_H`	Header — one row per document
`_D`	Detail — line items
`_G`	Per-gudang / rack breakdown (`tbTr_StockOpname_G`)
`_Daily`	Daily-partitioned copy (`tbTr_Penjualan_H_Daily`)

Column prefixes — each table tags every column: PRD_ (Barang), ST_ (Stock), BOH_/BOD_ (BackOffice header/detail), JLH_/JLD_ (Penjualan/sales), SUP_ (Supplier), CUST_ (Customer), GDG_ (Cabang/branch), LOK_ (Lokasi), OPH_/OPD_ (Opname), PRSH_ (Perusahaan), PRM_ (Parameter). Nearly every table also ends with an <prefix>_IDTRANS column used for sync/replication.

Multi-tenancy: everything is branch-scoped¶

flowchart LR
    A["Almost every primary key<br/>starts with a branch code"] --> B["*_KODECABANG<br/>(or GDG_KODE / PRSH_KODE)"]
    B --> C["Data is partitioned by branch<br/>within one database"]
    style B fill:#fff3e0,stroke:#fb8c00

Nearly every table's PK begins with a branch column (*_KODECABANG). The system is effectively multi-tenant by branch inside a single database.

The domain in an entity map¶

erDiagram
    tbMaster_Barang ||--o{ tbMaster_Barcode : "has many"
    tbMaster_Barang }o--|| tbMaster_Satuan : "unit"
    tbMaster_Barang }o--|| tbMaster_Merk : "brand"
    tbMaster_Barang }o--|| tbMaster_Supplier : "default supplier"
    tbMaster_Barang }o--|| tbMaster_Kat : "category"
    tbMaster_Kat }o--|| tbMaster_Dept : "department"
    tbMaster_Dept }o--|| tbMaster_Divisi : "division"
    tbMaster_Barang ||--o{ tbMaster_Stock : "stock per branch+location"
    tbMaster_Stock }o--|| tbMaster_Cabang : "branch"
    tbMaster_Stock ||--o{ tbHist_StockGudang : "snapshots"
    tbTr_BackOffice_H ||--o{ tbTr_BackOffice_D : "header/detail"
    tbTr_BackOffice_D }o--|| tbMaster_Barang : "item"
    tbMaster_User ||--o{ tbMaster_UserAccess : "grants"
    tbMaster_Access ||--o{ tbMaster_UserAccess : "defines"

Table catalogue by domain¶

Master data¶

Table	PK	Purpose
`tbMaster_Barang`	`PRD_PRDCD` (7-char)	Item master — name, unit, category, brand, supplier, prices, on-hand, tax flags.
`tbMaster_Barcode`	`BRC_BARCODE`	Barcode → item (many barcodes per item).
`tbMaster_Satuan`	`SAT_KODESAT`	Units of measure + conversion.
`tbMaster_Merk`	`MERK_KODEMERK`	Brands.
`tbMaster_Divisi` / `_Dept` / `_Kat`	codes	3-level item hierarchy Division→Department→Category.
`tbMaster_Supplier`	`SUP_KODESUP` (+branch)	Vendors — terms, tax, running payable balance.
`tbMaster_Customer`	`CUST_KODECUST` (+branch)	Customers — credit limit, terms, running receivable.
`tbMaster_Cabang`	`GDG_KODE`	Branches/stores (a branch is the gudang). Sync/FTP/SMTP settings.
`tbMaster_Lokasi`	rack keys	Physical storage locations (rack/shelf). Also `tbMaster_Rak`, `_SubRak`, `_Shelving`.
`tbMaster_User`	`USERID`	App users (password, level, station, last login).
`tbMaster_Access`	`AccessGroup`+`AccessCode`	Menu / permission definitions (`Baca/Tambah/Koreksi/Hapus`, `URL`).
`tbMaster_UserAccess`	`UserID`+access	Per-user permission grants.
`tbMaster_NomorDoc`	counter key	Document-number counters (for `GetNomor`).

Other masters: tbMaster_Bank, _Sales, _Karyawan, _Tag, _Wilayah, _JatuhTempo, _HargaJual/_HargaBeli/_HargaGrosir, _MinMaxStock, _Promosi_H/D, _Voucher.

Stock¶

Table	PK	Purpose
`tbMaster_Stock`	`ST_KODECABANG`+`ST_LOKASI`+`ST_PRDCD`	Perpetual stock — the accumulator bucket columns + valuation (`ST_AVGCOST`).
`tbMaster_StockGudang`	`STG_KODECABANG`+`STG_KODELOK`+`STG_PRDCD`	Stock per warehouse location.
`tbHist_StockGudang`	adds `STG_PERIODE`	Periodic (`yyyyMM`) stock snapshot.

The stock model gets its own page — see Stock & Costing.

Transactions¶

Table(s)	Keys	Purpose
`tbTr_BoTrans_H/_D`	branch+`NODOC`(+`NOURUT`)	Draft/staging back-office document (being entered).
`tbTr_BackOffice_H/_D`	branch+`NODOC`(+`NOURUT`)	Posted back-office document; `BOH_TIPEDOC` discriminates the type.
`tbTr_StockOpname_H/_D/_G`	branch+`NOLKSO`(+`NOURUT`)	Physical stock count (own tables, `OPH_/OPD_`).
`tbTr_MutasiGudang_H/_D`	branch+`NODOC`	Inter-warehouse transfers.
`tbTr_PurchaseOrder_H/_D`	branch+`NOPO`	Purchase orders.
`tbTr_Penjualan_H/_D`	branch+date+station+shift+`NOSTRUK`	POS sales (the widest key).

Others follow the same _H/_D shape: tbTr_PermintaanBarang, _SalesOrder, _Packing, _Sortir, _TitipBarang, _HomeDelivery, _BayarPiutang, _Service. See Transactions & Posting.

Configuration¶

Table	Purpose
`PERUSAHAAN` (`PRSH_`)	Company/branch config — active date, open period, closing flag, receipt layout, tax, sync. The `cPRSH` object.
`tbTabel_Parameter` (`PRM_`)	Per-branch UI/behaviour parameters. The `cPARAM`/`cPRM` objects.
`tbMaster_Computer`	Per-workstation config (printer, active user, keyed by IP+station). The `cCOMP` object.
`REF_SYSTEM`	Name/value system properties (incl. licence expiry).
`AuditTrail_UserAccess`	Audit log of logins and menu/record actions.

Primary-key patterns at a glance¶

Item code PRD_PRDCD (7-char) propagates everywhere as *_PRDCD.
Branch scoping — PKs start with *_KODECABANG / GDG_KODE / PRSH_KODE.
Perpetual stock — ST_KODECABANG+ST_LOKASI+ST_PRDCD.
Documents — header *_KODECABANG+<docno>; detail adds *_NOURUT line sequence.
Sales header is widest — branch+date+station+shift+receipt no.

Need every column?

The Data Dictionary lists every column, primary key, and meaning for the core tables across all domains.