テーブルとテーブルを繋ぐユニークキーの作り方は？

質問

Excelで以下のような表をデータベースにした場合についてお伺いします。

【Excel表】--------------------------------------
購入日　, 店舗, 商品名１　, 商品名２, 商品名３
2014/4/1, Ａ店, ボールペン, ノート　, えんぴつ
2014/4/2, Ｂ店, ハンカチ　, 洋服　　, くつした
-------------------------------------------------

まず第一正規形は以下のようになると思います。

【第一正規形】-----------------------------------
購入日　, 店舗, 商品名
2014/4/1, Ａ店, ボールペン
2014/4/1, Ａ店, ノート
2014/4/1, Ａ店, えんぴつ
2014/4/2, Ｂ店, ハンカチ
2014/4/2, Ｂ店, 洋服
2014/4/2, Ｂ店, くつした
-------------------------------------------------

第二正規形は以下のように重複している部分を別のテーブルにします。

【第二正規形】-----------------------------------
tb_purchase
購入日　, 店舗コード, 商品名
2014/4/1, 1 　　　　, ボールペン
2014/4/1, 1 　　　　, ノート
2014/4/1, 1 　　　　, えんぴつ
2014/4/2, 2 　　　　, ハンカチ
2014/4/2, 2 　　　　, 洋服
2014/4/2, 2 　　　　, くつした

tb_shop
店舗コード, 店舗
1 　　　　, Ａ店
2 　　　　, Ｂ店
-------------------------------------------------

質問１．
ここで疑問に思ったのですが、このようにtb_shopの「店舗コード」は実際にはどのようにして作成するのでしょうか？
tb_shopには重複しない「店舗コード」が必要ですが、それにはAUTO_INCREMENTを使うのが一般的なのでしょうか？

質問２．
実際の挿入時のクエリーは以下のような感じで良いのですか？

１．「2014/4/3にＡ店で消しゴムを買った」というデータを挿入する場合

　1. tb_shopで店舗カラムが「Ａ店」となる店舗コードを取得
　2. tb_purchaseに「2014/4/3, 1, 消しゴム」を挿入

　こんな感じでしょうか？

２．「2014/4/3にＣ店で牛乳を買った」というデータを挿入する場合（1～4はそれぞれ一つのクエリーです）、

　1. tb_shopで店舗カラムが「Ｃ店」となる店舗コードを取得
　2. Ｃ店が存在しないのでtb_shopに挿入
　3. 挿入後に「Ｃ店」となる店舗コードを取得
　4. tb_purchaseに「2014/4/3, 3, 牛乳」を挿入

　なんか手間が増えますが、1～3はそれぞれ別々のクエリーでやらないとダメでしょうか？

質問３．
あと質問ですが、このままだとtb_purchaseは主キーがないので、購入idのようなカラムは必要ですよね？

主に聞きたい内容は上記の「店舗コード」なる、テーブルとテーブルを繋ぐコードのようなものは
どのように作るのが一般的で同時挿入でも重複しないコードを作れるか？という点です。
自前でたとえば「S001, S002, …」というようなコードを作ったら、同時挿入で問題が出ますよね？

初心者的な質問ですみませんが、どうぞよろしくお願い致します。

回答No.3

anmochi

う～ん。質問者の意図というか思い（それと質問者のお客さんがやりたい事）というのも分かるんだよね。

回答No.1で名寄せは「駄目」ではなく「可能な限り行うべきではない」という言い方をしたのは、
現実は往々にしてあるんだよね。そういう「お客様からの要望」が。
○○コード、○○IDという概念自体が分からない、という人も居る。
なので名寄せが不可避な状況もある。
もちろんコード手打ちしてね、合ってたら店舗名が出てくるから、
店の名前がないときはこっちのボタンを押してあらかじめ店を登録してから続けてね、
という説得を試みるのは可能な限り行った方が後々お互い（デベロッパーとユーザー）のために良いとは思うんだけど、
まだ（お客様に見せれるシステムが）出来上がってない段階でお客さんにそれをイメージして納得して貰うのは極めて難しい。
質問者とお客さんがそれぞれ「餅」という言葉を聞いて頭の中で絵を描いてそれについて議論しろっていう事だからね。

で、店舗名寄せ法（毎回店舗名を入力）の場合、当然「マルナカ」と「マル　ナカ」は違うコードが割り当てられる。「株式会社ほげほげ」と「（株）ほげほげ」も同様。
これを後でどうするか（店舗コードを使ってるテーブル全部で片方の店舗コードに一気に書き換え＆消えた店舗コードを店舗マスタから削除）は完全に仕上げておかないと後で大変なことになる。

もっとも、店舗は本当に店舗名を書くだけで特に店舗ごとに集計が要らない、だから上の突合せ処理は要らない、というのだったらこの限りではない、んだけど、それならそもそも店舗を店舗マスター化する必要がない。
こういったタイプの名寄せ法を採用するなら店舗マスター化せずに本当に毎回店舗名を入力させて、後で購入履歴テーブルから似たような店舗名をピックアップして同じ店舗名に統一するという方法もあろう。この場合は本当に店舗名がテーブルの中では単なる関数従属なデータとなるが、システム側で何とかすると考えれば楽っちゃ楽な方法かも知れん。という意味でも店舗を店舗マスターかする必然性がない。

逆に、岐阜県にある「バイクショップみらくる」と熊本県にある「バイクショップみらくる」からの購入をどう分けるかという事についても考えねばならん。

> 実は店舗はユーザーがCSVで100店舗とか、1000店舗とか、一気に挿入できるようにしています。
> もしユーザーAが店舗コードS100～S300を挿入、ユーザーBが同時にS200～S400を挿入した場合、
> S200～S300が被ることになります。
わしはむしろこれが気になった。
ユーザーAとユーザーBで店舗コードがかぶるんが困るんなら
店舗コードの先頭n桁をユーザーIDという定義にすればいいだけだし、
CSVに手で入れたらかぶるやんけーっとユーザーから突っ込まれるのがイヤなら
最初から店舗コードをCSVにいれなければいい。

今回の課題で私が言いたい事は、今まで質問者から得られた情報だと
店舗マスターが単なる名称マスターとしての価値しかないという事だ。
私はそもそも名称マスターなるものは存在意義が無いと考えている。
名称マスターを設けて得られるメリットは、せいぜいDBサイズが若干減るくらいだ。
システムから見たデータベースの使い勝手はほとんど変わらない。
HDD単価が1GB当たり8円という時代にそんな事に腐心するくらいならもっと大事なことを考えようよというのが私の考え方だ。

回答No.2

yambejp

多少別の考え方をあげておきます

システムを組む時に最初にする必要があるのが要求定義と要件定義
「どうしたいのか」と「なにが必要か」ということ
要件定義の際に、店舗IDをどうつけるか決めるので
オートインクリメントで店舗コードを作るというのはありえない
（とはいえ、もっとつっこんでオートインクリメントでIDを振るという
定義もないことはないが、それは今回はおいておいてほしい）

店舗のID＝店舗コードの仕様を決めるにあたって、最大どのくらいの
店舗を想定しているか、予備の予備をとって最大桁数をきめます
またタイプミスなどを防ぐ、テンキーのみで作業ができる、ハンディなど
で読み込みが楽、などの理由から一般的には数値のみでコードは作ります
型としては単にINTや、アンサインドなゼロフィルして桁揃えしたINT、
ズバリCHARで固定長などいくつか候補はあります
汎用性や特殊な状況も踏まえると、数十店規模なら店舗コードは4～6桁
もっと大規模なら10～12桁前後が妥当な桁数でしょう
（多ければ多いほど拡張性があがりますが、入力に手間がかかり容量も増えます）

ここで重要なのは、店舗コードをプライマリーキーにするかどうかです
本来プライマリーキーの目的はDB側が特定のレコードにアクセスするための
ユニークなレコード番号です。
(1)シンプルな考え方としては店舗コードは当然ユニークでなくてはならないので
それをもってプライマリーにするというパターンも良いでしょう
(2)またプライマリーキーをオートインクリメントでとっていき、店舗コードは
あくまでもユニークにするという管理方法もあります。
これは特定のレコードを編集削除する際に店舗コードを意識しなくてもよいという
メリットがあります
(3)別の方法として、店舗コードはユニークにせず、店舗コードとフラグを
つかったユニークを作成する方法です。フラグは適当INT型で適当に増やしていけば
いいいでしょう
こうしておくと店舗コードに対する履歴を保存でき、使いようによっては
非常に便利ですがリレーションする際に無駄が増えるので注意が必要です。

データ管理の楽さや検索の速さとしては(1)>(2)>(3)で
拡張性や多機能性は(3)>(2)>(1)になると思います。

また数字だけの無意味なコードですから特定の店舗をユーザーが探すときには
探しにくいでしょう。そのため店舗管理テーブルには一定のアクセスキーを
設定しておくとユーザビリティがあがります
アクセスキーはむしろ意味を持たせた方がよいのでたとえば
新宿1号店＝SJK01、秋葉原48号店は略・・のようにしてわかりやすいものを
設定しておくといいいでしょう
アクセスキーを使う場合は後で変更になっても困らないように可変である前提の
利用方法を決め込んでください
もちろんアクセスキーはつかわず店舗コードのみで管理する方がよりシンプル

＞質問２

これはUIの問題です
#1さんが指摘している通り、あいまい検索をすると間違ったレコードが参照される
可能性はありますのでかなり注意が必要です
一番単純な方法は、入力者が管理表を自分で把握しておき具体的な店舗コードを
ダイレクトに打ち込むことです。打ちこみ完了した時点で近くのフィールドに
該当店舗の店名など表示されればチェック可能です。
セレクトボックスなど便利なようですが、感覚的に10以上のレコードが表示
されるようになるとむしろ手で打った方がイライラしなくてよいでしょう
また前述したようにアクセスキーを利用したり、店名の一部をいれると絞り込んだ
セレクトボックスがでるなどで入力を簡素化することはできるでしょう。

＞Ｃ店が存在しないのでtb_shopに挿入

むしろこれはありえないです
新規店舗は売上が発生する前に情報が入っているはずです
売上の可能性がある店舗は事前にコード登録をすませてください
運用上これは絶対条件
どうしても難しい場合は「諸口」というなんでもありのコードで打っておいて
あとから訂正伝票を入れることです
（データ管理・会計・監査上、どれをとってもあまりお勧めはできませんが）

＞質問３

当然購買IDは必要です。この場合伝票単位で処理することになるので
仕入伝票番号になるでしょう。
ただし１度に複数の購買が発生するのが通例ですので、「明細」の枝番を
利用するのが一般的です。
伝票番号＋枝番でユニークになるようにしてください
店舗コードの例のように、プライマリーキーを別途取得する、
伝票番頭＋枝番をプライマリーする、別の方法にする・・・など
運用方法はいろいろ考えられます。

お礼

とても詳しくご説明頂きましてありがとうございます。

＞要件定義の際に、店舗IDをどうつけるか決めるので
＞オートインクリメントで店舗コードを作るというのはありえない

たしかにそうですね…。
自分が不安に感じていた点は、同時挿入のときに店舗コードが被ってしまう問題でした。

実は店舗はユーザーがCSVで100店舗とか、1000店舗とか、一気に挿入できるようにしています。
もしユーザーAが店舗コードS100～S300を挿入、ユーザーBが同時にS200～S400を挿入した場合、S200～S300が被ることになります。

わざわざ「S200～S300が使えません」と表示するよりも、オートインクリメントでIDを割り振ってしまったほうがユーザーに混乱を与えないと思いました。
それに、店舗コードはユーザー側には一切見せません。単純に、テーブルとテーブルを結ぶIDとしてのみ利用するだけです。

＞むしろこれはありえないです
＞新規店舗は売上が発生する前に情報が入っているはずです

本当はそうしたいのです…。
しかし、「商品を入力すると同時に、店舗も一緒に入力するように」と依頼されています…。

＞ただし１度に複数の購買が発生するのが通例ですので、「明細」の枝番を利用するのが一般的です。

ありがとうございます。
参考にさせて頂きたいと思います。

どうもありがとうございました。

回答No.1

anmochi

Excelというのがよく分からんが。

> 質問１．このようにtb_shopの「店舗コード」は実際にはどのようにして作成するのでしょうか？
それを考えるのはあなた。
とは言ってもいくつかのセオリーはある。
オートインクリはDBMSの機能なのであれば使うもよし。
テーブルにロックをかけてIDのMAXを取ってそれの＋１を使うもよし。テーブルロックがないDBMSでは使えないが。
採番マスターを用意して現在のIDを持っておいて、それを取った後＋１して採番マスターを更新するもよし。こちらもテーブルロックがないDBMSでは使えない。

> 質問２．実際の挿入時のクエリーは以下のような感じで良いのですか？
> １．
システムとして作りこむ時には店舗IDは最初から画面に持たせておくべきじゃないかな。
名前からIDをルックアップする、いわゆる「名寄せ」という操作は可能な限り行うべきではない。
値が店舗IDで表示が店舗名であるようなドロップダウンリストがあれば十分戦える。

> ２．
さて、ここをどうするかがあなたの腕の見せ所だろう。
最も素直な（かつそのシステムを使うユーザーにワンクッションの手間を強いる）方法は
店舗マスター登録画面を別に作って、購入履歴登録画面からその画面を呼び出し、
店舗Cを登録してから購入履歴登録画面へ戻ってくる、というものだろう。

> 質問３．このままだとtb_purchaseは主キーがないので、購入idのようなカラムは必要ですよね？
キーをつけたいだけなら購入日と店舗コードと商品名を複合主キーにすればいいんじゃないの。商品名という名前の項目をキーにすべきかとう議論はおいておいて。
値自体に意味がない行IDをいたずらに追加すべきではないと思う。
例えば発注番号と発注枝番号という2つの項目を追加して、

tb_purchase
発注番号, 購入日　, 店舗コード
xxxxx1, 2014/4/1, 1
xxxxx2, 2014/4/2, 2

tb_purchase_sub
発注番号, 発注枝番号, 商品名
xxxxx1, 1, 　　　　, ボールペン
xxxxx1, 2, 　　　　, ノート
xxxxx1, 3, 　　　　, えんぴつ
xxxxx2, 1, 　　　　, ハンカチ
xxxxx2, 2, 　　　　, 洋服
xxxxx2, 3, 　　　　, くつした

くらいまでするなら話は別だが。
こうすると単なる無意味なユニークIDから、発注を行った単位がわかるという意味を無理やりでも持たせる事ができる。

> 自前でたとえば「S001, S002, …」というようなコードを作ったら、同時挿入で問題が出ますよね？
なので普通のDBMSには同時実行を制御するような仕組みがいくつも備わっている。
ExcelというのがODBCでバックエンドにExcelを使っているというのであれば話は別だが、
そうでないならその辺は特に心配する話ではない。

お礼

早速のご回答ありがとうございます！

＞質問１．
自分でやると不都合が起きそうなのでオートインクリを使いたいと思います。

＞質問２．
＞１．
すみません、実は店舗はこちらではわからないんです。
というのも、明日新規で開店する店舗があるかもしれません。
そのため、店舗コードは店舗を入力するときに自動的に生成したかったんです。
なのでこれもオートインクリで生成かな…と思いました。

＞２．
そうですね。自分でいろいろ方策を考えてみようと思います。
参考にさせて頂きます。

＞質問３．
＞キーをつけたいだけなら購入日と店舗コードと商品名を複合主キーにすればいいんじゃないの。

もしかしたら、同じ日に同じ店舗で同じ商品を２度購入するかもしれないので複合主キーにはできないかなと思いました。
これも説明不足ですみません・・・。

＞値自体に意味がない行IDをいたずらに追加すべきではないと思う
＞例えば発注番号と発注枝番号という2つの項目を追加して・・・
＞なので普通のDBMSには同時実行を制御するような仕組みがいくつも備わっている。

はい、ご説明にありましたようにテーブルをロックしておいて、IDのMAX値を取得して自分で発注番号や発注枝番号みたいなものを作るべき、ということですよね。

いろいろわかってよかったです。どうもありがとうございました。

あと、Excelは関係なかったです。