データベースの列定義について

Question

今現在開発を進めているデータベースでは、
テーブルが異なっていても同一列名の場合、型・サイズ・和名を揃えるようにしています。

ただ、他のプロジェクトでは同様のことを行っているところはなく、
ネットで調べた場合でも、そこまで厳密に定義した方が良いと書いているページは見つけられませんでした。

次期プロジェクトでも同様の方針でデータベース設計を行うつもりでしたが、
こういった設計は実は推奨されていない等はあるのでしょうか？

###実際に開発した際のメリット/デメリット
メリット
・DBから全テーブルの列定義を取得し、画面の入力(maxLengthや名称)等に反映出来る。
・上記理由から、桁数変更程度ならDBの列修正のみで対応できる。

デメリット
・同名の定義を行いたいが、和名だけ別なものにする場合に、それに見合う英名を探しに行く必要がある。
・後の保守要員に周知徹底出来るか疑問。
・サイズや型が異なる同名の列定義をした場合、同名の別画面部品に対して入力を行って初めて発覚する。

###例

create table Customer {
  id NUMBER(10) NOT NULL, -- id
  name VARCHAR2(30) ,  -- 名前  
  remark VARCHAR2(1000) , -- 備考
}
create table Producer {
  id NUMBER(10) NOT NULL, -- id
  name VARCHAR2(30) ,  -- 名前
  remark VARCHAR2(1000) , -- 備考
}

仮に上記のテーブルのコメントそれぞれ変更したい場合は、以下のように定義しています。

create table Customer {
  id NUMBER(10) NOT NULL, -- id
  customer_name VARCHAR2(30) ,  -- 顧客名  
  remark VARCHAR2(1000) , -- 備考
}
create table Producer {
  id NUMBER(10) NOT NULL, -- id
  producer_name VARCHAR2(30) ,  -- 生産者名
  remark VARCHAR2(1000) , -- 備考
}

また、列の定義を変更する場合は、以下のように定義しています。

create table Customer {
  id NUMBER(10) NOT NULL, -- id
  customer_name VARCHAR2(30) ,  -- 顧客名  
  remark VARCHAR2(1000) , -- 備考
}
create table Producer {
  id NUMBER(10) NOT NULL, -- id
  producer_name VARCHAR2(30) ,  -- 生産者名
  producer_remark VARCHAR2(500) , -- 備考(生産者)
}

Answer 1

同名であっても、管理するモノが違えば適切な型・桁数は変わってくるはずですので、カラム名が同じだからすべて同じ定義、と言うのは横暴な気がしますね。
「mail」等、どこで使われても同じモノもあるでしょうが、「name」等の場合は対象によって随分勝手が違うと思います。
「パシフィックゴルフグループインターナショナルホールディングス株式会社」で34文字ですが、人名で34文字も必要か？となると、違うと思いますし、同じにするのであれば無駄にサイズを食ってしまうワケで。

社内開発でメンバーがほぼ変わらないという場合で、その手法に慣れている、そしてその規則に応じたライブラリが整備されているというような状況であればそのまま使った方がいいかもしれませんが。

Comments

[cha-ra]

＞「name」等の場合は対象によって随分勝手が違うと思います。
　「パシフィックゴルフグループインターナショナルホールディングス株式会社」で34文字ですが、人名で34文字も必要か？となると、違うと思いますし、同じにするのであれば無駄にサイズを食ってしまうワケで。

もちろん「人名」と「会社名」で同一のサイズを扱う訳にはいきませんから、
この場合ですと「person_name」と「company_name」で列名を分けて管理します。
いつか１つのテーブル内で、一緒に管理することになった場合(名称等では考えにくいですが)、
同じ列名では定義出来ずに新しい列名をそれぞれ振ることになると思います。
それなら最初からそのように定義してある方が後々楽かなと考えております。
もちろん列名が長くなるので煩わしさはありますが。。。

Answer 2

先ずはじめに明確な回答とはなりませんがご容赦ください。

先ず始めに個人的な意見を言ってしまうと、
カラム･項目名の統一の是非はケースバイケースと考えています。

#カラム名統一のメリットと命名指針について
カラム名を統一するメリットについてですが、
質問者さんが上げられている点に加えると自然結合やUSINGを用いた結合が可能という点があります。

自然結合のNATURAL JOINはほぼほぼお目にかかることはないので、
まだ見かける機会のあるUSINGを利用した結合例を下記に記載します。

SELECT
    e.employee_id
,   p.payment_month
FROM
    Employees e
    INNER JOIN Payments p
      USING(employee_id)   /* 同名カラム同士ではUSING句が使える。※SQL Serverは未サポート */

またカラム名を統一する場合は、何らかのプリフィックスが付く(costomer_idとかcostomer_name)のはなかなか避けては通れないかと思われますが、
これに対して冗長という指摘はやはり多いかと思います。

確かに若干な冗長性は発生しますが、
テーブル名は集合となるから命名は原則複数形というルール(「Costomer」よりは「Customers」)を守っていれば、その中の1つを識別する「customer_id」の接頭辞は厳密には意味が異なってきます。

逆にid、nameのように接頭辞を付けない命名ルールだと、
SELECT句内でそれぞれに列別名を指定する手間が発生するのでどちらも一長一短というのが私的見解です。
(※純粋なデータベースに限っての話で、ORマッピングとかフレームワークが絡むと少し話が変ってきますが）

#カラム名・型を統一すべきでないケース
他の回答者さんも言及してますが、
何でもかんでも統一すれば良いという訳でもありません。
同じ項目でも使用状況や目的が異なる場合は無理に一致させようとするのは逆によろしくないです。

目的が異なる場合は素直に型の不一致を認めることもまた必要なケースもあるかなと思われます。

#上記ケースの使い分け方
結合キーとなりうるものについては、
カラム名と型まで一致させるというのが一番スマートな方法な気がします。
(個人的には結合キーとなりうる項目は統一出来ればというのが理想・・・)

逆に結合キーとなりえない他の従属項目については、
無理に統一する必要はないのかなと個人的に思います。
無理に合わせようと試行錯誤する時間と労力の方がもったいない気はしますしね・・・＾＾；

・蛇足
Orlofskyさんがお勧めしている「A5:SQL Mk-2」は当方もお勧めします。
DB定義書・ER図などの作成がさっくりできる、
エビデンスとして結果をEXCELに貼り付けた際に、
カラム名を論理名として貼り付ける設定などもできたりと凄まじい利便性です。

Answer 3

cha-ra さんの考えは正しいです。特にSQLのWHERE句で等号で比較される列はデータ型が違っていると暗黙の型変換が発生して、その列に索引が設定されていても索引が使われずに暗黙の型変換のために全件走査した結果と比較されるため、件数の多いテーブルでは極端にパフォーマンスが落ちます。(Oracleのパフォーマンス・チューニングでよく経験しています)
ドメイン(定義域)とは
きれいなデータベース設計を効率的に行う
何百何千とテーブルがあってもERツールを使わずにテーブル設計して(担当者の忍耐力はすごいが)、後でパフォーマンス・チューニングに多くの時間と費用をかけなければならないお客様が多いです。

Comments

[cha-ra]

>データ型が違っていると暗黙の型変換が発生して
[同名で型が違う]場合に比較すると大抵は検索エラーになると思いますが、そういうことではなく[同名で型は同じだが、サイズ(桁数)が異なる]場合のことでしょうか？

関わっているプロジェクトはどこも小規模で100～200テーブル程度のばかりで、
ERツールを利用したことはありませんでしたが、
紹介してくださったOBERは現在の開発プロジェクトで是非とも導入したかったです。
重複のチェック等はDBから全列情報を引っ張って、rownum>1で調べていたので、
こういうツールがあると、後任に委譲する際にもとても助かります。

[Orlofsky]

同じ物理列名でデータ型が同じで桁数が違うと桁あふれの可能性があります。
テーブル定義書が最新の状態で維持されているお客さまも少ないので、Oracleのパフォーマンス・チューニングで呼ばれると、物理列名、データ型、桁数、物理テーブル名、論理列名のリストをディクショナリ・ビューから作成しなければならないことも多いです。

無償のERツールとしてはA5:SQL Mk-2の評価が高いようです。
http://forest.watch.impress.co.jp/library/software/a5sqlmk2/
これだけのツールを無償公開する方はすごいです。寄付歓迎のとことです。
時間があったら試用されては？

Answer 4

型と桁数は用途によって異なるでしょうから合わせる必要はないですし、合わせるのは無理です。
名前に関してはわかりやすい名前にすれば良いだけで、例えテーブルが異なっても同じ名前で構わないと思います。
ただ、producer_nameはテーブル名がproducerなので冗長に感じます。
個人的には受け付けません。
これやるならidもproducer_idにしないとってことなりますね。

Comments

[cha-ra]

>ただ、producer_nameはテーブル名がproducerなので冗長に感じます。
そうですね、私も無駄に長いと感じるので好きではありません。
同じことを二度書かなくてはならないとこがとても。

>これやるならidもproducer_idにしないとってことなりますね。
第３のテーブルで、その他のidと一緒になるだろうことが予測出来るなら、
その通りにする必要がありますね。
(History.producer_id = Producer.idではなく、History.producer_id = Producer.producer_id ですね)
逆にシーケンス等から割り振りされ、かつ別テーブルでcustomer_idもproducer_idも定義する必要が無いようなシステムであるならば、
id で統一しても良いかもしれないと思います。

Answer 5

弊社及び、弊社の開発する案件では、1システム中では
定義の異なる同一名称のフィールドを設計しません。

開発時にはデータディクショナリを作成し、
すべてのフィールドは和名で一意に一元管理します。
そのディクショナリ定義を用いてテーブル作成を行います。

テーブルはExcelで和名のみでの設計を行い、
VBAマクロでcreateTableなどのSQLを作成します。

ケースバイケースで同一フィールド名でも異なる定義で
使い分けたい方もいらっしゃいますので否定はしませんが、
まとめておいたほうが便利なこともあります。