# Database Standardization & Migration Plan (MDSonla)

Date: 2026-01-29

Scope: MSSQL database for current application features and future upgrades. This plan is based on the exported schema in mdsonla_schema.sql and project requirements in this repository.

## Goals

- Standardize table/column names, data types, and constraints.
- Add consistent auditing (who/when) and full soft delete/restore.
- Improve schema organization for long-term maintenance and scale.
- Migrate with minimal downtime while the system remains online.

## 1) Issues With Current DB

### Naming & organization
- **Non-standard naming**: mixture of `tbl_`, `sys_`, and inconsistent column naming (`Ten`, `TenVuTrong`, `Ma`, `ID`, `Id`).
- **No schema separation**: everything is under `dbo`, making logical grouping and permissions harder.
- **Pluralization inconsistency**: table names mix singular/plural and Vietnamese abbreviations.

### Data types & constraints
- **Inconsistent numeric types** for quantities (some `float`, some `int`, some `decimal`).
- **Date/time fields** often use `datetime` rather than `datetime2` and have inconsistent nullability.
- **Lack of explicit constraints**: missing `NOT NULL`, `CHECK`, `DEFAULT`, and `FOREIGN KEY` constraints in many tables.
- **No global uniqueness pattern** for codes (`Ma*`) and natural keys.

### Auditing & soft delete
- **No universal audit fields** (CreatedAt/By, UpdatedAt/By, DeletedAt/By).
- **No consistent soft-delete** pattern for all tables.
- **No change history** (row-level audit trail).

### Operational concerns
- **Compatibility level 100** and older database settings in the script may reduce modern feature usage.
- **Limited role-based separation** beyond default db roles.

## 2) Suggested Industry-Standard Design

### 2.1 Naming conventions
- **Schema-based grouping** (examples):
  - `core` (transactional data)
  - `ref` (reference/master data)
  - `auth` (users/roles)
  - `audit` (history and logs)
- **Table names**: PascalCase singular (e.g., `core.Parcel`, `ref.Season`, `ref.SeedVariety`).
- **Column names**: PascalCase; primary key column = `Id`.
- **Foreign keys**: `<ReferencedTable>Id` (e.g., `SeasonId`).
- **Booleans**: `IsActive`, `IsDeleted`, etc.

### 2.2 Standard columns (apply to all business tables)
- `Id` (INT IDENTITY or BIGINT IDENTITY)
- `CreatedAt` (datetime2(3), default SYSDATETIME())
- `CreatedBy` (nvarchar(100) or bigint FK to auth.User)
- `UpdatedAt` (datetime2(3), nullable)
- `UpdatedBy` (nvarchar(100) or bigint FK to auth.User, nullable)
- `DeletedAt` (datetime2(3), nullable)
- `DeletedBy` (nvarchar(100) or bigint FK to auth.User, nullable)
- `IsDeleted` (bit, default 0)
- Optional: `RowVersion` (rowversion) for optimistic concurrency

### 2.3 Audit/change tracking
Two recommended options:

**Option A: Audit tables**
- `audit.ChangeLog` with columns:
  - `Id`, `TableName`, `RecordId`, `Operation` (INSERT/UPDATE/DELETE),
  - `ChangedAt`, `ChangedBy`, `OldValues`, `NewValues` (JSON), `CorrelationId`
- Implemented via triggers or API-level logging.

**Option B: Temporal tables** (SQL Server System-Versioned Tables)
- Add `ValidFrom`, `ValidTo` to tables and enable SYSTEM_VERSIONING.

Recommendation: Use **Option A for business needs** (clear “who/when/what”) + optional temporal for select high-value tables.

### 2.4 Data types
- **Identifiers**: `BIGINT` for long-term growth.
- **Money/quantity**: `DECIMAL(p,s)` (e.g., `DECIMAL(18,2)` for currency, `DECIMAL(18,4)` for area/yield).
- **Text**: `NVARCHAR(n)` with explicit lengths (avoid `NVARCHAR(MAX)` unless needed).
- **Dates**: `DATE` for date-only; `DATETIME2(3)` for timestamp.
- **Enum-like fields**: use reference tables instead of numeric magic values.

### 2.5 Core schema mapping (example)
Current `tbl_ThuaRuong` → `core.Parcel`
- `ThonID` → `ThonId` (FK to `ref.Village`)
- `LoaiDatID` → `SoilTypeId` (FK to `ref.SoilType`)
- `GiongMiaID` → `SeedVarietyId` (FK to `ref.SeedVariety`)
- `KieuTrongID` → `PlantingMethodId` (FK to `ref.PlantingMethod`)
- `VuTrongID` → `SeasonId` (FK to `ref.Season`)

### 2.6 Soft delete strategy
- All tables include `IsDeleted`, `DeletedAt`, `DeletedBy`.
- Add **filtered indexes** on `IsDeleted = 0` for common queries.
- Update API queries to default to `IsDeleted = 0`.

### 2.7 Security & access
- Create SQL roles for API-only access:
  - `role_api_read`, `role_api_write`
- Enforce data access via stored procedures or parameterized queries.

## 3) Step-by-Step Migration While System is Running

### Phase 0 — Preparation (No downtime)
1. **Inventory current schema** from [Schema/database.sql](Schema/database.sql).
2. **Define target schema** using the new naming and column standards.
3. **Build a mapping document** (old table/column → new table/column).
4. **Create migration scripts** (DDL + data migration).

### Phase 1 — Add auditing & soft delete (Online)
1. Add columns to existing tables:
   - `CreatedAt`, `CreatedBy`, `UpdatedAt`, `UpdatedBy`, `DeletedAt`, `DeletedBy`, `IsDeleted`, `RowVersion`.
2. Set defaults for `CreatedAt` and `IsDeleted`.
3. Backfill `CreatedAt` using `GETDATE()` or best available timestamp.
4. Update API and WinForms queries to filter `IsDeleted = 0`.
5. Add `audit.ChangeLog` and implement triggers or application logging.

### Phase 2 — Introduce new schema (Online, parallel write)
1. Create new schemas: `core`, `ref`, `auth`, `audit`.
2. Create new standardized tables in parallel (e.g., `core.Parcel`).
3. Add **migration views** for compatibility:
   - Example view: `dbo.tbl_ThuaRuong` → `core.Parcel` (temporary).
4. Implement **dual-write** in API (write to old + new tables) or use triggers to keep both in sync.

### Phase 3 — Data backfill & validation (Online)
1. Run ETL jobs to copy and transform data to new tables.
2. Validate counts and key relationships.
3. Run application read queries against new views or tables in shadow mode.

### Phase 4 — Cutover (Short maintenance window)
1. Switch API to read/write only new schema.
2. Freeze old tables (read-only) for rollback window.
3. Monitor for errors and performance issues.

### Phase 5 — Cleanup & optimization
1. Remove old tables after acceptance period.
2. Add/verify indexes, foreign keys, and check constraints.
3. Enable more modern database settings (compatibility level, ANSI settings).

## Additional Recommendations

- **Use migration tooling**: Flyway or EF Core migrations for repeatable deployments.
- **Document each table**: add extended properties for descriptions.
- **API versioning**: expose `/v2` endpoints to support new schema during transition.
- **Monitoring**: create dashboards for audit and soft-delete activity.

## Deliverables

1. Target schema definition (DDL for new tables).
2. Mapping sheet old → new.
3. Migration scripts (phased).
4. Updated API queries to use standardized schema.

---

## 4) Proposed Standardized Schema Mapping (Initial Core Set)

This mapping focuses on the **core features currently used** in the app and the most referenced reference tables. It is the minimal set to stabilize naming, auditing, and soft delete.

### Core (transactional)
- `dbo.tbl_ThuaRuong` → `core.Parcel`
- `dbo.tbl_HopDong` → `core.Contract`

### Reference/master
- `dbo.tbl_VuTrong` → `ref.Season`
- `dbo.tbl_GiongMia` → `ref.SeedVariety`
- `dbo.tbl_KieuTrong` → `ref.PlantingMethod`
- `dbo.tbl_LoaiDat` → `ref.SoilType`
- `dbo.tbl_TramNongVu` → `ref.FieldStation`
- `dbo.tbl_DanhMucCanBoNongVu` → `ref.ExtensionOfficer`
- `dbo.tbl_Tinh` → `ref.Province`
- `dbo.tbl_Huyen` → `ref.District`
- `dbo.tbl_Xa` → `ref.Commune`
- `dbo.tbl_Thon` → `ref.Village`

### Auth
- `dbo.sys_User` → `auth.User`

---

## 5) Draft DDL (Core/Ref/Auth/Audit)

This is a **starter DDL** for the standardized schema. It can be expanded to include all remaining tables after the first migration phase.

### 5.1 Create schemas
```sql
CREATE SCHEMA core;
CREATE SCHEMA ref;
CREATE SCHEMA auth;
CREATE SCHEMA audit;
```

### 5.2 Audit log
```sql
CREATE TABLE audit.ChangeLog (
  Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  TableName SYSNAME NOT NULL,
  RecordId BIGINT NOT NULL,
  Operation NVARCHAR(10) NOT NULL, -- INSERT/UPDATE/DELETE
  ChangedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  ChangedBy NVARCHAR(100) NULL,
  OldValues NVARCHAR(MAX) NULL, -- JSON
  NewValues NVARCHAR(MAX) NULL, -- JSON
  CorrelationId UNIQUEIDENTIFIER NULL
);
```

### 5.3 Auth
```sql
CREATE TABLE auth.[User] (
  Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  UserName NVARCHAR(50) NOT NULL,
  PasswordHash VARBINARY(256) NOT NULL,
  FullName NVARCHAR(100) NULL,
  Department NVARCHAR(100) NULL,
  IsActive BIT NOT NULL DEFAULT 1,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0,
  RowVersion ROWVERSION
);
```

### 5.4 Reference tables (geography)
```sql
CREATE TABLE ref.Province (
  Id INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(100) NOT NULL,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0
);

CREATE TABLE ref.District (
  Id INT NOT NULL PRIMARY KEY,
  ProvinceId INT NOT NULL,
  Name NVARCHAR(100) NOT NULL,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0,
  CONSTRAINT FK_District_Province FOREIGN KEY (ProvinceId) REFERENCES ref.Province(Id)
);

CREATE TABLE ref.Commune (
  Id INT NOT NULL PRIMARY KEY,
  DistrictId INT NOT NULL,
  Name NVARCHAR(100) NOT NULL,
  Code NVARCHAR(10) NULL, -- from tbl_Xa.MaXa
  ClusterId INT NULL,     -- from tbl_Xa.CumID
  Quota DECIMAL(18,4) NULL, -- from tbl_Xa.DinhMuc
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0,
  CONSTRAINT FK_Commune_District FOREIGN KEY (DistrictId) REFERENCES ref.District(Id)
);

CREATE TABLE ref.Village (
  Id INT NOT NULL PRIMARY KEY,
  CommuneId INT NOT NULL,
  Name NVARCHAR(100) NOT NULL,
  Code NVARCHAR(5) NULL, -- from tbl_Thon.MaThon
  Quota DECIMAL(18,4) NULL, -- from tbl_Thon.DinhMuc
  OfficerId INT NULL, -- from tbl_Thon.CanBoNongVuID
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0,
  CONSTRAINT FK_Village_Commune FOREIGN KEY (CommuneId) REFERENCES ref.Commune(Id)
);
```

### 5.5 Reference tables (cultivation)
```sql
CREATE TABLE ref.SoilType (
  Id INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(100) NOT NULL,
  Note NVARCHAR(100) NULL,
  Priority INT NULL,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0
);

CREATE TABLE ref.SeedVariety (
  Id INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL,
  Note NVARCHAR(255) NULL,
  DaysToHarvest INT NULL,
  Priority INT NULL,
  Price DECIMAL(18,2) NULL,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0
);

CREATE TABLE ref.PlantingMethod (
  Id INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(200) NOT NULL,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0
);

CREATE TABLE ref.Season (
  Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Name NVARCHAR(20) NOT NULL,
  StartDate DATE NULL,
  EndDate DATE NULL,
  PreviousSeasonId INT NULL,
  IsActive BIT NOT NULL DEFAULT 1,
  IsDefault BIT NOT NULL DEFAULT 0,
  InterestRate DECIMAL(9,4) NULL,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0
);

CREATE TABLE ref.FieldStation (
  Id INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(100) NOT NULL,
  Code NVARCHAR(50) NULL,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0
);

CREATE TABLE ref.ExtensionOfficer (
  Id INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL,
  Code NVARCHAR(50) NULL,
  Phone NVARCHAR(12) NULL,
  IsActive BIT NOT NULL DEFAULT 1,
  VillageId INT NULL,
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0
);
```

### 5.6 Core tables
```sql
CREATE TABLE core.Contract (
  Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ContractCode NVARCHAR(100) NULL, -- tbl_HopDong.MaHopDong
  FullName NVARCHAR(100) NULL,     -- tbl_HopDong.HoTen
  BirthDate DATE NULL,             -- tbl_HopDong.NgaySinh
  NationalId NVARCHAR(50) NULL,    -- tbl_HopDong.SoCMT
  NationalIdIssuedDate DATE NULL,  -- tbl_HopDong.NgayCap
  NationalIdIssuedPlace NVARCHAR(50) NULL, -- tbl_HopDong.NoiCap
  VillageId INT NULL,              -- tbl_HopDong.ThonID
  Address NVARCHAR(200) NULL,      -- tbl_HopDong.Diachi
  BankAccount NVARCHAR(50) NULL,   -- tbl_HopDong.SoTaiKhoan
  BankId INT NULL,                 -- tbl_HopDong.NganHangID
  Status INT NULL,                 -- tbl_HopDong.TrangThai
  Note NVARCHAR(500) NULL,         -- tbl_HopDong.NoteModify/GhiChu
  SignedDate DATE NULL,            -- tbl_HopDong.NgayKyHopDong
  OfficerId INT NULL,              -- tbl_HopDong.CanBoNongVuID
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0,
  RowVersion ROWVERSION
);

CREATE TABLE core.Parcel (
  Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SurveyNo NVARCHAR(50) NULL,      -- SoBanDieuTra
  Code NVARCHAR(50) NULL,          -- MaThuaRuong
  Name NVARCHAR(200) NULL,         -- SoHieuKeUoc, fallback to generated/Code
  ContractId BIGINT NULL,          -- HopDongID
  VillageId INT NULL,              -- ThonID
  SoilTypeId INT NULL,             -- LoaiDatID
  FieldStationId INT NULL,         -- TramNongVuID
  TransportRouteId INT NULL,       -- DuongVanChuyenID
  RoadCondition INT NULL,          -- HienTrangGiaoThong
  Area DECIMAL(18,4) NULL,         -- DienTich
  PlantingMethodId INT NULL,       -- KieuTrongID
  SeedVarietyId INT NULL,          -- GiongMiaID
  SeasonId INT NULL,               -- VuTrongID
  CropPurposeId INT NULL,          -- MucDichID
  FallowReasonId INT NULL,         -- PheCanhID
  PlantDate DATE NULL,             -- NgayTrong
  ExpectedHarvestDate DATE NULL,   -- NgayThuHoachDuKien
  ExpectedYield DECIMAL(18,4) NULL,    -- NangSuatDuKien
  ExpectedOutput DECIMAL(18,4) NULL,   -- SanLuongDuKien
  ExpectedYield2 DECIMAL(18,4) NULL,   -- NangSuatDuKien1
  ExpectedOutput2 DECIMAL(18,4) NULL,  -- SanLuongDuKien1
  FallowArea DECIMAL(18,4) NULL,       -- DienTichPheCanh
  SeedCutArea DECIMAL(18,4) NULL,      -- DienTichChatGiong
  SeedCutOutput DECIMAL(18,4) NULL,    -- SanLuongChatGiong
  SeedCutYield DECIMAL(18,4) NULL,     -- NangSuatChatGiong
  SeedCutExpectedArea DECIMAL(18,4) NULL, -- DienTichDuKienChatGiong
  RegistrationStatus INT NULL,         -- TrangThaiDangKy
  RegisterDate DATE NULL,              -- ThoiGianDangKy
  RegisterArea DECIMAL(18,4) NULL,     -- DienTichDangKy
  RegisterYield DECIMAL(18,4) NULL,    -- NangSuatDangKy
  RegisterOutput DECIMAL(18,4) NULL,   -- SanLuongDangKy
  RegisterSoilTypeId INT NULL,         -- LoaiDatDangKyID
  RegisterSeedVarietyId INT NULL,      -- GiongMiaDangKyID
  RegisterPlantingMethodId INT NULL,   -- KieuTrongDangKyID
  RegisterAreaGroup NVARCHAR(50) NULL, -- XuDongDangKy
  AreaGroup NVARCHAR(50) NULL,         -- XuDong
  Status INT NULL,                     -- TrangThai
  Condition INT NULL,                  -- TinhTrang
  VoucherNo INT NULL,                  -- SoPhieuChat
  OfficerId INT NULL,                  -- CanBoNongVuID
  Note NVARCHAR(500) NULL,             -- NoteModify/NguyenNhanphecanh
  CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
  CreatedBy NVARCHAR(100) NULL,
  UpdatedAt DATETIME2(3) NULL,
  UpdatedBy NVARCHAR(100) NULL,
  DeletedAt DATETIME2(3) NULL,
  DeletedBy NVARCHAR(100) NULL,
  IsDeleted BIT NOT NULL DEFAULT 0,
  RowVersion ROWVERSION
);
```

---

If you want, I can continue the mapping and DDL for the remaining tables (DauTu, HoTro, NhapMia, ThanhToan, VatTu, etc.) and generate ready-to-run migration scripts.

---

## 6) Full Table Mapping (All Tables in Schema)

**Legend for new schemas**
- `core`: transactional/business data
- `ref`: reference/master data
- `auth`: identity/roles/permissions
- `audit`: logs/history
- `stage`: import/staging/temp
- `archive`: backups/snapshots/legacy copies
- `system`: SQL Server/system tables

> Note: Any table marked **Review** needs business validation to confirm correct classification.

| Old Table | New Schema | New Table | Notes |
|---|---|---|---|
| tbl_UngVatTuVanChuyen | core | UngVatTuVanChuyen |  |
| tbl_NhapMia | core | NhapMia |  |
| tbl_XeVanChuyen | core | XeVanChuyen |  |
| tbl_HopDongVanChuyen | core | HopDongVanChuyen |  |
| tbl_Xa | ref | Commune | standardize geo | 
| tbl_Thon | ref | Village | standardize geo | 
| tbl_HopDong | core | Contract |  |
| tbl_Huyen | ref | District | standardize geo | 
| tbl_Cum | ref | Cluster | standardize geo | 
| tbl_RaiVu | ref | RaiVu | Review (lookup) |
| tbl_GiongMia | ref | SeedVariety |  |
| tbl_ThuaRuong | core | Parcel |  |
| tbl_LoaiDat | ref | SoilType |  |
| tbl_DotThanhToan | core | DotThanhToan |  |
| tbl_DanhMucCanBoNongVu | ref | ExtensionOfficer |  |
| sys_User | auth | User | replace password with hash |
| tbl_NganHang | ref | Bank |  |
| tbl_ThanhToanMia | core | ThanhToanMia |  |
| tbl_BaiTapKet | core | BaiTapKet |  |
| tbl_VuTrong | ref | Season |  |
| tbl_DauTu | core | DauTu |  |
| tbl_LenhChatMia | core | LenhChatMia |  |
| tbl_HopDongVuTrong | core | HopDongVuTrong |  |
| tbl_ThanhToanMia_TruNoDauTu | core | ThanhToanMiaTruNoDauTu |  |
| tbl_NhapMia_Giong | core | NhapMiaGiong |  |
| tbl_VatTuVanChuyen | core | VatTuVanChuyen |  |
| tbl_HoTro | core | HoTro |  |
| tbl_DanhMucDauTu | ref | DanhMucDauTu |  |
| tbl_DanhMucHoTro | ref | DanhMucHoTro |  |
| tbl_DauTu_TruNo | core | DauTuTruNo |  |
| tbl_Tinh | ref | Province | standardize geo | 
| tbl_DanhMucVatTu | ref | DanhMucVatTu |  |
| tbl_XuatVatTu | core | XuatVatTu |  |
| tbl_KieuTrong | ref | PlantingMethod |  |
| tbl_LoaiXeUuTien | ref | LoaiXeUuTien |  |
| tbl_TinhTrangThuaRuong | ref | TinhTrangThuaRuong |  |
| tbl_TramNongVu | ref | FieldStation |  |
| tbl_MucDichTrong | ref | CropPurpose |  |
| tbl_HopDong_DaLamThanhToan | core | HopDongDaLamThanhToan |  |
| tbl_NoCuChuHopDong | core | NoCuChuHopDong |  |
| tbl_BaiTapKet_GiaCuoc | core | BaiTapKetGiaCuoc |  |
| tbl_HoTro_TTDoiTru | core | HoTroTTDoiTru |  |
| tbl_HoTro_ChiTiet_TienLai | core | HoTroChiTietTienLai |  |
| tbl_HangHoa | ref | HangHoa |  |
| tbl_CanVatTu | core | CanVatTu |  |
| tbl_KhachHang | core | KhachHang |  |
| tbl_BaiTapKet_GiaCuocVatTu | core | BaiTapKetGiaCuocVatTu |  |
| tbl_NoiTamUngVatTu | core | NoiTamUngVatTu |  |
| tbl_HoTro_TruLai | core | HoTroTruLai |  |
| tbl_HoTro_ChietTinhCongNo | core | HoTroChietTinhCongNo |  |
| tbl_CCS | core | CCS |  |
| tbl_KhoanTienCo_TruNo | core | KhoanTienCoTruNo |  |
| tbl_NhanVatTu | core | NhanVatTu |  |
| tbl_ThanhToanVanChuyen | core | ThanhToanVanChuyen |  |
| tbl_ThuaRuong1 | archive | ThuaRuong_Archive1 | Review |
| tbl_LenhChatMia_Master | core | LenhChatMiaMaster |  |
| tbl_TrangThaiDuyetThuaRuong | ref | TrangThaiDuyetThuaRuong |  |
| tbl_DoiChieuCongNo | core | DoiChieuCongNo |  |
| tbl_HoTroTien | core | HoTroTien |  |
| tbl_GiaNhapMia | ref | GiaNhapMia |  |
| tbl_GiaMiaChay | ref | GiaMiaChay |  |
| tbl_DauTu_DuNo | core | DauTuDuNo |  |
| tbl_PheCanh | ref | PheCanh |  |
| 1Ex_VNL | stage | ExVNL | Review |
| abc | stage | Abc | Review |
| HDNEWS | archive | HdNews | Review |
| hodong_Backup | archive | HopDongBackup | Review |
| KCS_EpMia | core | KcsEpMia | Quality/KCS |
| KCS_NghiemThuMia | core | KcsNghiemThuMia | Quality/KCS |
| KCS_PhanTich | core | KcsPhanTich | Quality/KCS |
| KCS_TinhTrangMia | core | KcsTinhTrangMia | Quality/KCS |
| lam_Version | system | LamVersion | Review |
| sys_Chucnang | auth | ChucNang | permissions |
| sys_Controls | auth | Controls | permissions |
| sys_HopDong_log | audit | HopDongLog |  |
| sys_Log | audit | SystemLog |  |
| sys_MaPhieu | auth | MaPhieu | Review |
| sys_Roles | auth | Roles |  |
| sys_Roles_User_Cum | auth | RolesUserCum |  |
| sys_Roles_User_Cum1 | archive | RolesUserCum_Archive1 | Review |
| sys_ThongTyCongTy | ref | ThongTinCongTy |  |
| sys_UserRoles | auth | UserRoles |  |
| sys_Version | system | SystemVersion |  |
| sysdiagrams | system | SysDiagrams |  |
| T_DM_KH | stage | TdmKh | Review |
| T_DM_KMP | stage | TdmKmp | Review |
| T_VNL_CNBD | stage | TvnlCnbd | Review |
| T_VNL_CNBD_DAUTU | stage | TvnlCnbdDauTu | Review |
| tbl_ApDung | ref | ApDung | Review |
| tbl_BaiTapKet_GiaCuoc_log | audit | BaiTapKetGiaCuocLog |  |
| tbl_BaiTapKet_GiaCuoc1 | archive | BaiTapKetGiaCuoc_Archive1 |  |
| tbl_BaiTapKet_log | audit | BaiTapKetLog |  |
| tbl_BaiTapKet1 | archive | BaiTapKet_Archive1 |  |
| tbl_BaiTapKetVatTu | core | BaiTapKetVatTu |  |
| tbl_Brix_g_CCS | ref | BrixGccs | Review |
| tbl_CacVungNguyenLieu | ref | CacVungNguyenLieu |  |
| tbl_CacVungNguyenLieu_CBDB | ref | CacVungNguyenLieuCbdb |  |
| tbl_CacVungNguyenLieu_CBDB_log | audit | CacVungNguyenLieuCbdbLog |  |
| tbl_CacVungNguyenLieu_log | audit | CacVungNguyenLieuLog |  |
| tbl_CanVatTu_log | audit | CanVatTuLog |  |
| tbl_ccs_err | audit | CcsError |  |
| tbl_CCS_HeSo | ref | CcsHeSo |  |
| tbl_CCS_HST_Config | ref | CcsHstConfig |  |
| tbl_CCS_log | audit | CcsLog |  |
| tbl_CCS1 | archive | Ccs_Archive1 |  |
| tbl_CCS2 | archive | Ccs_Archive2 |  |
| tbl_Cum_log | audit | ClusterLog |  |
| tbl_DangKy_DauTu | core | DangKyDauTu |  |
| tbl_DangKy_DienTich | core | DangKyDienTich |  |
| tbl_DanhMucCanBoNongVu_log | audit | ExtensionOfficerLog |  |
| tbl_DanhMucCanBoNongVu1 | archive | ExtensionOfficer_Archive1 |  |
| tbl_DanhMucCanBoNongVu2 | archive | ExtensionOfficer_Archive2 |  |
| tbl_DanhMucDauTu_log | audit | DanhMucDauTuLog |  |
| tbl_DanhMucDauTu_VuTrong | ref | DanhMucDauTuVuTrong |  |
| tbl_DanhMucDauTu_VuTrong_log | audit | DanhMucDauTuVuTrongLog |  |
| tbl_DanhMucDT_DonViCU | ref | DanhMucDtDonViCu | Review |
| tbl_DanhMucDT_DonViCU_log | audit | DanhMucDtDonViCuLog |  |
| tbl_DanhMucHoTro_log | audit | DanhMucHoTroLog |  |
| tbl_DanhMucHoTro_TinhTheo | ref | DanhMucHoTroTinhTheo |  |
| tbl_DanhMucVatTu_log | audit | DanhMucVatTuLog |  |
| tbl_DauTu_bk20181106 | archive | DauTu_Backup_20181106 |  |
| tbl_DauTu_Chot_Vu15_17_2017-08-03 11:49:52 | archive | DauTu_Chot_Vu15_17_2017_08_03_114952 |  |
| tbl_DauTu_Chot_Vu17_18_2018-09-13 16:56:52 | archive | DauTu_Chot_Vu17_18_2018_09_13_165652 |  |
| tbl_DauTu_Chot_Vu17_18_2018-09-24 09:56:01 | archive | DauTu_Chot_Vu17_18_2018_09_24_095601 |  |
| tbl_DauTu_Chot_Vu17_18_2018-09-24 13:35:59 | archive | DauTu_Chot_Vu17_18_2018_09_24_133559 |  |
| tbl_DauTu_Chot_Vu18_19_2019-10-08 17:16:20 | archive | DauTu_Chot_Vu18_19_2019_10_08_171620 |  |
| tbl_DauTu_Chot_Vu19_20_2020-09-21 10:36:02 | archive | DauTu_Chot_Vu19_20_2020_09_21_103602 |  |
| tbl_DauTu_Chot_Vu19_21_2020-08-04 16:01:04 | archive | DauTu_Chot_Vu19_21_2020_08_04_160104 |  |
| tbl_DauTu_Chot_Vu20_21_2021-07-11 03:25:59 | archive | DauTu_Chot_Vu20_21_2021_07_11_032559 |  |
| tbl_DauTu_Chot_Vu20_21_2021-07-13 23:40:10 | archive | DauTu_Chot_Vu20_21_2021_07_13_234010 |  |
| tbl_DauTu_Chot_Vu20_21_2021-07-13 23:46:25 | archive | DauTu_Chot_Vu20_21_2021_07_13_234625 |  |
| tbl_DauTu_Chot_Vu21_22_2022-07-06 14:00:02 | archive | DauTu_Chot_Vu21_22_2022_07_06_140002 |  |
| tbl_DauTu_Chot_Vu21_22_2022-07-07 10:31:49 | archive | DauTu_Chot_Vu21_22_2022_07_07_103149 |  |
| tbl_DauTu_Chot_Vu22_23_2023-07-02 07:38:02 | archive | DauTu_Chot_Vu22_23_2023_07_02_073802 |  |
| tbl_DauTu_Chot_Vu23_24_2024-11-05 15:27:09 | archive | DauTu_Chot_Vu23_24_2024_11_05_152709 |  |
| tbl_DauTu_Chot_Vu23_24_2024-11-20 14:20:40 | archive | DauTu_Chot_Vu23_24_2024_11_20_142040 |  |
| tbl_DauTu_Chot_Vu24_25_2025-08-05 14:24:35 | archive | DauTu_Chot_Vu24_25_2025_08_05_142435 |  |
| tbl_DauTu_giong | core | DauTuGiong |  |
| tbl_DauTu_log | audit | DauTuLog |  |
| tbl_DauTu_phanbon | core | DauTuPhanBon |  |
| tbl_DauTu_Update_Delete | audit | DauTuUpdateDelete |  |
| tbl_DauTu1 | archive | DauTu_Archive1 |  |
| tbl_DinhMucXa | ref | DinhMucXa |  |
| tbl_DinhMucXa_log | audit | DinhMucXaLog |  |
| tbl_DoiChieuCongNo_ChiTiet | core | DoiChieuCongNoChiTiet |  |
| tbl_DoiChieuCongNo_log | audit | DoiChieuCongNoLog |  |
| tbl_DonViCungUngVatTu | ref | DonViCungUngVatTu |  |
| tbl_DotThanhToan_log | audit | DotThanhToanLog |  |
| tbl_DotTTVanChuyen | core | DotTtVanChuyen |  |
| tbl_DuNo | core | DuNo |  |
| tbl_GiaMiaChay_log | audit | GiaMiaChayLog |  |
| tbl_GiaNhapMia_log | audit | GiaNhapMiaLog |  |
| tbl_GiaVanChuyen | ref | GiaVanChuyen |  |
| tbl_GiaVatTu | ref | GiaVatTu |  |
| tbl_GiaVatTu_log | audit | GiaVatTuLog |  |
| tbl_GiongMia_log | audit | SeedVarietyLog |  |
| tbl_HienTrangGiaoThong | ref | HienTrangGiaoThong |  |
| tbl_HinhThucDauTu | ref | HinhThucDauTu |  |
| tbl_HoanUngVanChuyen | core | HoanUngVanChuyen |  |
| tbl_HoanUngVanChuyen_log | audit | HoanUngVanChuyenLog |  |
| tbl_HopDong_ChoLamThanhToan | core | HopDongChoLamThanhToan |  |
| tbl_HopDong_ChoLamThanhToan_log | audit | HopDongChoLamThanhToanLog |  |
| tbl_HopDong_DaLamThanhToan_log | audit | HopDongDaLamThanhToanLog |  |
| tbl_HopDong_log | audit | HopDongLog |  |
| tbl_HopDongTemp | stage | HopDongTemp |  |
| tbl_HopDongVanChuyen_log | audit | HopDongVanChuyenLog |  |
| tbl_HopDongVuTrong_log | audit | HopDongVuTrongLog |  |
| tbl_HopDongVuTrong1 | archive | HopDongVuTrong_Archive1 |  |
| tbl_HoTro_ChietTinhCongNo_log | audit | HoTroChietTinhCongNoLog |  |
| tbl_HoTro_HuyChietTinh | core | HoTroHuyChietTinh |  |
| tbl_HoTro_log | audit | HoTroLog |  |
| tbl_HoTro_SoTienConDu | core | HoTroSoTienConDu |  |
| tbl_HoTro_TruLai_log | audit | HoTroTruLaiLog |  |
| tbl_HoTroTheoLoaiHinh | ref | HoTroTheoLoaiHinh |  |
| tbl_HoTroTheoLoaiHinh_log | audit | HoTroTheoLoaiHinhLog |  |
| tbl_HoTroTien_log | audit | HoTroTienLog |  |
| tbl_HoTroVanChuyen | core | HoTroVanChuyen |  |
| tbl_Huyen_log | audit | DistrictLog |  |
| tbl_KeHoachSanLuong | core | KeHoachSanLuong |  |
| tbl_KeHoachSanLuong_ChiTiet | core | KeHoachSanLuongChiTiet |  |
| tbl_KeHoachSanLuong_ChiTiet_log | audit | KeHoachSanLuongChiTietLog |  |
| tbl_KeHoachSanLuong_log | audit | KeHoachSanLuongLog |  |
| tbl_LenhChatMia_log | audit | LenhChatMiaLog |  |
| tbl_LenhChatMia1 | archive | LenhChatMia_Archive1 |  |
| tbl_LoaiDat_log | audit | SoilTypeLog |  |
| tbl_LoaiHinhDauTu | ref | LoaiHinhDauTu |  |
| tbl_LuongMiaKeHoach | core | LuongMiaKeHoach |  |
| tbl_LuongMiaKeHoach_log | audit | LuongMiaKeHoachLog |  |
| tbl_MucDichTrong_log | audit | CropPurposeLog |  |
| tbl_NgayMia | core | NgayMia |  |
| tbl_NgayMia_log | audit | NgayMiaLog |  |
| tbl_NgayTinhCongNo | core | NgayTinhCongNo |  |
| tbl_NhanVatTu_log | audit | NhanVatTuLog |  |
| tbl_NhanVatTuThon | core | NhanVatTuThon |  |
| tbl_NhanVatTuThon_log | audit | NhanVatTuThonLog |  |
| tbl_NhapMia_Giong_log | audit | NhapMiaGiongLog |  |
| tbl_NhapMia_log | audit | NhapMiaLog |  |
| tbl_NhapMia_SoTienConDu | core | NhapMiaSoTienConDu |  |
| tbl_NhapMia1 | archive | NhapMia_Archive1 |  |
| tbl_NhapTienTraNo | core | NhapTienTraNo |  |
| tbl_NhapTienTraNo_log | audit | NhapTienTraNoLog |  |
| tbl_NhapTienTraNo_SoTienConDu | core | NhapTienTraNoSoTienConDu |  |
| tbl_NoCuChuHopDong_log | audit | NoCuChuHopDongLog |  |
| tbl_NoiDungChamSoc | ref | NoiDungChamSoc |  |
| tbl_NoiDungChamSoc_log | audit | NoiDungChamSocLog |  |
| tbl_NoTienMuaVatTuCuaCongTy | core | NoTienMuaVatTuCuaCongTy |  |
| tbl_PhanLoaiHangHoa | ref | PhanLoaiHangHoa |  |
| tbl_PhanTramGiaVanChuyen | ref | PhanTramGiaVanChuyen |  |
| tbl_PhanTramGiaVanChuyen_log | audit | PhanTramGiaVanChuyenLog |  |
| tbl_PheCanh_log | audit | PheCanhLog |  |
| tbl_QuanLyVaKhauHao | core | QuanLyVaKhauHao |  |
| tbl_QuanLyVaKhauHao_log | audit | QuanLyVaKhauHaoLog |  |
| tbl_RaiVu_log | audit | RaiVuLog |  |
| tbl_RaTioCCS | ref | RaTioCcs | Review |
| tbl_Temp | stage | Temp |  |
| tbl_Temp_Import_Excel_CapVatTu | stage | TempImportExcelCapVatTu |  |
| tbl_Temp_Import_Excel_CapVatTu_log | audit | TempImportExcelCapVatTuLog |  |
| tbl_Temp_Import_Excel_NhapDienTich | stage | TempImportExcelNhapDienTich |  |
| tbl_Temp_Import_Excel_NhapDienTich_log | audit | TempImportExcelNhapDienTichLog |  |
| tbl_Temp_Import_Excel_NhapSanLuong | stage | TempImportExcelNhapSanLuong |  |
| tbl_ThanhToanMia_bak_0302 | archive | ThanhToanMia_Backup_0302 |  |
| tbl_ThanhToanMia_LOG | audit | ThanhToanMiaLog |  |
| tbl_ThanhToanMia_TheoNhieuDot | core | ThanhToanMiaTheoNhieuDot |  |
| tbl_ThanhToanMia_TheoNhieuDot_log | audit | ThanhToanMiaTheoNhieuDotLog |  |
| tbl_ThanhToanMia_TheoNhieuDotbk | archive | ThanhToanMiaTheoNhieuDot_Backup |  |
| tbl_Thon_log | audit | VillageLog |  |
| tbl_ThonNangSuatDuKienApDung | ref | ThonNangSuatDuKienApDung |  |
| tbl_ThonNangSuatDuKienApDung_log | audit | ThonNangSuatDuKienApDungLog |  |
| tbl_ThuaRuong_log | audit | ParcelLog |  |
| tbl_TienBangChu_Hopdong | core | TienBangChuHopDong |  |
| tbl_TienPhatHopDongTrongMia | core | TienPhatHopDongTrongMia |  |
| tbl_TienPhatHopDongVanChuyen | core | TienPhatHopDongVanChuyen |  |
| tbl_Tinh_log | audit | ProvinceLog |  |
| tbl_TramNongVu_log | audit | FieldStationLog |  |
| tbl_TraNoVuTruoc | core | TraNoVuTruoc |  |
| tbl_TraNoVuTruoc_log | audit | TraNoVuTruocLog |  |
| tbl_TruNo_DauTu | core | TruNoDauTu |  |
| tbl_TruNo_DauTu_log | audit | TruNoDauTuLog |  |
| tbl_TyLeMiaXo | ref | TyLeMiaXo |  |
| tbl_UngTienMia | core | UngTienMia |  |
| tbl_UngVatTuVanChuyen_log | audit | UngVatTuVanChuyenLog |  |
| tbl_VatTu | ref | VatTu |  |
| tbl_VatTuVanChuyen_log | audit | VatTuVanChuyenLog |  |
| tbl_VuTrong_log | audit | SeasonLog |  |
| tbl_Xa_log | audit | CommuneLog |  |
| tbl_XeVanChuyen_log | audit | XeVanChuyenLog |  |
| tbl_XuatVatTu_log | audit | XuatVatTuLog |  |
| temmp_hd_vc | stage | TempHdVc | Review |
| temp_HD_BaiTapKet | stage | TempHdBaiTapKet | Review |
| TG_LinkingObject | auth | LinkingObject | Review |
| TG_NewID | auth | NewId | Review |
| TG_Objects | auth | Objects | Review |
| TG_ObjectType | auth | ObjectType | Review |
| TG_Permissions | auth | Permissions | Review |
| TG_ReturnID | auth | ReturnId | Review |
| TG_VisitNumber | auth | VisitNumber | Review |
| TLB_CCS_ERR | audit | CcsErrorLegacy |  |
| V2016_PhieuTT_Huy | archive | V2016PhieuTtHuy |  |
| ##DSTemp_HopDongKhongCheTram | stage | DsTempHopDongKhongCheTram | temp table |
| ##DSTemp_HopDongKhongCheTram (dup) | stage | DsTempHopDongKhongCheTram | temp table |
| ##DSTemp_HopDongKhongCheCHD | stage | DsTempHopDongKhongCheChd | temp table |
| ##DSTemp_TongHopKhongChe | stage | DsTempTongHopKhongChe | temp table |
| ##DSTemp_HopDongDienTichTong | stage | DsTempHopDongDienTichTong | temp table |
| ##DSTemp_TongThonDienTich | stage | DsTempTongThonDienTich | temp table |
| ##DSTemp_HopDongNhapMiaKhongChe | stage | DsTempHopDongNhapMiaKhongChe | temp table |
| ##DSTemp_HopDongNhapMia | stage | DsTempHopDongNhapMia | temp table |
| ##DSTemp_HopDongNhapMiaGiong | stage | DsTempHopDongNhapMiaGiong | temp table |


---

If you want, I can generate the first draft of the target schema for the core tables (Parcel, Contract, Season, SeedVariety, SoilType, Village, etc.) based on current table definitions.