Её величество Плоская таблица

Понимание архитектуры таблицы позволяет лучше работать с данными. В настоящее время скорость и качество работы с данными - ключевой фактор успеха.

Каждый день нам приходится проектировать таблицы. Это может быть список продуктов, учет продаж в магазине, данные по расходам группы компаний и т.д. Правильное понимание сути таблицы позволяет правильно их проектировать.

Кроме теоретического понимания необходимы практические инструменты, которые помогут частично автоматизировать архитектурный анализ таблицы.

Почему именно таблица? И как она может быть плоской? Я работаю в сфере бизнес-консалтинга. Со временем мне удалось выделить три "структуры данных", которые играют ключевую роль в нашей сфере:

Плоская таблица - самая фундаментальная структура данных в бизнесе, по моей оценке

Иерархия (дерево) - по сути, это частный случай графа, но имеет много особенностей, поэтому выделяется отдельно. В области бизнеса так удобнее

Граф

Если вы откроете почти любой Excel файл, там будет нечто похожее на таблицы. В Word, Power Point также частенько вставляют таблицы. Реляционные БД - чистые плоские таблицы. Кубические БД - можно логически представить в виде таблицы. Даже иерархии и графы часто физически хранятся в табличном виде. Именно поэтому, в бизнесе, самый большой из трёх китов - плоская таблица. В этой статье мы подробнее рассмотрим именно её.

Термин "плоская таблица" подчеркивает разницу с просто таблицами:

Плоская таблица - это классическая таблица (а-ля SQL table) в первой нормальной форме (1NF). Однострочная шапка, атомарные типы данных. В общем красота. Когда вам дают такую таблицу, вы сразу можете приступить к интересной и полезной стадии анализа.

Таблица (в общем случае) - это всё, что человек считает таблицей. Таблицы бывают матричные, двойные матричные, с многострочными шапками, неатомарными типами данных в теле и т.д. Если вам дали такую таблицу, вы потратите от 15 минут до нескольких дней на перевод её в понятный, плоский вид. Иногда этот перевод вообще невозможен, по причине архитектурных ошибок или качества исходных данных.

Архитектура плоской таблицы Введем основные термины.

Потенциальные ключи таблицы (candidate key) - это наборы столбцов, которые обеспечивают уникальность каждой строчки таблицы. Больше техническое понятие. Полный набор потенциальных ключей интереса не представляет.

Первичный ключ таблицы (primary key) - это то, про что рассказывает плоская таблица. Каждая строчка содержит один объект. Каждая строчка рассказывает что-то про этот объект. Первичный ключ обеспечивает уникальность каждой строчки. Т.е. первичный ключ - это один из потенциальных ключей. К сожалению, далеко не все, кто проектируют таблицы, задумываются о первичном ключе. А без этого никуда.

Функциональные зависимости (functional dependencies). Приведу пример. Есть справочник должности, а есть справочник сотрудники. Допустим, что каждый сотрудник имеет строго одну должность. Таким образом, сотрудник функционально определяет должность. Обратное, обычно, неверно. Т.е. одна должность может быть у многих сотрудников. В бизнесе принято говорить, что должность - это атрибут/реквизит/группировка для справочника сотрудников. Но суть дела не меняется.

В функциональной зависимости есть детерминант (определитель) и цель. Детерминант -> Цель. Сотрудники - детерминант, должности - цель.

А теперь представим, что нам для анализа дали хорошую плоскую таблицу. Как нам можно составить общее представление о ней (понять архитектуру таблицы). Ниже четыре вопроса, которые я задаю чаще всего:

1. Что является первичным ключом таблицы?

Плоская таблица - это рассказ про первичный ключ.

Это всегда мой первый вопрос, когда я вижу плоскую таблицу. В ряде случаев ответ очевиден, но иногда требуется проверка или даже поиск первичного ключа. Иногда ключа вообще нет. Иногда заявления о первичном ключе не подтверждаются фактическим состоянием таблицы.

2. Какие функциональные зависимости есть в таблице?

В каком-то смысле, функциональная зависимость - основа информации. Т.е. именно отношение между двумя множествами - это и есть информация. Например, фраза "каждый сотрудник имеет ровно одну должность" означает два множества и функциональную зависимость между ними. Сотрудник -> Должность.

Понимание функциональных зависимостей позволяет лучше понять данные и предметную область на основе точных метрик (есть или нет функциональная зависимость). Это простой и эффективный способ архитектурного анализа данных.

3. Как соотносятся два столбца в таблице?

Этот вопрос задается реже, обычно он нужен, чтобы детально изучить отношение между двумя столбцами (либо двумя наборами столбцов).

Столбцы могут соотноситься как: 1-1, 1-N, N-1, N-N (логика как в ER-диаграммах). Сотрудники и должности соотносятся как N-1.

Кроме того, существует ряд более детальных метрик связи между двумя столбцами. Например, типы и количество компонент связности двудольного графа (соотношение двух столбцов, по сути, образуют двудольный граф). Но это уже глубокий архитектурный анализ, который нужен не часто.

4. Чем функционально определяется данный столбец?

Обычно мы не можем выявить все функциональные зависимости в таблицы. Т.к. это долго. Обычно выявляют только те, где детерминант и цель содержат по одному столбцу. Это можно посчитать сразу полностью. Ниже в кейсе мы так и сделаем и представим результат в виде графа.

Но часто интерес представляет конкретный столбец. Можно задаться вопросом, какие наборы столбцов функционально определяют наш целевой столбец. Это, опять-таки, позволяет лучше понять архитектуру данных, проверить гипотезы, выявить противоречия в данных.

Несколько слов про нормальные формы "по-простому" Теперь мы готовы к пониманию первых трех нормальных форм (NF) в которых может находиться таблица.

1NF - это однострочная шапка, атомарные значения, без дубликатов строк, без пустых строк и столбцов. Ну т.е. минимальные требования к формату. Это и есть плоская таблица.

2NF и 3NF требуют соблюдения более тонкого принципа: "Таблица - рассказ про первичный ключ". Если какая-то колонка рассказывает про часть первичного ключа - нарушается нормальная форма. Если одна неключевая колонка рассказывает про другую неключевую колонку - опять же принцип нарушается.

В общем, первые три нормальные формы логичны. Помнить про них надо всегда. А вот соблюдаются они не всегда, реальные данные часто требуют денормализацию. Нормальные формы выше третьей интересны только с теоретической точки зрения.

Инструменты помогающие понять архитектуру плоской таблицы Технически можно применять следующие инструменты:

MS Excel

Если нет ничего другого, используется чистый Excel, либо его аналог. Его преимущество в том, что он простой и позволяет быстро проверять простые гипотезы. Например, является ли этот набор столбцов уникальным. Но, в целом, это неэффективный инструмент для такого класса задач.

Pivot table (MS Excel)

Сводные таблицы Excel - это уже значительно более сильный инструмент. С ними удобно проверять интересные гипотезы средней сложности. Например, существует ли функциональная зависимость между двумя наборами столбцов. Но это все делается руками, так что много гипотез не проверишь.

Python

В сложных случаях требуется python. Интересно, но я смог найти только одну библиотеку, которая выполняет подобный архитектурный анализ из коробки (git, article). FDtool написан на python2. Видно, что библиотека не поддерживается её создателями. Что расстраивает(

Я решил написать велосипед свою версию, которая бы сделала архитектурный анализ удобнее. Код выложен на github. Ниже в кейсе я покажу применение инструмента на реальной таблице.