← 개발일지

Designing a Non-Business Day Table for Oracle

oracledatabase-designbusiness-day-calculationtable-designholiday-api

ℹ️ This post was written with the assistance of Claude (Anthropic). The table design was discussed and refined through a conversation with Claude, and this post documents the process and decisions.

The Problem

Any domain that deals with settlements, logistics, or financial processing eventually needs to answer: "What date is N business days from today?" You can't just add N to the calendar date — weekends and public holidays need to be excluded. Hardcoding holiday logic is a maintenance nightmare that breaks silently every year. The sustainable approach is a non-business day master table that you query with a simple NOT EXISTS.

In countries like South Korea, the government provides a public holiday API (via the Open Data Portal) that returns the year's holidays as XML. You parse it in a batch job and INSERT into your table. But the table design itself has a few decisions that aren't obvious until you've been burned.

Three Decisions That Shape Everything

Should weekends go in the table?

You have two options.

Option A — Include weekends in the table. Your business day check becomes a single NOT EXISTS query. No day-of-week logic anywhere. About 130 rows per year (104 weekend days + ~20 holidays). Trivial data volume.

Option B — Handle weekends in application logic. The table stays small (~20 rows), but every business day calculation needs day-of-week checks plus a table lookup. Two concerns tangled together.

Option A wins. 130 rows per year is nothing, and you get a critical bonus: exception handling becomes trivial. If Saturday is a mandatory work day, you flip that row's USE_YN flag to 'N' and the calculation adjusts automatically. With Option B, you'd need a separate "business day exceptions" table.

How strict should your UNIQUE constraint be?

The naive approach is a UNIQUE index on the date column alone — one row per date. But the same date can appear for multiple reasons: a public holiday from the government API and a company-specific closure. If you enforce single-date uniqueness, you can't track both.

The solution is a composite UNIQUE on (date + data source). This lets you manage data independently per source — for example, you can purge and reload API data annually without touching manually registered entries.

Watch out for industry-specific holidays

Not every "holiday" in the government API is a non-business day for every industry. In South Korea, Labor Day (May 1) is a paid holiday under labor law but not a government office holiday. Financial institutions close; government offices don't. General companies vary. You need to either filter during the API import batch or import everything and selectively deactivate rows with the use flag.

Table Structure

Here's what the core columns look like.

Date column (VARCHAR2(8), YYYYMMDD): Stores the non-business day. Using a string instead of DATE is a common convention in Korean enterprise systems — string comparison works fine for range queries on this format.

Day type code: Distinguishes public holidays, substitute holidays, Saturdays, Sundays, and company closures. Manage these codes in a shared code table rather than hardcoding.

Data source code: Tracks whether the row came from a government API, manual entry, or a batch job (for weekends). Essential for selective data refresh.

Use flag: Defaults to 'Y'. Flip to 'N' for exceptions like mandatory work days. The business day query checks this flag, so deactivated rows are transparently excluded.

DDL

CREATE TABLE NON_BSNS_DAY (
    NON_BSNS_DAY_SQ    NUMBER            NOT NULL,
    BASE_DT             VARCHAR2(8)       NOT NULL,
    DAY_TP_CD           VARCHAR2(10)      NOT NULL,
    HOLDY_NM            VARCHAR2(100),
    DATA_SRC_CD         VARCHAR2(10)      NOT NULL,
    USE_YN              VARCHAR2(1)       DEFAULT 'Y' NOT NULL,
    -- audit columns omitted for brevity
    CONSTRAINT PK_NON_BSNS_DAY PRIMARY KEY (NON_BSNS_DAY_SQ)
);

-- Prevent duplicate entries for the same date + source
CREATE UNIQUE INDEX UK_NON_BSNS_DAY_01
    ON NON_BSNS_DAY (BASE_DT, DATA_SRC_CD);

CREATE SEQUENCE SQ_NON_BSNS_DAY START WITH 1 INCREMENT BY 1 NOCACHE;

One composite UNIQUE index is enough. Since BASE_DT is the leading column, date-based range scans are covered. At ~130 rows per year, adding more indexes just adds maintenance burden with zero performance benefit.

Business Day Check

SELECT CASE
    WHEN EXISTS (
        SELECT 1 FROM NON_BSNS_DAY
        WHERE BASE_DT = :target_dt AND USE_YN = 'Y'
    ) THEN 'N'
    ELSE 'Y'
END AS IS_BSNS_DAY
FROM DUAL;

No day-of-week logic. That simplicity is the entire payoff of including weekends in the table.

Operational Workflow

At the start of each year, run three batch jobs in sequence:

  1. Generate weekend rows — INSERT all Saturdays and Sundays with source code BAT.
  2. Import holidays from government API — Parse the XML response and INSERT with source code API.
  3. Manual entries — Admin registers company-specific closures with source code MAN.

When an exception occurs mid-year (e.g., mandatory Saturday work), update that row's use flag to 'N'.

Key Takeaway

The two decisions that cascade into everything else are: "Do weekends live in the table?" and "Can the same date have multiple entries?" Get those right, and your index strategy, query patterns, and operational procedures all fall into place naturally. For a low-volume master table like this, always optimize for logic simplicity over data minimalism.