クラスター化テーブルインデックスによる継承表現

2024-04-02

SQL Serverで継承を表現する方法

SQL Serverでは、テーブル間の親子関係を表現する「継承」機能は直接提供されていません。しかし、いくつかの代替方法を用いることで、継承関係を模倣することができます。

代替方法

テーブル階層

最も単純な方法は、テーブル階層を作成することです。親テーブルには共通属性、子テーブルには固有属性を定義します。

-- 親テーブル
CREATE TABLE Person (
  PersonID int PRIMARY KEY,
  FirstName varchar(50),
  LastName varchar(50)
);

-- 子テーブル
CREATE TABLE Employee (
  EmployeeID int PRIMARY KEY,
  PersonID int FOREIGN KEY REFERENCES Person(PersonID),
  JobTitle varchar(50)
);

-- 子テーブル
CREATE TABLE Customer (
  CustomerID int PRIMARY KEY,
  PersonID int FOREIGN KEY REFERENCES Person(PersonID),
  Address varchar(100)
);

長所

シンプルで理解しやすい

短所

データ冗長が発生する
更新処理が複雑になる

ビュー

ビューを用いて、継承関係を仮想的に表現することができます。

CREATE VIEW EmployeeDetails AS
SELECT
  p.PersonID,
  p.FirstName,
  p.LastName,
  e.JobTitle
FROM Person p
INNER JOIN Employee e ON p.PersonID = e.PersonID;

更新処理がシンプル

ビューは実際のテーブルではない
すべての操作がサポートされているわけではない

ストアドプロシージャ

CREATE PROCEDURE GetEmployeeDetails
  @PersonID int
AS
BEGIN
  SELECT
    p.PersonID,
    p.FirstName,
    p.LastName,
    e.JobTitle
  FROM Person p
  INNER JOIN Employee e ON p.PersonID = e.PersonID
  WHERE p.PersonID = @PersonID;
END

複雑なロジックをカプセル化できる
柔軟性が高い

開発コストが高い
パフォーマンスのチューニングが必要

シンプルな関係であれば、テーブル階層が最適です。
データ冗長を避けたい場合は、ビューまたはストアドプロシージャを使用します。
複雑なロジックを扱う場合は、ストアドプロシージャが最適です。

補足

SQL Server 2016 以降では、JSON 型を使用することで、オブジェクト指向的なデータモデルを表現することができます。

将来のバージョンでは、継承機能が正式に提供される可能性があります。

SQL Serverで継承を表現するには、いくつかの代替方法があります。どの方法を選択するべきかは、具体的な要件によって異なります。

テーブル階層

-- 親テーブル
CREATE TABLE Person (
  PersonID int PRIMARY KEY,
  FirstName varchar(50),
  LastName varchar(50)
);

-- 子テーブル
CREATE TABLE Employee (
  EmployeeID int PRIMARY KEY,
  PersonID int FOREIGN KEY REFERENCES Person(PersonID),
  JobTitle varchar(50)
);

-- 子テーブル
CREATE TABLE Customer (
  CustomerID int PRIMARY KEY,
  PersonID int FOREIGN KEY REFERENCES Person(PersonID),
  Address varchar(100)
);

-- INSERT 文
INSERT INTO Person (FirstName, LastName) VALUES ('John', 'Doe');
INSERT INTO Employee (PersonID, JobTitle) VALUES (1, 'Software Engineer');
INSERT INTO Customer (PersonID, Address) VALUES (1, '123 Main Street');

-- SELECT 文
SELECT
  p.FirstName,
  p.LastName,
  e.JobTitle
FROM Person p
INNER JOIN Employee e ON p.PersonID = e.PersonID;

-- 結果
-- John Doe
-- Software Engineer

ビュー

CREATE VIEW EmployeeDetails AS
SELECT
  p.PersonID,
  p.FirstName,
  p.LastName,
  e.JobTitle
FROM Person p
INNER JOIN Employee e ON p.PersonID = e.PersonID;

-- SELECT 文
SELECT * FROM EmployeeDetails;

-- 結果
-- PersonID | FirstName | LastName | JobTitle
-- -------- | -------- | -------- | --------
-- 1        | John      | Doe      | Software Engineer

ストアドプロシージャ

CREATE PROCEDURE GetEmployeeDetails
  @PersonID int
AS
BEGIN
  SELECT
    p.PersonID,
    p.FirstName,
    p.LastName,
    e.JobTitle
  FROM Person p
  INNER JOIN Employee e ON p.PersonID = e.PersonID
  WHERE p.PersonID = @PersonID;
END

-- EXECUTE 文
EXEC GetEmployeeDetails @PersonID = 1;

-- 結果
-- PersonID | FirstName | LastName | JobTitle
-- -------- | -------- | -------- | --------
-- 1        | John      | Doe      | Software Engineer

SQL Serverで継承を表現するその他の方法

クラスター化テーブルインデックス (CTI)

CTI を使用して、親テーブルと子テーブルを1つのテーブルにまとめることができます。

CREATE TABLE Person (
  PersonID int PRIMARY KEY,
  FirstName varchar(50),
  LastName varchar(50),
  Discriminator int
);

CREATE TABLE Employee (
  EmployeeID int PRIMARY KEY,
  JobTitle varchar(50)
) WITH (CLUSTERED INDEX (PersonID));

-- INSERT 文
INSERT INTO Person (FirstName, LastName, Discriminator) VALUES ('John', 'Doe', 1);
INSERT INTO Employee (EmployeeID, JobTitle) VALUES (1, 'Software Engineer');

-- SELECT 文
SELECT
  p.FirstName,
  p.LastName,
  e.JobTitle
FROM Person p
INNER JOIN Employee e ON p.PersonID = e.EmployeeID
WHERE p.Discriminator = 1;

-- 結果
-- John Doe
-- Software Engineer

クエリが複雑になる

エンティティフレームワークなどのORMツールを使用すると、オブジェクト指向的なモデルをデータベースにマッピングすることができます。

public class Person
{
  public int PersonID { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

public class Employee : Person
{
  public int EmployeeID { get; set; }
  public string JobTitle { get; set; }
}

using (var context = new MyContext())
{
  var employee = new Employee
  {
    FirstName = "John",
    LastName = "Doe",
    JobTitle = "Software Engineer"
  };

  context.Employees.Add(employee);
  context.SaveChanges();
}