Why You Start by Fixing Column Names

Summary

The reason to decide column names first is not aesthetics: without a standard, columns with the same meaning end up in three versions with different names and lengths, and you pay for it in a migration three years later.

Why this is a problem

Development runs fine without a standard. Each team names things reasonably on its own. The problem shows up only when you combine the three — the integrated lookup screen, settlement reconciliation, and the next-generation migration.

The most painful part is length. If one place defines a customer name as 50 characters and another as 30, the day a long name arrives it is loaded silently truncated. No error occurs, so nobody knows, and it is discovered months later through an inquiry such as "why does this customer's name look like this?"

What happens three years later

If you do not set a data standard early in the project, this is what you get.

고객관리 팀:  CUSTOMER   (CUST_NAME    VARCHAR(50))
주문 팀:      ORDERS     (CUSTOMER_NM  VARCHAR(100))
정산 팀:      SETTLEMENT (CUST_NM      VARCHAR(30))

It is the same customer name, but with three names and three lengths. At first there is no problem at all. The problem blows up when you join, when you build an integrated lookup, and when you migrate the data three years later.

• To build an integrated search screen, you have to use different column names from three tables each time
• When you try to put a 50-character name into the settlement team's table, it gets truncated. Silently truncated
• For the next-generation project's migration, a person has to build the mapping table by hand

That is why an SI project finalizes the data standard before development starts. For a public-sector project, following the "Guidelines for Database Standardization in Public Institutions," you deliver definition documents for standard words, standard domains, standard terms, and standard codes as deliverables.

The four stages of the standard

1. 표준단어사전   의미의 최소 단위와 그 약어
                  고객→CUST, 주문→ORD, 상품→PROD, 명칭→NM, 일자→DT,
                  금액→AMT, 수량→QTY, 번호→NO, 여부→YN, 코드→CD

2. 표준도메인     같은 성격의 값이 갖는 타입·길이 규칙
                  금액 → NUMBER(15,2)   일자 → CHAR(8)
                  여부 → CHAR(1) Y/N    코드 → VARCHAR(10)
                  명칭 → VARCHAR(100)   번호(내부키) → NUMBER(18)

3. 표준용어       단어를 조합한 논리명과 물리명
                  고객명 → CUST_NM (도메인: 명칭)
                  주문금액 → ORD_AMT (도메인: 금액)
                  주문일자 → ORD_DT (도메인: 일자)

4. 컬럼 정의      실제 DDL
                  ORD_AMT NUMBER(15,2) NOT NULL DEFAULT 0

If you follow this order, whoever builds it gets the same names and the same types. And when you need a new column, you only decide "which domain is this," and the rest is automatic.

An honest word about conventions

Domestic SI has some conventions you may not like.

• Dates as CHAR(8) YYYYMMDD — the DATE type is better. True.
• Flags as CHAR(1) Y/N — BOOLEAN is better. True.
• REG_DT, REG_ID, UPD_DT, UPD_ID on every table — audit columns. These are quite useful.
• Logical deletion (DEL_YN) — you do not physically delete. For recovery and auditing.

The first two are due to legacy compatibility. If only the new system goes a different way, conversion code appears at every integration and migration point, and if even one is missed, silently wrong data piles up. That cost can be larger than the benefit of a better type.

A standard is not "the best" but "an agreement." If you understand this sentence, instead of asking "why would you use such a bad approach," you will ask "when and how can we change this standard?"

Logical deletion does need care, though. A single query that misses the DEL_YN='N' condition spills deleted data onto the screen. So design it so that reads always go through a view, or at least put it on the code review checklist.

Constraints are code, not documents

If the table definition says "required field" but the DDL has no NOT NULL, that document is not enforced. A constraint is enforced only when you put it on the DB.

Constraint	What it prevents	Reality in SI
PRIMARY KEY	Duplicate rows	Always apply
NOT NULL	Missing required values	Always apply
CHECK	Out-of-range values / code value violations	Often omitted. You should apply it
UNIQUE	Duplicate business keys	Often omitted. The main culprit of duplicate-data incidents
FOREIGN KEY	Referential integrity	Controversial
DEFAULT	Unexpected NULLs	Applying it simplifies the code

There are reasons FKs are controversial. In large batches, FK checks eat performance, they force a load order during migration, and they conflict with logical deletion. So some organizations "apply FKs only in the development environment and leave them out of production."

Either way, decide explicitly and document it. The worst case is "some tables have them and some do not." Then nobody knows whether the application or the DB guarantees integrity.

Indexes are a design deliverable

If you treat an index as "something to add later if it is slow," you will suffer after launch. At the design stage, sort out the query patterns and design the indexes together with them.

화면 SCR-021 주문조회: WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?
  → IX_ORDERS_01 (CUST_ID, ORD_DT)

배치 BAT-005 일마감:  WHERE ORD_DT = ? AND ORD_STS_CD = '완료'
  → IX_ORDERS_02 (ORD_DT, ORD_STS_CD)

The column order of a composite index is the key. The rules are:

1. Put columns used in equality conditions (=) first
2. Put columns used in range conditions (BETWEEN, >, <) after them
3. Put columns with good selectivity (many distinct values) toward the front

(CUST_ID, ORD_DT) and (ORD_DT, CUST_ID) are completely different indexes. For WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?, the former is right. The latter has to sweep the whole date range and filter customers within it.

And indexes are not free. Because the indexes are updated on every INSERT/UPDATE/DELETE, a bulk load into a table with 10 indexes is very slow. This is why, during a data migration, you drop the indexes, load, and then recreate them.

Code tables — the war against hardcoding

Suppose you store order status as '01', '02', '03'. Where does the meaning of these values live?

• Application constants — hardcoded differently on every screen
• Common code table — managed in one place, can be added to during operation

In SI it is almost always the latter. And it usually has this structure.

CREATE TABLE COMMON_CODE (
  GRP_CD  VARCHAR(20) NOT NULL,   -- 'ORD_STS'
  CD      VARCHAR(20) NOT NULL,   -- '01'
  CD_NM   VARCHAR(100) NOT NULL,  -- '접수'
  SORT_NO INTEGER NOT NULL,
  USE_YN  CHAR(1) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (GRP_CD, CD)
);

One caution: just because something is in the common code table, anything must not be allowed in. Apply a CHECK to the status column, or at least decide where code values are validated. "There is a common code table, so it is fine" often means nobody validates.

Generate the table definition from the DB

You deliver a table definition as a design deliverable, but by the time development is finishing, the document and the actual schema have drifted apart. Always.

So the practical tip: generate the final table definition from the actual DB metadata. The column list, types, nullability, defaults, and constraints can be extracted with queries. The only thing a person needs to fill in is the description (comment).

And if you put that description in the DB's COMMENT feature, the document and the schema go together forever. If you use a DBMS where that is not possible, at least keep the generation script in configuration management and produce the document from it. The moment you maintain a document by hand, it starts to become a lie.

What it looks like in the field

The more common failure is setting a standard and not following it. The definition exists, but the tables contain names that are not in the standard, and nobody checks for that.

So a standard must be kept not as a document but in a form that can be checked. Extracting the table definition from pragma_table_info or the system catalog, instead of writing it by hand, is for the same reason — a hand-written definition inevitably drifts from the real schema, and a drifted definition is worse than none. Because the reader trusts it.

The same goes for constraints. If "quantity must be 1 or more" exists only in a document, sooner or later a 0 comes in. If you apply it as a CHECK, that sentence becomes code, and there is no way for it not to be enforced.