ํ”„๋กœ์ ํŠธ

[Floney] ์นดํ…Œ๊ณ ๋ฆฌ ๋ฆฌํŒฉํ† ๋ง ์ผ๋Œ€๊ธฐ

sechoi 2024. 3. 11. 15:09

๐Ÿ€ ๊ธฐ์กด ์ƒํ™ฉ

๋„๋ฉ”์ธ

์šฐ๋ฆฌ ๊ฐ€๊ณ„๋ถ€์—์„œ ํ•˜๋‚˜์˜ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์€ ์ง€์ถœ/์ˆ˜์ž…/์ด์ฒด๋กœ ๊ตฌ๋ถ„๋˜๊ณ , ์ž์‚ฐ๊ณผ ๋ถ„๋ฅ˜ ํ•ญ๋ชฉ์œผ๋กœ ์„ธ๋ถ€์ ์œผ๋กœ ๋‹ค์‹œ ๊ตฌ๋ถ„๋œ๋‹ค.

์ด ์„ธ ๊ฐ€์ง€๋ฅผ ํŽธ์˜์ƒ ์นดํ…Œ๊ณ ๋ฆฌ๋กœ ํ†ต์นญํ•œ๋‹ค.

๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ ์ถ”๊ฐ€ ํ™”๋ฉด

  • ์ง€์ถœ/์ˆ˜์ž…/์ด์ฒด (์ดํ›„ ์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ผ ์นญํ•จ): ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์˜ ์ข…๋ฅ˜
  • ๋ถ„๋ฅ˜ (์ดํ›„ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ผ ์นญํ•จ): ์œ„ ์ง€์ถœ/์ˆ˜์ž…/์ด์ฒด์˜ ํ•˜์œ„ ๋ถ„๋ฅ˜ (ex. ์‹๋น„, ์›”๊ธ‰ ๋“ฑ)
  • ์ž์‚ฐ (์ดํ›„ ํ•˜์œ„ ์ž์‚ฐ ์นดํ…Œ๊ณ ๋ฆฌ๋ผ ์นญํ•จ): ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์˜ ์ž์‚ฐ ์ถœ์ฒ˜ (ex. ํ˜„๊ธˆ, ์นด๋“œ ๋“ฑ)

์ด๋ฅผ ๊ณ„์ธต์ ์œผ๋กœ ์ •๋ฆฌํ•˜๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

 

 

์ด ์นดํ…Œ๊ณ ๋ฆฌ๋“ค์€ ๊ฐ€๊ณ„๋ถ€ ๋ณ„๋กœ ๊ด€๋ฆฌ๋œ๋‹ค.

๋ถ„๋ฅ˜ ํ•ญ๋ชฉ (ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ) ๊ด€๋ฆฌ ํ™”๋ฉด

 

์ด ๋•Œ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋Š” ๊ฐ€๊ณ„๋ถ€ ์ƒ์„ฑ ์‹œ ๊ธฐ๋ณธ์ ์œผ๋กœ N๊ฐœ๊ฐ€ ์ฃผ์–ด์ง„๋‹ค. ์ด๋ฅผ ๊ธฐ๋ณธ ์นดํ…Œ๊ณ ๋ฆฌ๋ผ๊ณ  ๋ถ€๋ฅด๊ฒ ๋‹ค. ๊ธฐ๋ณธ ์นดํ…Œ๊ณ ๋ฆฌ๋Š” ์‚ฌ์šฉ์ž๊ฐ€ ์ž„์˜๋กœ ์‚ญ์ œ ๋ฐ ์ˆ˜์ •์„ ํ•  ์ˆ˜ ์—†๋‹ค. ์œ„ ํ™”๋ฉด์— ๋ณด์ด๋Š” ์ž์‚ฐ์˜ ํ˜„๊ธˆ~์€ํ–‰ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๊ฐ€ ๊ทธ๋Ÿฌํ•˜๋‹ค.

๋‹ค๋งŒ ์‚ฌ์šฉ์ž๊ฐ€ ์ง์ ‘ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ์ถ”๊ฐ€ํ•  ์ˆ˜ ์žˆ๋‹ค. ์ด๋ฅผ ์ปค์Šคํ…€ ์นดํ…Œ๊ณ ๋ฆฌ๋ผ๊ณ  ๋ถ€๋ฅด๊ฒ ๋‹ค. ํ™”๋ฉด์˜ ์ถ”๊ฐ€ํ•ญ๋ชฉ1 ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๊ฐ€ ๊ทธ๋Ÿฌํ•˜๋‹ค.

 

 

๊ตฌํ˜„

๋‚˜์™€ ์„œ๋ฒ„๋ฅผ ๋‹ด๋‹นํ•˜๋Š” ํŒ€์› ๋ถ„์€ ์ด ๋„๋ฉ”์ธ์„ ๊ตฌํ˜„ํ•˜๊ธฐ ์œ„ํ•ด ์—ฌ๋Ÿฌ ๊ณ ๋ฏผ์„ ํ–ˆ๊ณ (๊ทธ ๋•Œ์˜ ๊ณ ๋ฏผ ๊ณผ์ •์€ ์ด ํฌ์ŠคํŒ…์—์„œ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค), ์ตœ์ข…์ ์œผ๋กœ ๋‹ค์Œ๊ณผ ๊ฐ™์ด ๊ตฌํ˜„ํ–ˆ๋‹ค.

 

โœ… ์นดํ…Œ๊ณ ๋ฆฌ

@Inheritance(strategy = SINGLE_TABLE)
public abstract class Category {

    @Column
    private String name;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_id")
    private Category parent;
}

// ์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ
@DiscriminatorValue("DefaultRoot")
public class RootCategory extends Category {
}

// ๊ธฐ๋ณธ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ
@DiscriminatorValue("Default")
public class DefaultCategory extends Category {
}

// ์ปค์Šคํ…€ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ
@DiscriminatorValue("Book")
public class BookCategory extends Category {

    @ManyToOne
    private Book book;
}

๊ธฐ๋ณธ์ ์œผ๋กœ ์นดํ…Œ๊ณ ๋ฆฌ๋ผ๋Š” ๋„๋ฉ”์ธ์— ์†ํ•ด ์žˆ๊ธฐ ๋•Œ๋ฌธ์— Category ์ถ”์ƒ ํด๋ž˜์Šค๋ฅผ ์ƒ์„ฑํ–ˆ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ, ํ•˜์œ„ ๊ธฐ๋ณธ ์นดํ…Œ๊ณ ๋ฆฌ, ํ•˜์œ„ ์ปค์Šคํ…€ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ์ƒ์†(@Inheritance)์„ ์ด์šฉํ•ด ๊ฐ๊ฐ์˜ ๊ฐ์ฒด๋กœ ๋‚˜๋ˆด๋‹ค.

 

id dtype name parent_id book_id
1 DefaultRoot ์ง€์ถœ null null
2 Default ์‹๋น„ 1 null
3 Book ๋‚ด๊ฐ€ ๋งŒ๋“  ์นดํ…Œ๊ณ ๋ฆฌ 1 1

์ƒ์† ์ „๋žต์€ SINGLE_TABLE ์„ ์‚ฌ์šฉํ–ˆ๋‹ค. ๋”ฐ๋ผ์„œ ์„ธ ๊ฐœ์˜ Category ๊ฐ์ฒด๊ฐ€ DB์—์„œ ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”์— ์ €์žฅ๋œ๋‹ค. ๋Œ€์‹  ๊ฐ ๊ฐ์ฒด๋Š” dType์œผ๋กœ ๊ตฌ๋ถ„๋œ๋‹ค.

 

โœ… ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ - ์นดํ…Œ๊ณ ๋ฆฌ ์—ฐ๊ฒฐ

ํ•˜๋‚˜์˜ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์€ ์„ธ ๊ฐœ์˜ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ์ฐธ์กฐํ•œ๋‹ค. ํ•˜๋‚˜์˜ ์นดํ…Œ๊ณ ๋ฆฌ๋Š” ์—ฌ๋Ÿฌ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ๋“ค์„ ์ฐธ์กฐํ•œ๋‹ค.

์„œ๋กœ๊ฐ€ ๋‹ค๋Œ€๋‹ค ๊ด€๊ณ„๋ฅผ ๋งบ๊ณ  ์žˆ์œผ๋ฏ€๋กœ DB์—์„œ ๋‘ ํ…Œ์ด๋ธ”์„ ์ฐธ์กฐํ•˜๋Š” ์—ฐ๊ฒฐ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ–ˆ๋‹ค. ์ด ๋•Œ ๋‚ด์—ญ์ด ์ฐธ์กฐํ•˜๋Š” ์„ธ ๊ฐœ์˜ ์นดํ…Œ๊ณ ๋ฆฌ๋Š” ๊ฐ์ž ์ž์‹ ์˜ ์†์„ฑ์ด ์žˆ์œผ๋ฏ€๋กœ, ์ด๋ฅผ category_type ์ปฌ๋Ÿผ์œผ๋กœ ๊ตฌ๋ถ„ํ•œ๋‹ค.

id book_id (์—ฐ๊ฒฐ ๊ฐ€๊ณ„๋ถ€) category_id (์—ฐ๊ฒฐ ์นดํ…Œ๊ณ ๋ฆฌ) category_type (์นดํ…Œ๊ณ ๋ฆฌ ์ข…๋ฅ˜)
1 1 1 FLOW (์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ - ์ง€/์ˆ˜/์ด)
2 1 2 ASSET (์ž์‚ฐ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ)
3 1 3 FLOW_LINE (ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ)

 

์–ดํ”Œ๋ฆฌ์ผ€์ด์…˜์—์„œ๋Š” ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์ด EnumMap์œผ๋กœ ์„ธ ๊ฐœ์˜ ์นดํ…Œ๊ณ ๋ฆฌ ์—ฐ๊ฒฐ ๊ฐ์ฒด๋ฅผ ์ฐธ์กฐํ•œ๋‹ค. ๋‚ด์—ญ์„ ์กฐํšŒํ•  ๋•Œ ๊ฑฐ์˜ ์—ฐ๊ด€ ์นดํ…Œ๊ณ ๋ฆฌ๊นŒ์ง€ ์กฐํšŒํ•˜๊ณ , ๋…ผ๋ฆฌ์ ์œผ๋กœ๋„ ๋‚ด์—ญ์— ์นดํ…Œ๊ณ ๋ฆฌ ์ •๋ณด๊ฐ€ ํฌํ•จ๋œ๋‹ค๊ณ  ํŒ๋‹จํ–ˆ๋‹ค.

// ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ
@Entity
public class BookLine {

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "bookLine")
    @MapKeyEnumerated(EnumType.STRING)
    private final Map<CategoryEnum, BookLineCategory> bookLineCategories = new EnumMap<>(CategoryEnum.class);
}

// ํ•œ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ๊ณผ ์—ฐ๊ด€๋œ ์นดํ…Œ๊ณ ๋ฆฌ ์ข…๋ฅ˜
public enum CategoryEnum {

    FLOW, 
    ASSET, 
    FLOW_LINE; 
}

 

 

 

 

 

๐Ÿ’ฉ ๋ณ€ํ™”๊ฐ€ ํ•„์š”ํ•˜๋‹ค

์ดํ›„ ์—ฌ๋Ÿฌ ๊ธฐ๋Šฅ์„ ๊ตฌํ˜„ํ•˜๋ฉด์„œ ๊ธฐ์กด ์„ค๊ณ„ ๋ฐ ์ฝ”๋“œ์— ๋Œ€ํ•ด ๋งŽ์€ ๋ถˆํŽธํ•จ์„ ๋Š๊ผˆ๋‹ค.

QueryDSL์˜ projection

๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์„ ์กฐํšŒํ•  ๋•Œ ์—ฐ๊ด€๋œ ์นดํ…Œ๊ณ ๋ฆฌ๋“ค์„ JOIN ํ•˜๋ฉด ์ผ๋Œ€๋‹ค๋กœ ์ฐธ์กฐํ•œ bookLineCategories ์— ์•Œ๋งž๊ฒŒ ์—ฐ๊ฒฐ๋œ๋‹ค.

// QueryDSL ์‚ฌ์šฉ
public BookLine getBookLine() {
    return jpaQueryFactory.from(bookLine)
        .innerJoin(bookLine.bookLineCategories, bookLineCategory)
        .fetchOne();
}

 

๋ฌธ์ œ๋Š” QueryProjection, ์ฆ‰ ์กฐํšŒ์šฉ ๊ฐ์ฒด๋ฅผ ์‚ฌ์šฉํ•  ๋•Œ๋‹ค.

// QueryProjection ์‚ฌ์šฉ
public CustomBookLine getBookLine() {
    return jpaQueryFactory
    	.select(new QCustomBookLine(bookLineCategory.name)) // X
    	.from(bookLine)
        .innerJoin(bookLine.bookLineCategories, bookLineCategory)
        .fetchOne();
}

 

ํ•˜๋‚˜์˜ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์— ์„ธ ๊ฐœ์˜ ์นดํ…Œ๊ณ ๋ฆฌ๊ฐ€ ์—ฐ๊ฒฐ๋  ๋•Œ ์ด ์ •๋ณด๋ฅผ bookLineCategories ๋กœ ๋ชจ์œผ์ง€ ์•Š์œผ๋ฏ€๋กœ ๊ฐ๊ฐ ๋”ฐ๋กœ ์กฐํšŒ๋œ๋‹ค.

 

๋‹น์‹œ์—๋Š” ๋งˆ๋•…ํ•œ ํ•ด๊ฒฐ์ฑ…์„ ๋ชป ์ฐพ์•„ 3๊ฐœ์˜ ํ–‰์„ ๋”ฐ๋กœ ๋ถˆ๋Ÿฌ์˜ค๋˜, ์–ดํ”Œ๋ฆฌ์ผ€์ด์…˜์—์„œ ์ง์ ‘ ํ•ฉ์ณ์คฌ๋‹ค.

// ์•ˆ ์ฝ์–ด๋„ ๋จ
// ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์— ์—ฌ๋Ÿฌ ๊ฐœ์˜ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ํ•˜๋‚˜์˜ ๊ฐ์ฒด๋กœ ํ•ฉ์น˜๋Š” ํ•จ์ˆ˜
public List<DayLines> transfer(List<CustomBookLine> dayLines) {
    Map<Long, DayLineInfo> dayLineWithCategories = new HashMap<>();

    dayLines.forEach((dayLine) ->
    {
        DayLineInfo dayLineInfo = dayLineWithCategories.get(dayLine.getId());

        // ์นดํ…Œ๊ณ ๋ฆฌ ์™ธ์˜ ๋ฐ์ดํ„ฐ ์ตœ์ดˆ ๋“ฑ๋ก
        if (dayLineInfo == null) {
            dayLineWithCategories.put(dayLine.getId(), DayLineInfo.toDayViewInfos(dayLine));
        } else {
            dayLineInfo.addCategory(dayLine.getCategories());
        }
    });

    return;
}

๋Œ€์ถฉ ๋ด๋„ ๋ณต์žกํ•˜๋‹ค. 

 

 

SINGLE_TABLE ์ƒ์† ์ „๋žต๊ณผ ์œ ๋‹ˆํฌ ์ธ๋ฑ์Šค

DB์˜ Category ํ…Œ์ด๋ธ”์€ ๋‹ค์Œ๊ณผ ๊ฐ™์ด dtype ์œผ๋กœ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ๊ตฌ๋ณ„ํ•œ๋‹ค.

id dtype name parent_id book_id
1 DefaultRoot ์ง€์ถœ null null
2 Default ์‹๋น„ 1 null
3 Book ๋‚ด๊ฐ€ ๋งŒ๋“  ์นดํ…Œ๊ณ ๋ฆฌ 1 1

 

์ด ๋•Œ dtype ์— ๋”ฐ๋ผ constraints ๋„ ๋‹ฌ๋ผ์ง„๋‹ค.

DefaultRoot ๋Š” ์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ฉฐ ๋ชจ๋“  ๊ฐ€๊ณ„๋ถ€๊ฐ€ ๊ณต์œ ํ•œ๋‹ค. ๋”ฐ๋ผ์„œ parent_id ์™€ book_id ๊ฐ€ ๋ชจ๋‘ null ์ด๋‹ค.

Default ๋Š” ๊ธฐ๋ณธ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ผ ๋ชจ๋“  ๊ฐ€๊ณ„๋ถ€๊ฐ€ ๊ณต์œ ํ•œ๋‹ค. ๋”ฐ๋ผ์„œ book_id ๊ฐ€ null ์ด๋‹ค.

Book ์€ ์ปค์Šคํ…€ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ผ ๊ฐ€๊ณ„๋ถ€ ๋งˆ๋‹ค ์ƒ์„ฑ ๊ฐ€๋Šฅํ•˜๋‹ค. ๋”ฐ๋ผ์„œ ๋ชจ๋“  ์ปฌ๋Ÿผ์— null ์ด ํ—ˆ์šฉ๋˜์ง€ ์•Š๋Š”๋‹ค.

 

๋…ผ๋ฆฌ์ ์œผ๋กœ๋Š” ์œ„์™€ ๊ฐ™์ง€๋งŒ, ๋ชจ๋‘ ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”์„ ์‚ฌ์šฉํ•˜๋ฏ€๋กœ parent_id ์™€ book_id ๋Š” null ์„ ๋ฌด์กฐ๊ฑด ํ—ˆ์šฉํ•ด์•ผ ํ•œ๋‹ค.

 

์ด ๋•Œ ๋„๋ฉ”์ธ ์ƒ ์ปค์Šคํ…€ ์นดํ…Œ๊ณ ๋ฆฌ๋Š” (์ด๋ฆ„, ๊ฐ€๊ณ„๋ถ€, ์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ) ์กฐํ•ฉ์ด ์œ ์ผํ•˜๋‹ค. ๊ธฐ๋ณธ ์นดํ…Œ๊ณ ๋ฆฌ์™€๋„ ์ด๋ฆ„์ด ๊ฒน์น˜๋ฉด ์•ˆ ๋œ๋‹ค.

๋ถ„๋ฅ˜ ํ•ญ๋ชฉ (ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ) ๊ด€๋ฆฌ ํ™”๋ฉด

์ฆ‰ ์—ฌ๊ธฐ์„œ ์ž์‚ฐ์˜ ๋ถ„๋ฅ˜ ํ•ญ๋ชฉ์„ ์ถ”๊ฐ€ํ•  ๋•Œ ํ˜„๊ธˆ๋ถ€ํ„ฐ ์ถ”๊ฐ€ํ•ญ๋ชฉ1 ๊นŒ์ง€ ์ด๋ฆ„์ด ๊ฒน์น˜๋ฉด ์•ˆ ๋œ๋‹ค. 

 

์ด๋ฅผ ์–ดํ”Œ๋ฆฌ์ผ€์ด์…˜์—์„œ ์กฐ๊ฑด์— ๋งž๋Š” ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ๋จผ์ € ์กฐํšŒํ•˜๊ณ , ์žˆ์œผ๋ฉด ์˜ˆ์™ธ๋ฅผ ๋˜์ง€๊ณ  ์—†์œผ๋ฉด ์ถ”๊ฐ€ํ•˜๋Š” ๋ฐฉ์‹์œผ๋กœ ๊ตฌํ˜„ํ–ˆ๋‹ค.

...๋ญ”๊ฐ€ ์ต์ˆ™ํ•œ ๋ฐฉ์‹์ด๋‹ค.

๋งž๋‹ค. ์ž์‚ฐ ์ด์Šˆ์—์„œ ๋˜‘๊ฐ™์ด '์กฐํšŒ ํ›„ ๋ฐ์ดํ„ฐ ์‚ฝ์ž…'์„ ํ•˜๋‹ค๊ฐ€ ๋™์‹œ์„ฑ ๋ฌธ์ œ๊ฐ€ ๋ฐœ์ƒํ–ˆ์—ˆ๋‹ค.

 

์ด ๋•Œ ํ•ด๋‹น ์ด์Šˆ์—์„œ๋Š” MySQL์˜ upsert ๋ฌธ๋ฒ•(INSERT ... ON DUPLICATE KEY UPDATE)์„ ์‚ฌ์šฉํ•œ Atomic Query๋กœ ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ–ˆ๋‹ค. ๊ทธ๋ž˜์„œ ๋˜‘๊ฐ™์ด ์ ์šฉํ•˜๋ ค๊ณ  ํ–ˆ์œผ๋‚˜, ์•ˆํƒ€๊น๊ฒŒ๋„ MySQL์—์„œ๋Š” ํŠน์ • ์ปฌ๋Ÿผ์— null ๊ฐ’์ด ํฌํ•จ๋˜๋ฉด unique index๊ฐ€ ์˜๋„๋Œ€๋กœ ์ž‘๋™ํ•˜์ง€ ์•Š๋Š”๋‹ค. MySQL์—์„œ null์€ '์•Œ ์ˆ˜ ์—†๋Š” ๊ฐ’'์œผ๋กœ ์ทจ๊ธ‰๋˜๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค.

The concept of the NULL value is a common source of confusion for newcomers to SQL, who often think that NULL is the same thing as an empty string ''. This is not the case. 
Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.
NULL ๊ฐ’์˜ ๊ฐœ๋…์€ SQL์„ ์ฒ˜์Œ ์ ‘ํ•˜๋Š” ์‚ฌ๋žŒ๋“ค์ด ํ”ํžˆ ํ˜ผ๋™ํ•˜๋Š” ๋ถ€๋ถ„์œผ๋กœ, NULL์ด ๋นˆ ๋ฌธ์ž์—ด ''๊ณผ ๊ฐ™์€ ์˜๋ฏธ๋ผ๊ณ  ์ƒ๊ฐํ•˜๋Š” ๊ฒฝ์šฐ๊ฐ€ ๋งŽ์œผ๋‚˜ ๊ทธ๋ ‡์ง€ ์•Š๋‹ค. ๊ฐœ๋…์ ์œผ๋กœ NULL์€ "์•Œ ์ˆ˜ ์—†๋Š” ๋ˆ„๋ฝ๋œ ๊ฐ’"์„ ์˜๋ฏธํ•˜๋ฉฐ ๋‹ค๋ฅธ ๊ฐ’๊ณผ ๋‹ค์†Œ ๋‹ค๋ฅด๊ฒŒ ์ทจ๊ธ‰๋œ๋‹ค.

- MySQL 8.0 references ์ค‘

๋”ฐ๋ผ์„œ nullable ์ปฌ๋Ÿผ์— unique ์ œ์•ฝ ์กฐ๊ฑด์„ ์ถ”๊ฐ€ํ•˜๋Š” ๊ฒฝ์šฐ null ๊ฐ’์ธ ํ–‰์€ ์—ฌ๋Ÿฌ ๊ฐœ๊ฐ€ ๋  ์ˆ˜ ์žˆ๋‹ค. ๊ธฐ์กด์˜ null ๊ฐ’๊ณผ ์ƒˆ๋กœ ์ถ”๊ฐ€๋œ null ๊ฐ’์ด ๊ฐ™๋‹ค๊ณ  ํŒ๋‹จํ•  ์ˆ˜ ์—†๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค.

 

Atomic Query๋กœ ๋™์‹œ์„ฑ ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•  ์ˆ˜ ์—†์œผ๋ฉด ์ƒํ™ฉ์ด ๋ณต์žกํ•ด์ง„๋‹ค. MySQL์˜ Named Lock ํ˜น์€ Redis ๋ฝ ๋“ฑ์„ ์‚ฌ์šฉํ•ด์•ผ ํ•œ๋‹ค. ๊ณ ์ž‘ ์นดํ…Œ๊ณ ๋ฆฌ ์ค‘๋ณต ๋ฐฉ์ง€์— ์ด๋งŒํ•œ ์‹œ๊ฐ„์„ ๋“ค์—ฌ์•ผํ•˜๋‚˜ ์‹ถ์–ด์ง„๋‹ค.

 

 

๋‘ ์ข…๋ฅ˜์˜ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ - ๊ธฐ๋ณธ๊ณผ ์ปค์Šคํ…€

ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋Š” ํŠน์„ฑ์— ๋”ฐ๋ผ ๋‹ค์‹œ ๊ธฐ๋ณธ๊ณผ ์ปค์Šคํ…€ ์นดํ…Œ๊ณ ๋ฆฌ๋กœ ๋‚˜๋‰œ๋‹ค. ์ด๋ฅผ ์–ดํ”Œ๋ฆฌ์ผ€์ด์…˜์—์„œ๋„ ๊ฐ๊ฐ์˜ ๊ฐ์ฒด(DefaultCategory, BookCategory)๋กœ ๊ตฌ๋ถ„ํ–ˆ๋‹ค.

 

ํ•˜์ง€๋งŒ ๋Œ€๋ถ€๋ถ„์˜ ์กฐํšŒ ์ฟผ๋ฆฌ์—์„œ ๊ทธ ๋‘˜์„ ๊ตฌ๋ถ„ํ•˜์ง€ ์•Š๊ณ  ์กฐํšŒํ•œ๋‹ค. ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ์ƒ์„ฑํ•œ ์ด์ƒ, ํ•ด๋‹น ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ์กฐํšŒํ•  ๋•Œ๋Š” ๋‘˜์„ ๊ตฌ๋ณ„ํ•˜๋Š” ๊ฒŒ ๋ฌด์˜๋ฏธํ•˜๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค. ํ•˜์ง€๋งŒ ๊ฐ์ฒด๋กœ๋Š” ์—„์—ฐํžˆ ๊ตฌ๋ถ„๋˜์–ด ์žˆ๊ณ  ์กฐ๊ฑด๋„ ๋‹ค๋ฅด๊ฒŒ ์ฃผ์–ด์•ผ ํ•˜๋ฏ€๋กœ ์ฟผ๋ฆฌ๋ฅผ ๋‘ ๋ฒˆ ๋‚ ๋ ค์•ผ ํ•œ๋‹ค. ๋ถˆํŽธํ•˜๋‹ค.

public Optional<Category> findFlowLineCategory() {
    // ๊ธฐ๋ณธ ์นดํ…Œ๊ณ ๋ฆฌ ๋จผ์ € ์กฐํšŒ
    Optional<Category> target = Optional.ofNullable(jpaQueryFactory.selectFrom(category)
        .where(
            category.name.eq(name),
            category.parent.eq(parentFlowCategory),
            category.instanceOf(DefaultCategory.class)
        )
        .fetchOne());

    // ๊ธฐ๋ณธ ์นดํ…Œ๊ณ ๋ฆฌ์—์„œ ์›ํ•˜๋Š” ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ์ฐพ์ง€ ๋ชปํ•œ ๊ฒฝ์šฐ, ์ปค์Šคํ…€ ์นดํ…Œ๊ณ ๋ฆฌ ์กฐํšŒ
    if (target.isEmpty()) {
        target = Optional.ofNullable(jpaQueryFactory
            .selectFrom(bookCategory)
            .innerJoin(bookCategory.book, book)
            .where(
                book.bookKey.eq(bookKey),
                bookCategory.name.eq(name),
                bookCategory.parent.eq(parentFlowCategory),
            )
            .fetchOne());
    }
    return target;
}

 

 

๋„๋ฉ”์ธ ์šฉ์–ด

์™ธ์—๋„ ์ฝ”๋“œ ๋‚ด์— ์“ฐ์ธ ์šฉ์–ด๋“ค์ด ํ—ท๊ฐˆ๋ฆฌ๋Š” ๊ฒฝ์šฐ๊ฐ€ ๋งŽ์•˜๋‹ค. ํ•œ๊ธ€๋กœ ๋œ ๋„๋ฉ”์ธ ์šฉ์–ด๋ฅผ ์˜์–ด๋กœ ๋ฒˆ์—ญํ•˜๋Š” ๊ณผ์ •์—์„œ ๋‹ค๋ฅธ ์šฉ์–ด์™€ ๊ฒน์น˜๋Š” ๊ฒฝ์šฐ๋„ ์žˆ์—ˆ๊ณ , ์ด๋ฆ„๋งŒ ๋ดค์„ ๋•Œ ๋ฌด์Šจ ๋„๋ฉ”์ธ์ธ์ง€ ํ—ท๊ฐˆ๋ฆฌ๋Š” ๊ฒฝ์šฐ๋„ ์žˆ์—ˆ๋‹ค.

 

์˜ˆ๋ฅผ ๋“ค์–ด ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ๊ณผ ๊ด€๋ จ๋œ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ๊ด€๋ฆฌํ•˜๋Š” CategoryEnum ์ด ์žˆ์—ˆ๋‹ค.

// ํ•œ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ๊ณผ ์—ฐ๊ด€๋œ ์นดํ…Œ๊ณ ๋ฆฌ ์ข…๋ฅ˜
public enum CategoryEnum {

    FLOW("๋‚ด์—ญ"), 
    ASSET("์ž์‚ฐ"), 
    FLOW_LINE("๋‚ด์—ญ ๋ถ„๋ฅ˜"); 
}

์—ฌ๊ธฐ์„œ๋Š” FLOW ๊ฐ€ ๋‚ด์—ญ์ด๋‹ค. ํ•˜์ง€๋งŒ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์€ Book'Line' ์ด๋‹ค.

๋˜ ์ด ๊ฐ์ฒด์˜ ASSET ์€ ์ž์‚ฐ ๋ถ„๋ฅ˜ ํ•ญ๋ชฉ์„ ์˜๋ฏธํ•œ๋‹ค. ํ•˜์ง€๋งŒ ์šฐ๋ฆฌ๋Š” ๊ฐ€๊ณ„๋ถ€ ์ˆ˜์ž… ๋ฐ ์ง€์ถœ ๋‚ด์—ญ์œผ๋กœ ๊ณ„์‚ฐํ•˜๋Š” ์ž์‚ฐ ๋„๋ฉ”์ธ์ด ๋”ฐ๋กœ ์žˆ๊ณ , DB์—์„œ asset ํ…Œ์ด๋ธ”์— ์ €์žฅํ•œ๋‹ค. 

 

์ด๋ ‡๊ฒŒ ์šฉ์–ด๊ฐ€ ํ—ท๊ฐˆ๋ฆฌ๋Š” ๊ฒŒ ๊ฐœ๋ฐœํ•˜๋ฉด์„œ ์ƒ๊ฐ๋ณด๋‹ค ํฐ ์žฅ์• ๋ฌผ์ด ๋˜์—ˆ๋‹ค.

 

 

 

๐Ÿ’ฆ ์žฌ์„ค๊ณ„

๊ณ ๋ ค์‚ฌํ•ญ

๊ทธ๋™์•ˆ ๊ฒช์€ ๋ถˆํŽธํ•จ์„ ๋ฐ”ํƒ•์œผ๋กœ ์ƒ๊ธด ์กฐ๊ฑด์€ ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

  • ๊ธฐ๋ณธ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ์™€ ์ปค์Šคํ…€ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋Š” ์กฐํšŒ๋  ๋•Œ ํ•˜๋‚˜๋กœ ์ทจ๊ธ‰๋˜์–ด์•ผ ํ•œ๋‹ค.
  • ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”๋กœ ๋ฌถ์ธ ์นดํ…Œ๊ณ ๋ฆฌ๋“ค์„ ๋‚˜๋ˆ„์–ด ์œ ๋‹ˆํฌ ์ธ๋ฑ์Šค๋ฅผ ์ถ”๊ฐ€ํ•œ๋‹ค.
  • ํ•˜๋‚˜์˜ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์„ ์ฐธ์กฐํ•˜๋Š” ์„ธ ๊ฐœ์˜ ์นดํ…Œ๊ณ ๋ฆฌ ์—ฐ๊ฒฐ ๋ฐ์ดํ„ฐ๋Š” ํ•˜๋‚˜๋กœ ๋ฌถ๋Š”๋‹ค.
  • ๋ณ€๊ฒฝํ•˜๋Š” ๊น€์— ์šฉ์–ด๋“ค ๋˜ํ•œ ๊ฐ€๋…์„ฑ ์ข‹๊ฒŒ ์ˆ˜์ •ํ•œ๋‹ค.

๋งˆ์นจ ๊ธฐ๋ณธ ์นดํ…Œ๊ณ ๋ฆฌ๋„ ํŽธ์ง‘ํ•˜๊ฒŒ ํ•ด๋‹ฌ๋ผ๋Š” ์š”์ฒญ์ด ๋งŽ์•„์ ธ ์ปค์Šคํ…€ ์นดํ…Œ๊ณ ๋ฆฌ์™€ ๋˜‘๊ฐ™์ด ํŽธ์ง‘์ด ๊ฐ€๋Šฅํ•˜๋„๋ก ๊ธฐํš์ด ์ถ”๊ฐ€๋˜์—ˆ๋‹ค. ๋”ฐ๋ผ์„œ ์ฒซ ๋ฒˆ์งธ ์กฐ๊ฑด์„ ํ•ด๊ฒฐํ•  ์ˆ˜ ์žˆ๊ฒŒ ๋˜์—ˆ๋‹ค.

 

๋˜ ์—ฌ๋Ÿฌ ๊ณ ๋ฏผ ๋์— ์–ดํ”Œ๋ฆฌ์ผ€์ด์…˜์—์„œ ์นดํ…Œ๊ณ ๋ฆฌ ๊ฐ„์˜ ์ƒ์† ๊ด€๊ณ„๋ฅผ ์—†์• ๊ธฐ๋กœ ํ–ˆ๋‹ค. ์šฐ๋ฆฌ๋Š” ๋‘ ๋ฒˆ์งธ ์กฐ๊ฑด์„ ์œ„ํ•ด ์ƒ์† ์ „๋žต ์ค‘ TABLE_PER_CLASS ๋ฅผ ์‚ฌ์šฉํ•˜๊ณ  ์‹ถ์—ˆ๋‹ค. ํ•˜์ง€๋งŒ ์ด ์ „๋žต๊ณผ ์‹๋ณ„์ž(id) ์ƒ์„ฑ ๋ฐฉ์‹ ์ค‘ IDENTITY ๋ฅผ ๊ฐ™์ด ์‚ฌ์šฉํ•˜๋ฉด ์˜ค๋ฅ˜๊ฐ€ ๋ฐœ์ƒํ•œ๋‹ค(์ฐธ๊ณ ). ๋…ผ๋ฆฌ์ ์œผ๋กœ๋„ ๊ตณ์ด ๊ฐ ์—”ํ‹ฐํ‹ฐ๋“ค์ด ๋ฌถ์—ฌ ์žˆ์„ ํ•„์š”๊ฐ€ ์—†๋‹ค๊ณ  ํŒ๋‹จํ–ˆ๋‹ค.

 

 

์ตœ์ข… ์„ค๊ณ„

๊ณ ๋ฏผ ๋์— ๋‹ค์Œ๊ณผ ๊ฐ™์€ ์„ค๊ณ„๋ฅผ ์™„์„ฑํ–ˆ๋‹ค.

 

โœ… ์นดํ…Œ๊ณ ๋ฆฌ

 

  • Category ํ…Œ์ด๋ธ”: ์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ (์ง€์ถœ/์ˆ˜์ž…/์ด์ฒด/์ž์‚ฐ)
  • Subcategory ํ…Œ์ด๋ธ”: ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ
  • DefaultSubcategory ํ…Œ์ด๋ธ”: ๊ธฐ๋ณธ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ
    (๊ฐ€๊ณ„๋ถ€๋ฅผ ์ƒ์„ฑํ•  ๋•Œ๋งŒ ํ•ด๋‹น ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถˆ๋Ÿฌ์™€ Subcategory ๋ฐ์ดํ„ฐ๋กœ ์ €์žฅํ•œ๋‹ค.)

 

โœ… ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ-์นดํ…Œ๊ณ ๋ฆฌ

 

์„ธ ๊ฐœ์˜ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ์„ธ ๊ฐœ์˜ ํ–‰์œผ๋กœ ์ €์žฅํ•˜์ง€ ์•Š๊ณ , ์„ธ ๊ฐœ์˜ column ์„ ํ†ตํ•ด ํ•˜๋‚˜์˜ ํ–‰์œผ๋กœ ์ €์žฅํ•œ๋‹ค.

 

 

 

 

 

๐Ÿง ํ…Œ์ŠคํŠธ ์—†๋Š” ๋ฆฌํŒฉํ† ๋ง

์„ค๊ณ„๋Š” ์™„์„ฑํ–ˆ๋‹ค. ์ด์ œ ๊ตฌํ˜„์ด ํ•„์š”ํ•˜๋‹ค.

๊ธฐ์กด ์ƒํ™ฉ

์•ˆํƒ€๊น๊ฒŒ๋„ ์ถœ์‹œ ์ผ์ •์— ์น˜์—ฌ ๊ฐœ๋ฐœํ•˜๋Š๋ผ ํ…Œ์ŠคํŠธ๊ฐ€ ๋งค์šฐ ๋ถ€์กฑํ–ˆ๋‹ค. ๋” ์•ˆํƒ€๊นŒ์šด ์ ์€ ๊ทธ ์ ์€ ํ…Œ์ŠคํŠธ๋Š” ๋ชจ๋‘ ๋‹จ์œ„ ํ…Œ์ŠคํŠธ๋ผ, ๊ฐ์ฒด ์ž์ฒด๊ฐ€ ๋ณ€ํ•˜๋ฉด์„œ ๋‹จ์œ„ ํ…Œ์ŠคํŠธ๋“ค์ด ๋ชจ๋‘ ์‚ฌ๋ผ์ ธ์•ผํ–ˆ๋‹ค. ๊ทธ๋ž˜์„œ ํ…Œ์ŠคํŠธ๊ฐ€ ์—†๋Š” ๋ฆฌํŒฉํ† ๋ง์„ ์ง„ํ–‰ํ•ด์•ผ ํ–ˆ๋‹ค.

๋˜ํ•œ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ๊ณผ ์นดํ…Œ๊ณ ๋ฆฌ๋Š” ๋งค์šฐ ๋น„์ค‘์ด ๋†’์€ ํ•˜์œ„ ๋„๋ฉ”์ธ์ด๋ผ ํ”„๋กœ๋•์…˜ ์ฝ”๋“œ์˜ ๋Œ€๋ถ€๋ถ„์ด ๋ฐ”๋€Œ์—ˆ๋‹ค.

 

์šฐ๋ฆฌ๋Š” ๋ฆฌํŒฉํ† ๋ง ์ค‘ ํ˜น์—ฌ๋‚˜ ์ž˜๋ชป ๋ณ€๊ฒฝํ•œ ์ฝ”๋“œ๊ฐ€ ์žˆ์„ ์ง€ ๋งค์šฐ ๋ถˆ์•ˆํ–ˆ๋‹ค. ์ด๋ฏธ ํ…Œ์ŠคํŠธ ์—†์ด ๊ธ‰ํ•˜๊ฒŒ ์˜ค๋ฅ˜๋ฅผ ์ˆ˜์ •ํ•˜๋‹ค๊ฐ€ ๋ฐค์„ ์ƒˆ๋ฉฐ ์šด์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ˆ˜์ •ํ•œ ์ „์ ์ด ์žˆ๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค.

๊ฐ€๊ณ„๋ถ€ 200๊ฐœ์˜ ๋‚ด์—ญ๋“ค์„ ๋ถˆ๋Ÿฌ์™€ ์—‘์…€ ํ•จ์ˆ˜๋กœ ์ผ์ผํžˆ ๊ณ„์‚ฐํ•˜๊ณ  ํ™•์ธํ•œ ๋ˆˆ๋ฌผ์˜ ํ”์ 

 

๊ทธ๋ž˜์„œ ์‹œ๊ฐ„์ด ๊ฑธ๋ฆฌ๋”๋ผ๋„ ํ…Œ์ŠคํŠธ๋ฅผ ์ตœ๋Œ€ํ•œ ์ถ”๊ฐ€ํ•˜๊ธฐ๋กœ ํ–ˆ๋‹ค.

 

 

DCI ํŒจํ„ด ๋„์ž…

๊ธฐ์กด์— ํ…Œ์ŠคํŠธ ์ฝ”๋“œ๋ฅผ ์ž‘์„ฑํ•˜๋ฉด์„œ ๋ถˆํŽธํ–ˆ๋˜ ์ ์€, ํ•˜๋‚˜์˜ ๊ธฐ๋Šฅ์— ๋Œ€ํ•ด ์—ฌ๋Ÿฌ ์„ฑ๊ณต ๋ฐ ์‹คํŒจ ํ…Œ์ŠคํŠธ๊ฐ€ ์กด์žฌํ•˜๋Š” ๋ฐ ์ด๋ฅผ ํ™•์ธํ•˜๊ธฐ ์‰ฝ์ง€ ์•Š์•˜๋‹ค. ์ผ๋ ฌ๋กœ ๋‚˜์—ด๋œ ํ…Œ์ŠคํŠธ ๊ฒฐ๊ณผ์ฐฝ์„ ๋ณด๊ณ  ์–ด๋–ค ํ…Œ์ŠคํŠธ๋ฅผ ๋นผ๋จน์—ˆ๋Š”์ง€ ํ˜น์€ ์–ด๋–ค ํ…Œ์ŠคํŠธ๊ฐ€ ์ค‘๋ณต๋˜์—ˆ๋Š”์ง€ ๋ฐ”๋กœ ํŒŒ์•…ํ•  ์ˆ˜ ์—†์—ˆ๋‹ค.

 

๊ทธ๋ž˜์„œ ๊ตฌ์กฐํ™” ๋œ ํ…Œ์ŠคํŠธ ์ฝ”๋“œ๋ฅผ ์œ„ํ•ด DCI ํŒจํ„ด(์ฐธ๊ณ )์„ ์ œ์•ˆํ•ด ๋„์ž…ํ–ˆ๋‹ค.

 

ํŒจํ„ด์— ๋งž์ถ”์–ด ์ฝ”๋“œ๋ฅผ ์ž‘์„ฑํ•˜๋ฉด์„œ ์ฝ”๋“œ ์–‘์ด ๋Š˜์–ด๋‚œ๋‹ค๋Š” ๋‹จ์ ์ด ์žˆ์ง€๋งŒ, class ๋‹จ์œ„๋กœ ํ…Œ์ŠคํŠธ ๋Œ€์ƒ(Describe) ๋ฐ ํ™˜๊ฒฝ(Context)์„ ๋ถ„๋ฆฌํ•˜๋‹ˆ ์ผ€์ด์Šค ๊ตฌ๋ณ„์ด ์‰ฌ์›Œ ๋งŒ์กฑ์Šค๋Ÿฝ๋‹ค.

๋ฌด์—‡๋ณด๋‹ค๋„ ์‹คํ–‰ ์ฐฝ์ด ๋งค์šฐ ์•„๋ฆ„๋‹ต๋‹ค. ๐ŸŽจ

 

 

์ƒˆ๋กœ์šด ํŒ€์› ์˜์ž…

DCI ํŒจํ„ด๊ณผ ๋ณ„๊ฐœ๋กœ ์ถ”๊ฐ€ํ•ด์•ผ ํ•  ํ…Œ์ŠคํŠธ๊ฐ€ ๋งŽ์•˜๋‹ค. ํ•˜์ง€๋งŒ ์ƒˆ๋กœ ๊ตฌํ˜„ํ•ด์•ผ ํ•  ๊ธฐ๋Šฅ๋„ ์žˆ์–ด ์ธ๋ ฅ์ด ๋ถ€์กฑํ•˜๋‹ค๊ณ  ํŒ๋‹จํ–ˆ๋‹ค. ๊ทธ๋ž˜์„œ ์šด์˜ํŒ€์—๊ฒŒ ์„œ๋ฒ„ ๊ฐœ๋ฐœ์ž ์ถฉ์›์„ ์š”์ฒญํ–ˆ๊ณ , ํ•œ ๋ถ„์ด ์ƒˆ๋กœ ๋“ค์–ด์™€์„œ ๊ฐ™์ด 200์—ฌ ๊ฐœ์˜ ํ…Œ์ŠคํŠธ๋ฅผ ์ถ”๊ฐ€ํ–ˆ๋‹ค. ์ง€๊ธˆ๋„ ์ถ”๊ฐ€ํ•˜๋Š” ์ค‘์ด๋‹ค.

 

 

 

 

 

โ˜ ๏ธ ๋ฐ์ดํ„ฐ ๋งˆ์ด๊ทธ๋ ˆ์ด์…˜

์ƒˆ๋กœ์šด ์„ค๊ณ„๋ฅผ ๋ฐ”ํƒ•์œผ๋กœ ์ฝ”๋“œ ์ž‘์„ฑ๋„ ๋๋‚ฌ๋‹ค. ์ด์ œ DB์— ์ €์žฅ๋œ ๊ธฐ์กด ๋ฐ์ดํ„ฐ๋“ค์„ ์ƒˆ ํ…Œ์ด๋ธ”๋กœ ์•Œ๋งž๊ฒŒ ์ด๋™ํ•˜๋ฉด ๋œ๋‹ค.

๋ฌธ์ œ ์ƒํ™ฉ

์˜ˆ์ƒํ•˜๊ธด ํ–ˆ์ง€๋งŒ ๊ฐ„๋‹จํ•œ ์ž‘์—…์ด ์•„๋‹ˆ์—ˆ๋‹ค. id๊ฐ€ 1์ธ ํ•œ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ์ด ์žˆ๋‹ค๊ณ  ๊ฐ€์ •ํ•˜์ž.

 

โœ… ๊ธฐ์กด ์นดํ…Œ๊ณ ๋ฆฌ ํ…Œ์ด๋ธ” (old_category)

id dType (์ข…๋ฅ˜) parent_id (์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ) book_id (์—ฐ๊ฒฐ ๊ฐ€๊ณ„๋ถ€) name (์ด๋ฆ„)
1 DefaultRoot  null null ์ˆ˜์ž…
2 DefaultRoot  null null ์ง€์ถœ
3 DefaultRoot  null null ์ด์ฒด
4 DefaultRoot  null null ์ž์‚ฐ
5 Default 1 null ์›”๊ธ‰
6 Book 4 1 ์ƒˆ ์ž์‚ฐ ๋ถ„๋ฅ˜

 

โœ… ๊ธฐ์กด ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ-์นดํ…Œ๊ณ ๋ฆฌ ์—ฐ๊ฒฐ ํ…Œ์ด๋ธ” (old_book_line_category)

id book_id (์—ฐ๊ฒฐ ๊ฐ€๊ณ„๋ถ€) category_id (์—ฐ๊ฒฐ ์นดํ…Œ๊ณ ๋ฆฌ) category_type (์นดํ…Œ๊ณ ๋ฆฌ ์ข…๋ฅ˜)
1 1 1 FLOW (์ง€์ถœ/์ˆ˜์ž…/์ด์ฒด)
2 1 5 ASSET (์ž์‚ฐ ๋ถ„๋ฅ˜)
3 1 6 FLOW_LINE (์ง€/์ˆ˜/์ด ๋ถ„๋ฅ˜)

์ด ์ƒํ™ฉ์—์„œ ์„ธ ๊ฐœ์˜ ํ–‰์œผ๋กœ ์ชผ๊ฐœ์ ธ์žˆ๋˜ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ-์นดํ…Œ๊ณ ๋ฆฌ ์—ฐ๊ฒฐ ๋ฐ์ดํ„ฐ๋ฅผ ํ•˜๋‚˜์˜ ํ–‰์œผ๋กœ ํ•ฉ์ณ์•ผ ํ•œ๋‹ค.

 

Q. ์—ฐ๊ฒฐ๋œ ์นดํ…Œ๊ณ ๋ฆฌ ์ •๋ณด๋ฅผ(category_id = 1, 5, 6)์„ ์ƒˆ๋กœ์šด ํ–‰์˜ ๊ฐ ์ปฌ๋Ÿผ์— ๋„ฃ์–ด์ฃผ๋ฉด ๋˜๋Š” ๊ฑฐ ์•„๋‹Œ๊ฐ€์š”?
A. ๊ทธ๊ฑฐ ์•„๋‹ˆ์•ผ...

 

โœ… ์ƒˆ ์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ ํ…Œ์ด๋ธ” (category)

id name (์ด๋ฆ„)
1 ์ˆ˜์ž…
2 ์ง€์ถœ
3 ์ด์ฒด
4 ์ž์‚ฐ

โœ… ์ƒˆ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ ํ…Œ์ด๋ธ” (subcategory)

id parent_id (์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ) book_id (์—ฐ๊ฒฐ ๊ฐ€๊ณ„๋ถ€) name (์ด๋ฆ„)
1 1 1 ์›”๊ธ‰
2 4 1 ์ƒˆ ์ž์‚ฐ ๋ถ„๋ฅ˜ 

 

โœ… ์ƒˆ ๊ฐ€๊ณ„๋ถ€ ๋‚ด์—ญ-์นดํ…Œ๊ณ ๋ฆฌ ์—ฐ๊ฒฐ ํ…Œ์ด๋ธ” (book_line_category)

id flow_category_id
(์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ)
asset_category_id
(์ž์‚ฐ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ)
flow_line_category_id
(ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ)
1 1 2 1

 

๊ธฐ์กด์— ํ•˜๋‚˜๋งŒ ์ƒ์„ฑ๋˜๋˜ ๊ธฐ๋ณธ ์นดํ…Œ๊ณ ๋ฆฌ๊ฐ€ ๊ฐ ๊ฐ€๊ณ„๋ถ€ ๋งˆ๋‹ค ์ƒ์„ฑ๋˜๋„๋ก ๋ณ€๊ฒฝ๋˜๋ฉฐ ์นดํ…Œ๊ณ ๋ฆฌ id๋„ ๋ฐ”๋€Œ์—ˆ๋‹ค. ์ฆ‰ ๊ธฐ์กด ์นดํ…Œ๊ณ ๋ฆฌ์˜ ์ •๋ณด๋กœ ์ƒˆ ์นดํ…Œ๊ณ ๋ฆฌ ํ…Œ์ด๋ธ”์—์„œ id ๋ฅผ ์ฐพ์•„์•ผํ•˜๋Š” ๊ฒƒ์ด๋‹ค. ๋‹คํ–‰์ธ ์ ์€ ์นดํ…Œ๊ณ ๋ฆฌ์—์„œ (์ด๋ฆ„, ์ƒ์œ„ ์นดํ…Œ๊ณ ๋ฆฌ, ๊ฐ€๊ณ„๋ถ€) ์กฐํ•ฉ์ด ์œ ์ผํ•ด์•ผ ํ•˜๋ฏ€๋กœ ์ด ์„ธ ๊ฐ€์ง€๋ฅผ ์ด์šฉํ•ด id๋ฅผ ์ฐพ์œผ๋ฉด ๋œ๋‹ค.

 

 

๋งˆ์ด๊ทธ๋ ˆ์ด์…˜ ์Šคํฌ๋ฆฝํŠธ ์ƒ์„ฑ

๋งˆ์ด๊ทธ๋ ˆ์ด์…˜ ์กฐ๊ฑด์ด ๋ณต์žกํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์ฒ˜์Œ์—๋Š” ์ด๋ฅผ ๋‹ด๋‹นํ•˜๋Š” ์–ดํ”Œ๋ฆฌ์ผ€์ด์…˜์„ ์ƒ์„ฑํ•˜๋ ค๊ณ  ํ–ˆ๋‹ค. ํ•˜์ง€๋งŒ ์ƒˆ ์–ดํ”Œ๋ฆฌ์ผ€์ด์…˜์„ ์ƒ์„ฑํ•˜๊ธฐ ์œ„ํ•ด ์ž‘์„ฑํ•ด์•ผ ํ•  ์ฝ”๋“œ๊ฐ€ ๊ธธ๊ณ , ์ด๋ฏธ ์šด์˜ ์ค‘์ธ ์„œ๋น„์Šค๊ธฐ ๋•Œ๋ฌธ์— ๋งˆ์ด๊ทธ๋ ˆ์ด์…˜ ์‹œ๊ฐ„์„ ์ตœ์†Œํ™”ํ•˜๊ณ  ์‹ถ์—ˆ๋‹ค. ์ถ”๊ฐ€๋กœ ํ˜„์žฌ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์Šคํ‚ค๋งˆ๋ฅผ flyway ๋ฅผ ํ†ตํ•ด ๊ด€๋ฆฌํ•˜๊ณ  ์žˆ์–ด ๋งˆ์ด๊ทธ๋ ˆ์ด์…˜ sql ์Šคํฌ๋ฆฝํŠธ๋ฅผ ์ž‘์„ฑํ•˜๊ธฐ๋กœ ๊ฒฐ์‹ฌํ–ˆ๋‹ค.

 

๊ธฐ๋ณธ์ ์œผ๋กœ flyway๋Š” ์Šคํฌ๋ฆฝํŠธ๋ฅผ ๋ฒ„์ „ ๋ณ„๋กœ ์‹คํ–‰ํ•˜๊ณ , ๊ฐ ์Šคํฌ๋ฆฝํŠธ ๋งˆ๋‹ค ํŠธ๋žœ์žญ์…˜์ด ์ ์šฉ๋œ๋‹ค. ์ค‘๊ฐ„์— ์—๋Ÿฌ๊ฐ€ ๋ฐœ์ƒํ•˜๋ฉด ์ดํ›„ ์Šคํฌ๋ฆฝํŠธ๋Š” ์‹คํ–‰ํ•˜์ง€ ์•Š๋Š”๋‹ค. ์ด๋ฅผ ์ด์šฉํ•ด ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์Šคํฌ๋ฆฝํŠธ๋ฅผ ์ž‘์„ฑํ–ˆ๋‹ค.

 

โœ… ์Šคํฌ๋ฆฝํŠธ 1 - ํ…Œ์ด๋ธ” ์ƒ์„ฑ

  1. ๊ธฐ์กด category & book_line_category ํ…Œ์ด๋ธ” ์ด๋ฆ„ ๋ณ€๊ฒฝ (์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”๊ณผ ์ด๋ฆ„์ด ๊ฒน์นœ๋‹ค.)
  2. ์ƒˆ category & subcategory & book_line_category ํ…Œ์ด๋ธ” ์ƒ์„ฑ

โœ… ์Šคํฌ๋ฆฝํŠธ 2 - ๋ฐ์ดํ„ฐ ์ด๋™

  1. ์ƒˆ category ํ…Œ์ด๋ธ”์— ์ง€์ถœ/์ˆ˜์ž…/์ด์ฒด/์ž์‚ฐ ๋ฐ์ดํ„ฐ ์ƒ์„ฑ
  2. ์ƒˆ subcategory ํ…Œ์ด๋ธ”์— ๊ธฐ๋ณธ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ๊ฐ€๊ณ„๋ถ€ ๋ณ„๋กœ ์ƒ์„ฑ
  3. ๊ธฐ์กด category ํ…Œ์ด๋ธ”์—์„œ ์ƒˆ subcategory ํ…Œ์ด๋ธ”๋กœ ์ปค์Šคํ…€ ํ•˜์œ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ฅผ ์ด๋™
  4. ๊ธฐ์กด book_line_category ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋“ค์„ ์ƒˆ book_line_category ํ…Œ์ด๋ธ”๋กœ ์ด๋™
  5. ๊ธฐ์กด ํ…Œ์ด๋ธ” ์‚ญ์ œ

์ด ๋•Œ book_line_category ๋งˆ์ด๊ทธ๋ ˆ์ด์…˜ ์ฟผ๋ฆฌ๋Š” ๋ณต์žกํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์ œ๋Œ€๋กœ ๋™์ž‘ํ•  ์ง€ ํ™•์‹ ์ด ์—†์—ˆ๋‹ค.

-- ์Šคํฌ์ผ๋Ÿฌ: ์‹คํŒจํ•  ์ฟผ๋ฆฌ์ด๋ฏ€๋กœ ๋”ฐ๋กœ ์„ค๋ช…์„ ๋‹ฌ์ง€ ์•Š์•˜๋‹ค
INSERT INTO `book_line_category`(book_line_id,
                                 line_category_id,
                                 line_subcategory_id,
                                 asset_subcategory_id,
                                 created_at)
    (SELECT oblc.book_line_id,
            linesub.parent_id,
            linesub.id,
            assetsub.id,
            oblc.created_at

     FROM `subcategory` linesub
              INNER JOIN `old_category` old_linesub
                         ON old_linesub.name = linesub.name
                             AND
                            old_linesub.book_id = linesub.book_id
                             AND
                            old_linesub.parent_id = linesub.parent_id
                            
              INNER JOIN `old_book_line_category` oblc
                         ON oblc.category_id = old_linesub.id
                         
              INNER JOIN `old_book_line_category` oblc_asset
                         ON oblc_asset.book_line_id = oblc.book_line_id
                         
              INNER JOIN `old_category` old_assetsub
                         ON old_assetsub.id = oblc_asset.category_id
                         
              INNER JOIN `subcategory` assetsub
                         ON assetsub.name = old_assetsub.name
                             AND
                            assetsub.book_id = old_assetsub.book_id
                             AND assetsub.parent_id =
                                 old_assetsub.parent_id
                                 
     WHERE old_linesub.dtype = 'Book'
       AND old_linesub.parent_id < 4
       AND old_assetsub.dtype = 'Book'
       AND old_assetsub.parent_id = 4);

 

์‹คํŒจ์— ๋Œ€๋น„ํ•ด ์Šคํฌ๋ฆฝํŠธ๊ฐ€ ์‹คํ–‰๋˜๊ธฐ ์ง์ „ DB๋ฅผ ๋ฐฑ์—…ํ–ˆ๋‹ค. RDS๋ฅผ ์‚ฌ์šฉํ•˜๊ณ  ์žˆ์–ด์„œ ์ œ๊ณตํ•˜๋Š” ๋ฐฑ์—… ๊ธฐ๋Šฅ์„ ์‚ฌ์šฉํ–ˆ๋‹ค.

 

 

๋งˆ์ด๊ทธ๋ ˆ์ด์…˜ ์‹คํŒจ์™€ ์žฌ์‹œ๋„

์ „์ฒด ํŒ€์›๋“ค์—๊ฒŒ ๊ฐœ๋ฐœ ์•ฑ์ด ์ž ๊น ์ œ๋Œ€๋กœ ์ž‘๋™ํ•˜์ง€ ์•Š์„ ์ˆ˜ ์žˆ๋‹ค๊ณ  ์•Œ๋ฆฌ๊ณ  ์Šคํฌ๋ฆฝํŠธ๋ฅผ ์‹คํ–‰ํ–ˆ๋‹ค. flyway ๋กœ๊ทธ๋ฅผ ์‚ดํ”ผ๋‹ˆ ๋‹คํ–‰ํžˆ ์ค‘๊ฐ„์— ์—๋Ÿฌ๊ฐ€ ๋‚˜์ง€๋Š” ์•Š์•˜๋‹ค. ๊ทธ๋ž˜์„œ ์„ฑ๊ณตํ–ˆ๋‚˜? ์‹ถ์—ˆ๋Š”๋ฐ ๋ฌธ์ œ์˜ ์ฟผ๋ฆฌ๊ฐ€ ์ž˜๋ชป ์ž‘์„ฑ๋˜์–ด book_line_category ๋ฐ์ดํ„ฐ๊ฐ€ ์ œ๋Œ€๋กœ ์ด์ „๋˜์ง€ ์•Š์•˜๋‹ค.

์–ด๋””๋กœ ๊ฐ”๋‹ˆ...

๋ฐฑ์—…ํ•œ DB์—์„œ ๋ฐ์ดํ„ฐ๋งŒ ๋”ฐ์™€(mysqldump ์ด์šฉ) ๋กœ์ปฌ์—์„œ ๋‹ค์‹œ ํ…Œ์ŠคํŠธํ•ด๋ณด๋ฉฐ ์ƒˆ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ–ˆ๋‹ค.

 

insert into `book_line_category` (book_line_id,
                                  line_category_id,
                                  line_subcategory_id,
                                  asset_subcategory_id,
                                  created_at)
    (select bl.id,
            line_sub.parent_id,
            line_sub.id,
            asset_sub.id,
            old_line_sub_blc.created_at

     from old_book_line_category old_line_blc

              -- 3๊ฐœ์˜ ํ–‰์œผ๋กœ ๋‚˜๋‰œ ๊ธฐ์กด book_line_category๋ฅผ ํ•˜๋‚˜์˜ ํ–‰์œผ๋กœ JOIN
              inner join old_book_line_category old_line_sub_blc
                         on old_line_sub_blc.book_line_id = old_line_blc.book_line_id
              inner join old_book_line_category old_asset_sub_blc
                         on old_asset_sub_blc.book_line_id = old_line_sub_blc.book_line_id

         -- book์˜ id๋ฅผ ์ฐพ๊ธฐ ์œ„ํ•œ JOIN
              inner join book_line bl
                         on bl.id = old_line_blc.book_line_id

         -- ๊ธฐ์กด category์˜ id๋ฅผ ์ฐพ๊ธฐ ์œ„ํ•œ JOIN
              inner join old_category old_line_sub
                         on old_line_sub.id = old_line_sub_blc.category_id
              inner join old_category old_asset_sub
                         on old_asset_sub.id = old_asset_sub_blc.category_id

         -- (์ด๋ฆ„, ์†ํ•œ ๊ฐ€๊ณ„๋ถ€, ๋ถ€๋ชจ ์นดํ…Œ๊ณ ๋ฆฌ)๋ฅผ ๋ฐ”ํƒ•์œผ๋กœ ์ƒˆ๋กœ์šด category์˜ id ๋ฅผ ์ฐพ๊ธฐ ์œ„ํ•œ JOIN
              inner join subcategory line_sub
                         on line_sub.name = old_line_sub_blc.name
                             and line_sub.parent_id = old_line_blc.category_id
                             and line_sub.book_id = bl.book_id
              inner join subcategory asset_sub
                         on asset_sub.name = old_asset_sub_blc.name
                             and asset_sub.parent_id = 4
                             and asset_sub.book_id = bl.book_id

          -- 3๊ฐœ์˜ ํ–‰์œผ๋กœ ๋‚˜๋‰œ ๊ธฐ์กด book_line_category๋ฅผ ํ•˜๋‚˜์˜ ํ–‰์œผ๋กœ JOIN ํ•˜๋Š” ์กฐ๊ฑด
     where old_line_blc.book_line_categories_key = 'FLOW'
       and old_line_sub_blc.book_line_categories_key = 'FLOW_LINE'
       and old_asset_sub_blc.book_line_categories_key = 'ASSET');

 

๋‹คํ–‰ํžˆ ์ƒˆ ์ฟผ๋ฆฌ๋Š” ์ž˜ ๋™์ž‘ํ–ˆ๋‹ค.

 

 

 

 

 

๐Ÿคฏ ์†Œ๊ฐ

์„œ๋ฒ„ํŒ€์˜ ํฐ ์ด์Šˆ๋ฅผ ๋“œ๋””์–ด ๋๋ƒˆ๋‹ค. ์ฝ”๋“œ๋งŒ ํ•ด๋„ ์ตœ์†Œ 9์ฒœ์ค„์ด ๋ณ€๊ฒฝ๋œ ๋Œ€๊ณต์‚ฌ์˜€๋‹ค.

 

์ตœ์„ ์˜ ์„ค๊ณ„๋Š” ์–ด๋–ป๊ฒŒ ํ• ๊นŒ?

์ง€๊ธˆ ๋ ˆ๊ฑฐ์‹œ๋กœ ์ทจ๊ธ‰ํ•˜๊ณ  ๋ณ€๊ฒฝํ•œ ์ด์ „ ์„ค๊ณ„๋Š” ๊ทธ ๋‹น์‹œ์—์„  ์ตœ์„ ์œผ๋กœ ์—ฌ๊ฒผ๋˜ ์„ค๊ณ„๋‹ค. ๊ทธ๋ ‡๋‹ค๋ฉด ์ง€๊ธˆ ์„ค๊ณ„๋„ ๋ฏธ๋ž˜์— ๋ ˆ๊ฑฐ์‹œ๊ฐ€ ๋˜์ง€ ์•Š์„๊นŒ? 

 

์š”์ฆ˜์€ ์žฅ์•  ์ƒํ™ฉ์ด ์ผ์–ด๋‚˜์ง€ ์•Š๋„๋ก 100% ์˜ˆ๋ฐฉํ•˜๋Š” ๊ฑด ๋ถˆ๊ฐ€๋Šฅํ•˜๋‹ˆ, ์žฅ์•  ์ƒํ™ฉ์ด ์ผ์–ด๋‚ฌ์„ ๋•Œ ๋น ๋ฅด๊ฒŒ ๋ณต๊ตฌ๋˜๋„๋ก ์„œ๋น„์Šค๋ฅผ ๊ฐœ๋ฐœํ•œ๋‹ค๊ณ  ํ•œ๋‹ค. ๋„๋ฉ”์ธ์ด ์–ด๋–ป๊ฒŒ ๋ณ€๊ฒฝ๋  ์ง€ 100% ์˜ˆ์ธกํ•˜๋Š” ๊ฑด ๋ถˆ๊ฐ€๋Šฅํ•˜๋‹ˆ ์ง€๊ธˆ์˜ ์„ค๊ณ„๋„ ์ถ”ํ›„ ๋ ˆ๊ฑฐ์‹œ๊ฐ€ ๋  ๊ฐ€๋Šฅ์„ฑ์ด ์žˆ๋‹ค. ๊ทธ๋ ‡๋‹ค๋ฉด ๊ทธ ๋•Œ ์ตœ๋Œ€ํ•œ ์•ˆ์ •์ ์œผ๋กœ ๋ณ€๊ฒฝํ•  ์ˆ˜ ์žˆ๋„๋ก ๋Œ€๋น„ํ•˜๋ฉด ๋˜์ง€ ์•Š์„๊นŒ? ๋Œ€๋น„์ฑ…์œผ๋กœ๋Š” ์•„์ง ์ธ์ˆ˜ ํ…Œ์ŠคํŠธ ์ถ”๊ฐ€๋งŒ ์ƒ๊ฐ๋‚˜๋Š”๋ฐ(๊ฐœ๋ฐœ ์™ธ์—๋Š” QA ์‹œ๋‚˜๋ฆฌ์˜ค ์ •๋ฆฌ?)... ์•„์ง์€ ์ž˜ ๋ชจ๋ฅด๊ฒ ๋‹ค.

 

๋ ˆ๊ฑฐ์‹œ๋Š” ๋นจ๋ฆฌ ์น˜์šฐ์ž

์‚ฌ์‹ค ์ด๋ฒˆ์—๋Š” ์•Œ๊ณ ๋„ ์ผ์ •์— ๋งž์ถ”๋Š๋ผ ๋ชป ์น˜์› ์ง€๋งŒ... ์—ญ์‹œ ๊ฐœ๋ฐœํ•˜๋ฉด์„œ ๊ฑธ๋ฆฌ๋Š” ์š”์†Œ๋Š” ์ตœ๋Œ€ํ•œ ๋นจ๋ฆฌ ํ•ด๊ฒฐํ•ด์•ผ๊ฒ ๋‹ค. ๋ณ€๊ฒฝํ•  ์ฝ”๋“œ๊ฐ€ ๋งŽ์•„์ง€๊ธฐ๋„ ํ•˜์ง€๋งŒ ์„œ๋น„์Šค๋ฅผ ์šด์˜ํ•˜๋‹ˆ ์†๋Œˆ ๋ฐ์ดํ„ฐ๋„ ๋งŽ์•„์ง„๋‹ค.