ExcelのデータをWebアプリに移植する

Question

追記３：
連絡先が不明のレコードもございます。
ちなみに，IDと氏名のみしか入力していないレコードは2件ほど，
ございます。

---

追記２：
好きな食べ物，嫌いな食べ物は1人1個までしか書いておりません。
ただし，好きな食べ物，嫌いな食べ物がない場合は，
レコードの該当するセルには何も入力しないようにしました。

---

追記１：
私が作成しようと思っているWebアプリは，スプレッドシートで代用可能です。
ただし，今回は，自分の勉強（DB化やコーディング）という目的も
含まれているので，そういったものは使わない方向で進めていきます。

---

【概要】
Excelで管理していたデータをブラウザで表示できるようにするために，
Webアプリを作成し，ExcelのデータをMySQLに移植しようと思いました。

ただ，その際，必要となったデータベース設計書が作成できず，困ってしまいました。
下記に詳細をまとめるので，どなたかご教示いただけると幸いです。

【Excelに管理されているデータ】
昔，飲み会の幹事を任されることが多かったので，
自分と関わるひとの電話番号や好きな食べ物などまとめ，
下記のような表を作成しようと思いました。

---

1行目に左から，
ID，氏名，連絡先，好きな食べ物，嫌いな食べ物という項目を作り，
その下から，その項目に対応するデータをまとめていきました。

（イメージ図）
ID　|　氏名　|　連絡先　 　　　　　　　　　　　　　　　 |　　好きな食べ物 |嫌いな食べ物
001 |　田中 |　　XXX-XXXX-XXXX　　　　　　　　　　 |　 　　鍋　　　　| トマト
002 | 山田　|　 XXX-XXXX-XXXX，[e-mail] |　えりんぎ　　 |　 大根

---

この図を見ると分かる通り，山田さんの連絡先だけ，
1つのセルに複数のデータ（電話番号とメールアドレス）が含まれています。

こういったセルは複数あり，
メールアドレス，電話番号の順に入力されているものもあれば，
電話番号
メールアドレス
と改行し，入力されているものもあります。

【質問内容】
この場合，データベース設計書（特に，ER図）をどのように作成すればよいのでしょうか。
また，何か他の手段があれば，そちらもご教示いただけると幸いです。

【質問内容に対して，個人的に検討したこと】
・電話番号とメールアドレスを1つのセルにまとめない。
（1つのセルに1つのデータとなるよう列をもう1つ挿入する。Excelでの管理方法を見直す。）
→結構な量のデータなので，いまさら修正となると，かなりの時間がかかる。
・電話番号とメールアドレスを分けることを諦める。
（varchar型（30文字）などとして，電話番号とメールアドレスを一緒にしてしまう。）
→今のところ，これが現状の最善かと思っている。

以上です。
どなたか助けていただけると幸いです。
よろしくお願いいたします。

Comments

[kou0179]

好きな食べ物、嫌いな食べ物は必ず一人一個で良いですか？

[tanukitii]

説明不足ゆえ，ご迷惑おかけいたしましたこと，
誠に申し訳ございません。

その人が，特に好きな食べもの，きらいな食べものをまとめているので，
1つまでしか入力しておりません。
ただし，嫌いな食べ物（または，好きな食べ物）がない人の該当セルは
空欄になっております。

この点につきましては，簡単に正規化できるので，
質問内容に含めませんでした。
ただ，それが，かえって質問内容をわかりにくくしてしまっているかもしれません。
念のため，追記いたします。

ご指摘誠にありがとうございます。

[kou0179]

連絡先が存在しない人はいませんか？
たびたび、すみません。

[tanukitii]

いえ，こちらこそ，申し訳ございません。
連絡先不明の方もいらっしゃいます。
ちなみに，レコード数は2つくらいかと思いますが，
ID名と氏名しか入力していないレコードもございます。

そちらも念のため，追記いたします。
よろしくお願いいたします。

Answer 1

同じカラムには1種類に設計すべきって掟から電話番号とメルアドは分けるべきでしょう。
好きな食べ物や嫌いな食べ物は複数あり得るから、テーブル設計としては個人ID, 好き嫌い区分, 食べ物ID (カラムの並びは用途によって考える)をPRIMARY KEYとする別テーブルにしたいし、食べ物ID, 食べ物名でテーブルも作っておきしたです。
データベースのテーブルはデータベースの正規化 に沿って設計します。通常、第3正規化まで行います。

何百人以上とかの大人数でもなければ、Excelでもテキストファイルのままでも良いでしょうけど。

Comments

[tanukitii]

ご回答ありがとうございます。

Excelで管理しているデータは意外と多いので，電話番号とメールアドレスを
分けない方法があればと思ったのですが，やはり，正規化できるように
Excelのデータの構成を変えないといけないようですね…。

ちなみに，Orlofskyさんが上記で述べたテーブル構成ですが，
個人ID，好き嫌い区分，食べ物IDのどれがPRIMARY KEYにあたるのでしょうか。
それともこれらまとめて複合キーとして考えているのでしょうか。

お手数おかけいたしますが，よろしくお願いいたします。

[Orlofsky]

個人ID，好き嫌い区分，食べ物IDの3つともPRIMARY KEYにする必要があります。
PRIMARY KEY(主キー)制約とは？ https://www.sejuku.net/blog/52356

[tanukitii]

あ，勘違いしていました。

Primary Keyは個人IDだけでよいのでは，と思っていたのですが，
個人IDだけではレコードを特定できませんね。

ただ，これは順序も関係するのでしょうか…。
個人IDと食べ物IDをPrimary Keyにすれば，この2つでレコードを
特定できるので，十分な気がしますが，
この解釈はどこかおかしいのでしょうか？

度々申し訳ございません。
よろしくお願いいたします。

[Orlofsky]

>個人IDと食べ物IDをPrimary Keyにすれば，この2つでレコードを
>特定できるので，十分な気がしますが，
好きな食べ物テーブル、嫌いな食べ物テーブルをそれぞれ作る場合は、それぞれ個人IDと食べ物IDをPRIMARY KEYとすれば良いですが、
好きな食べ物 や 嫌いな食べ物 が0以上あり得る場合、個人IDと食べ物IDと
好きな食べ物 か 嫌いな食べ物 かを識別する情報が必要ですよね。

[tanukitii]

理解するのに少し時間がかかってしまい，返信が遅くなってしまいました。
申し訳ございません。

ようやく自分の中で整理できました。
ご教示いただいたことを活かして，アプリケーションを作成していこうと思います。

また，私の質問に多く回答していただいたので，ベストアンサーとさせていただきます。
この度は，誠にありがとうございました。

Answer 2

ブラウザで見たい、というだけであればスプレッドシートでいいのでは？と思いましたが如何でしょう？

※スプレッドシートにせよ、Webアプリにせよ、セキュリティちゃんとしないと個人情報が漏洩するので、そのあたりに自信がない場合は、そもそもやらない（Excelを共有サーバ等に置く程度に留める）をお勧めします。

※Webアプリ化（というかDB化）する場合は、データの追加／削除／更新用の機能も併せて作成されるのかな？と思いますが、内容的にそこまでの手間を掛ける程かな？とも思います。

まぁいずれも目的次第になりますので、最終的に本当にやりたいこと、を明らかにすると良いかと思います。

Comments

[tanukitii]

ご回答ありがとうございます。

確かに仰る通り，作成するWebアプリは，たいした規模のものではございません。
もちろん，スプレッドシートでも十分ではあります。
ただ，今回は，DB化やコーディングなど，自分の勉強という意味も含まれているので，
それは使用しない方向でいこうと思っていました。

ですが，そのようなご指摘を受けると企画そのものを検討しなおした方が，
いいとも思うようになってきました。
この後，いろいろと考えてみようと思います。

[aikon_marimo]

学習目的ということであれば良い題材かと思います。

質問内容にある電話番号とメールアドレスについては、分けた方が良いかと思います。
（分け方の勉強にもなる＋データキレイに整理できる）

分け方としては、色々ありますが、私の場合は、
１．電話番号とメアドの区切られ方を整理する（多分、","、"、"、" "、改行、程度の分類になるかと）。
２．改行だけ扱いが面倒なので、まず改行を無くす。（別の文字に置き換える）
　⇒ExcelでSUBSTITUTE使えばできます。（以下はセル内の改行をカンマに置換する例）
　　=SUBSTITUTE(対象セル,"
",",")　# 第2引数はWindowsならAlt+Enterで改行
３．これでとりあえず改行のケースは無くなるので、上記１．で整理した区切り文字を文字列置換し、区切り文字を統一する。
　　"、"⇒","
　　" "⇒","

でやってみるかなと思いますので参考までに。区切り文字さえ統一できれば、列に分けるのは容易です。

[aikon_marimo]

電話番号とメアドの入力順が異なるケースもあるとのことですが、メアドに必ず@が含まれる、と前提を置けば、
・２つのデータのうち、@が含まれる方をメアド列に、もう片方を電話番号列に入れる
のようなやり方で整理できるかと思います。

一見面倒そうなものでも、パターンを整理すれば案外簡単にできるので、色々検討するのも勉強になって楽しいかと思います。

[tanukitii]

手動でなおすことばかり考えていたので，関数やVBAを使うのは盲点でした。
これらを使用して，まずは，正規化できるようにデータを整理してみようと思います。

ご指導ご鞭撻のほど，誠にありがとうございました。

Answer 3

ER図

DDL

create table foods (
  id INT not null comment 'ID'
  , name VARCHAR(32) comment '名前'
  , constraint foods_PKC primary key (id)
) comment '食べ物';

create table users (
  id INT not null comment 'ID'
  , name VARCHAR(64) not null comment '名前'
  , tel VARCHAR(12) comment '電話番号'
  , mail VARCHAR(255) comment 'メールアドレス'
  , like_food_id INT comment '好きな食べ物'
  , dislike_food_id INT comment '嫌いな食べ物'
  , constraint users_PKC primary key (id)
) comment 'ユーザー';

DB化への課題と解決

メールアドレスと電話番号の分割

連絡先という１つの列(以降、「カラム」と表記)で持ってるので、それらを分けるべきでしょう。
エクセルの関数だけでゴリゴリ分けた例です。

前提条件

• 連絡先カラムは、メールアドレスか電話番号、もしくはその両方か、入力が何もない
• メールアドレスの電話番号の順番は問わない
• メールアドレスと電話番号の区切り文字は必ず「,」である
• メールアドレスもしくは電話番号のみの時は「,」が無い
• 「,」、メールアドレス、電話番号の前後に半角スペースが入っている可能性がある

D2

=IF($C2="","",IFERROR(TRIM(LEFT($C2,FIND(",",$C2)-1)),$C2))

E2

=IF($C2="","",IFERROR(TRIM(RIGHT($C2,LEN(C2)-FIND(",",$C2))),$C2))

F2

=IFERROR(IF(FIND("@",$D2),$D2,"dummy"),IFERROR(IF(FIND("@",$E2),$E2,"dummy"),""))

G2

=SUBSTITUTE(IF( AND($D2=$E2,$D2=$F2),"",IF($F2=$D2,$E2,$D2)),"-","")

そのほかにもCSVに出力してPython等、その他のプログラミング言語など用い行う事も可能です。

好きな食べ物、嫌いな食べ物の正規化

ここは簡単でしょう。
Excelの重複削除など、Excelの強みをガンガン活かしてやってください。

Comments

[tanukitii]

ご連絡が遅くなってしまい，申し訳ございません。

やはり，Excelでまとめたものを適切に整理しないといけないんですね。
頑張って整理して，Webアプリケーションを作成しようと思います。
この度は，ご教示いただき誠にありがとうございます。

ただ，今回，かなり迷ったのですが，
ベストアンサーはOrlofskyさんにさせていただきました。
kou0179さんのご意見もかなり参考になったのですが，
決めきれず，早くご回答いただいた方をベストアンサーにいたしました。

貴重なご意見をいただいたのにも関わらず，申し訳ございません…。

Answer 4

Excelで管理していたデータをブラウザで表示できるようにする

これが目的なら、one driveかgoogle driveに置いてしまえばいいと思います。

Comments

[tanukitii]

ご回答ありがとうございます。

確かに仰る通り，今回作成するアプリを考えると，クラウドを使用すれば
良いかと思います。
ただ，このWebアプリの作成は，データベース設計やプログラミングなど，
自分の勉強のためという理由も含まれているので，
今回は，クラウドを使用しない方向を検討しております。

上記は，質問内容に含んでいなかったので，追記いたします。
申し訳ございません。

よろしくお願いいたします。