SQL과 연관지어본 Airtable: linked records, lookup, count, rollup 필드

에어테이블(Airtable)은 기본적으로 관계형 데이터베이스입니다. 요즘에는 비정형 데이터와 빅데이터가 부상하면서 NoSQL 데이터베이스도 많이 쓰이지만, 여전히 관계형 데이터베이스는 매우 중요합니다. 에어테이블을 사용하기 위해 데이터베이스의 개념이나 질의 언어인 SQL을 알 필요는 전혀 없습니다. 그러나, 기존에 SQL을 조금이라도 써왔던 분들은, SQL과 연관시켜서 에어테이블의 주요 개념들을 좀 더 쉽게 이해할 수 있을 것입니다.

에어테이블에서 한 개의 데이터베이스를 베이스(base)라고 부릅니다. 그리고 베이스 안에는 여러 개의 테이블(table)을 담을 수 있습니다. 다시 테이블은 여러 개의 다른 방식으로 표현된 뷰(view)를 가질 수 있습니다. 이 때 기본적으로 테이블과 테이블이 관계를 맺으면(에어테이블에서 link to라고 표현함. SQL에서 말하는 join), 현재의 테이블에 관계 맺어진 다른 테이블의 값을 불러오거나(lookup), 가져와서 요약 정리하거나(rollup), 갯수를 셀 수(count) 있습니다. 이 글에서는, 관계형 데이터베이스를 설명하려는 것은 아니므로, 간단히 에어테이블에서 이런 것들이 어떤 필드로 표현되는지, 그것을 SQL로 표현하면 어떻게 대응되는지만 살펴봅니다.

어떤 예제로 설명을 해야 하나 고민하다가, 그냥 제가 개인적으로 사용하고 있는 독서 목록이라는 베이스를 사용하기로 했습니다. 읽은 책들을 정리하기 위한 베이스의 구조는 아주 간단합니다. 책 목록과 작가 목록 두 개의 테이블로 되어 있고, 책의 저자가 작가의 이름과 연결(link)되어 있습니다. 책 목록에 같은 작가가 쓴 책이 여러 권 있을 때, 작가 이름을 매번 새롭게 치는 것이 아니고, 작가 테이블에서 불러온다는 것입니다.

예시 (데이터)베이스

예전부터 공개된 독서 카드(특정 뷰)

에어테이블에서는 미리 테이블 안에 linked records 필드 (또는 link to 필드), lookup 필드, count 필드, rollup 필드를 만들어놓게 되므로, 컬럼(필드) 수가 다소 많아지게 됩니다. 그래서, 필요 없는 컬럼은 숨기기하면서 목적에 맞는 여러 개의 뷰(view)를 만들면 됩니다.

실제로는 아래 두 개의 테이블명과 필드명으로 한글을 사용했습니다. 다만, 일반적인 데이터베이스에서 한글을 테이블과 필드 이름으로 사용하는 데에는 제약이 따르므로, 여기 예시에서는 편의상 영문으로 합니다.

Books 테이블 구조

필드명	필드 유형	비고
Book_title	Single line text	Primary field
Author	Linked records	저자 테이블과 연결
Rating	Rating	1점~5점
About_author	Lookup	저자 테이블에서 인물소개 값을 참조

Authors 테이블 구조

필드명	필드 유형	비고
Author_name	Single line text	Primary field
Description	Long text	저자에 대한 소개글
Books	Linked records	해당 저자가 쓴 책 목록. 책 테이블과 연결
N_of_books	Count	이 작가의 책이 책 테이블에 몇 개 있는지 셈
Average_rating	Rollup	이 작가가 쓴 책의 평균 별점 계산

Linked records 필드

에어테이블 확장 프로그램인 Base schema를 이용해 독서 목록이라는 베이스의 스키마를 시각적으로 표현한 그림. 두 테이블 사이에 Linked records 필드만 보여주도록 설정한 경우

Linked records 필드는 두 개의 테이블을 연결하는 것입니다. Books 테이블에서 저자는, Authors 테이블에 있는 저자명과 연결됩니다. 에어테이블에서는 한 테이블에서 Link to 필드를 만들면, 자동으로 다른 테이블에도 Link to 필드가 생깁니다. 다시 말해, Books 테이블의 저자를 Authors 테이블의 저자명과 연결하면, 거꾸로 Authors 테이블에는 Books 테이블의 책이름과 연결되는 Link to 필드가 자동 생성되어, 해당 저자가 쓴 책 목록이 모두 표시됩니다. Books 테이블에서는 글쓴이가 보여지는 방식을 SQL로 표현하면 다음과 같이 되겠지요.

SELECT b.book_title, a.author
FROM books b
INNER JOIN authors a ON b.author = a.author_name;

Lookup 필드

Lookup 필드는 연결된 다른 테이블에서 특정 필드 값을 조회해서 가져오는 것입니다. Books 테이블에서는 매번 저자의 인물 소개가 들어가지 않지만, Authors 테이블에서 Description의 값을 조회해서 보여주는 방식입니다.

SELECT b.book_title, b.author, a.description AS about_author
FROM books b
INNER JOIN authors a ON b.author = a.author_name;

Count 필드

에어테이블에서 Count라는 유형의 필드는 실제 SQL에서 count 함수를 쓰는 것과 비슷합니다. 아래 쿼리는 저자별로 Books 테이블에 몇 권의 책이 있는지 세어서 보여줍니다.

SELECT a.author_name, COUNT(*) AS n_of_books
FROM books b INNER JOIN authors a 
ON b.author = a.author_name
GROUP BY a.author_name;

Rollup 필드

에어테이블의 Rollup 필드는 연결된 다른 테이블의 특정 필드를 요약해서 보여줄 때 사용합니다. 위에서 저자별로 여러 권의 책이 있고, 각 책들의 별점이 있는데, 저자별로 모든 책들의 별점의 평균을 보여주기 위해 Authors 테이블에 Rollup 필드를 넣었습니다. Rollup 필드는 평균 외에도, 합계, 갯수, 최대값, 최소값, 문자열 함수, 논리 함수 등 여러 가지를 사용해서 요약할 수 있습니다.

에어테이블의 평균별점 필드의 수식은 다음과 같이 정의됩니다. 별점이 있는 경우, 있는 레코드만 모아서 평균을 보여달라는 것이죠.

IF(values, AVERAGE(values))

SQL에서 저자와 저자별 별점 평균을 보여주려면 다음과 같이 하면 되겠죠. (소수 첫째 자리까지만)

SELECT a.author_name, ROUND(AVG(b.rating), 1) AS average_rating
FROM books b
INNER JOIN authors a ON b.author = a.author_name
GROUP BY a.author_name;

제가 겪은 실무에서는, 단순하게 lookup, rollup, count를 쓰기보다는 조건부(conditional) 필드를 사실 더 많이 쓰게 됩니다. 에어테이블에서는 상당히 직관적으로 코딩 없이 조건부 lookup, rollup, count를 사용할 수 있고, 그것에 따라 여러 개의 뷰를 만들 수 있습니다. 그리고 각각의 뷰에서 할 수 있는 일을 제한해놓습니다. 그러면, 데이터베이스나 프로그래밍에 대한 지식이 거의 없어도, 상당히 복잡한 업무 처리를 이것만으로 구현하게 됩니다.