Database Redesign

[RexYuan]

這是目前第一版的 SQL structure dump

# Dump of table course
# ------------------------------------------------------------

CREATE TABLE `course` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `department` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `chdepartment` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `code` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
  `chname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `teacher` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `credit` int(1) NOT NULL,
  `fbreview` text COLLATE utf8_unicode_ci,
  `likeit` int(10) unsigned NOT NULL,
  `dislikeit` int(10) unsigned NOT NULL,
  `availability` tinyint(1) NOT NULL,
  `grade` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `teacher` (`teacher`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# Dump of table department
# ------------------------------------------------------------

CREATE TABLE `department` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `abbr` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `code` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `abbr` (`abbr`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# Dump of table vote
# ------------------------------------------------------------

CREATE TABLE `vote` (
  `id` int(30) NOT NULL AUTO_INCREMENT,
  `fbid` bigint(30) unsigned NOT NULL,
  `code` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `vote` tinyint(1) NOT NULL COMMENT '1 for yay 0 for nay',
  `fbName` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `fbGender` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fbid` (`fbid`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

目標是解決 #16 和 #18，解決同堂課不同教授問題，更清楚的區分不同屬性的資料（使用者、系所、課程...），和讓 scraping 的動作更容易。潛在：讓 #10 推薦系統能夠運作。 另外，這是包含 content 的 SQL dump。

[RexYuan]

首先兩個考量到資料獲取（scraping）時的參考：

1. URL GET Request Components Guide：解釋在 scraping 時需考量的各個 GET variable 以及其意義。
2. scrap.php：昨天剛寫的，更可閱讀、更具重複使用性、更可依照新的資料庫結構變動的的 scraper 設計。

[RexYuan]

我目前想到的理想情況： 四個 table 是 course, department, user, vote，先做前兩個。 首先是 course，它儲存了每一堂課的資料，我認為他應該有的 columns：

1. 一個能夠辨識他的 primary key，目前是用課程代碼（參考URL GET Request Components Guide 的 courseCode），但考量到同一堂課、不同老師／班級／時段的課程共用同一個 courseCode，必須尋找替代
2. 課程代碼，因為就算他並非作為 primary key，在獲取資料時（參考 scrap.php 中的 checking duplicate）必須以它來判斷該課程是否重複（已經存在資料庫中）；但如果將老師分開成兩個 rows，或許就能不需要
3. 中文課程名稱
4. 英文課程名稱（需要嗎？似乎有比較潮）
5. 教師，這個目前如果有同一個課程名稱，不同個版本（不同教師／時段／班級等），依然存在同一格，以 ／ 作為區分；但如果要重新設計，勢必不能用這個方法
6. 課程描述
7. 學分
8. 年級
9. 本學期有無開課
10. 開設系所
11. 課綱 URL，為了下次 scrap 的時候不需要重新尋找所有437356260個可能性（見 URL GET Request Components Guide 中的 p.s. 1） 最好還是存一下，以待不時之需

而在 department 中我認為目前的架構還可以：

1. id，這是目前的 primary key。目前在 course 裡，每堂課的系所中文名稱以及 code 都是存在同個 row 裡的，因此這和 department 的有重複，因此重新設計時應該分開，在 course 中只存該 department 的 id。見 #16
2. 系所簡稱，這個在目前的資料庫中是 abbr。這代表了該系所開設的課程在課程代碼中的簡稱，例如資工系的線性代數是 CSU0016，而他的系所簡稱便是 CSU
3. 系所代碼，scraping 需要（參考 GET Request Components Guide 中的 deptCode）
4. 系所中文名稱
5. 系所英文名稱（需要嗎？似乎有比較潮）

Edit:

• 新的 scraping 方法開啟了讓刮師資生課程資料以及碩博班資料更為輕鬆，兩者應被列入考量
• 新的方法同時讓 department 中的 abbr 不一定需要在 scraping 時考量，大幅簡化通識課程的資料

[RexYuan]

關於 course 裡的 10.，有個潛在問題是，通識課程並非同一個分類的通識就都有同樣的系所簡稱或系所代碼，見 SQL dump content 中 id 在 7035 - 7044 之間的一般通識課程：

id	department	chdepartment	code	chname	teacher
7035	0AU	一般通識	0AUG418	電影英文	林秀玲
7036	0AU	一般通識	0AUG426	物理與生活	張嘉泓
7037	0HU	一般通識	0HUG223	禪與人生	王開府
7038	0HU	一般通識	0HUG502	法語（一）	楊慧娟
7040	0HU	一般通識	0HUG640	武士道文化與批判	張崑將
7041	0NU	一般通識	0NUG246	生理心理學	呂國棟
7042	0SU	一般通識	0SUG208	臺灣的政治發展與變遷	范世平
7043	0SU	一般通識	0SUG523	通識教育講座	黃玫瑄
7044	0SU	一般通識	0SUG514	博物館探索	黃玫瑄

Edit: 由於新的 scraping 方式，abbr 在 scraping 時已不需要。但這個問題依然存在，因為如果要在 course 中不儲存系所代碼與系所名稱，而已指向 department 的 id 儲存，這邊會遇到不確定性。

[jaidTw]

這是草案，細節部分待討論和確定 TABLE department 儲存所有系所資訊

名稱	型別	意義	範例	備註
dept_id	u_INT	PRIMARY KEY	1／53／4523 ...
abbr	CHAR(3)	開課代碼前綴	HED（衛教博）／EDC（教育大碩）／PCM（心輔碩）／CSU（資工系）／02U（其中一種通識） ...
code	CHAR(4)	系所代碼	9UAA（校際學士班（臺大））／SU47（資工系）／E（教育學院）／CU（共同科） ...
chn_name	NVARCHAR()	系所中文名稱	資工系／教育輔／理學院／生物多樣學位學程 ...
eng_name	VARCHAR()	系所英文名稱	Department of Computer Science and Information Engineering (Undergraduate) !ADDMORE

TABLE course 儲存所有課程資訊

名稱	型別	意義	範例	備註
course_id	u_INT	PRIMARY KEY	1／32／793
serial_no	u_SMALLINT	開課序號	3025 !BEGINHERE
course_code	CHAR(7)	開課代碼	CSU0001
acadmYear	u_TINYINT	學年	104
acadmTerm	u_TINYINT	學期	1
chn_name	NVARCHAR()	課程中文名稱	程式設計（一）
eng_name	VARCHAR()	課程英文名稱	Computer Programming (I)
teacher	NVARCHAR()	教師中文姓名	蔣宗哲	可能有多名老師教授同一堂課。
tname	VARCHAR()	教師英文姓名	CHIANG, Tsung-Che	同上。
course_group	CHAR(1)	組別
classes	CHAR(1)	開課班級代碼		見Scraper Guide之classCode
course_kind	CHAR(1)	全/半學期	H	值為H/F
credit	FLOAT()	學分數	3.0	是否有小數之學分數？
dept_code	CHAR(4)	開課系所代碼	SU47
dept_group	CHAR()	開課組別
eng_teach	BOOL	全英語授課	F
form_s	CHAR(1)	開課年級	1
gender_restrict	CHAR(1)	性別限制	N	值為N/M/F
moocs_teach	BOOL	MOOCs	F
option_code	CHAR(1)	必/選修	R	值為R/S
restrict	NVARCHAR()	擋修條件		是否有英文?
rt	BOOL	遠距授課	F
selfTeachName	NVARCHAR()	正課/實驗親授		未見使用
chn_location	NVARCHAR()	中文上課地點	公館 理圖807	或許可建立教室表？
eng_location	VARCHAR()	英文上課地點	同上
time	VARCHAR()	上課時間	三 8-9, 五 7	parse日期以及節次
status	BOOL	是否停開	F
comment	NVARCHAR()	中文註解
eng_comment	VARCHAR()	英文註解
counter_exceptAuth	u_TINYINT	修課總人數	52
authorize_p	u_TINYINT	授權碼名額	20
authorize_r	FLOAT()	授權碼比例	0.40
authorize_using	TINYINT	授權碼使用人數	-52
limit	u_TINYINT	台大聯盟限修總人數	0
limit_count_h	u_TINYINT	限修人數	50

TABLE course_record 以課程代碼查詢各學期開課記錄用，映射至course之PRIMARY KEY集合。

名稱	型別	意義	範例	備註
id	INT	PRIMARY KEY	1
course_code	CHAR(7)	課程代碼	CSU0001
record	TEXT	開課紀錄	1

可擴充方面:

1. 設計教師資料頁面，提供教師聯絡方式及開課紀錄等資訊
2. 結合考古題平台（現有FB社團以及PTT NTNU_exam)，或自幹。
3. 進一步parse課程之詳細資料(課綱、評量、參考書目等)。

[RexYuan]

修了一下typo, format 跟加上一些unsigned 與將 BIT 改為 BOOL 參考：MySQL Doc 11.1 Data Type Overview // TODO: 加上範例與計算各值 length 另外可考慮將教師或地點或時間分開建 table 以利搜尋

[jaidTw]

新增範例，更改unsigned標記方式，修正少許錯誤及更改部分欄位型別。

[RexYuan]

這是依照上面，尚待修改／擴充的暫時版

[RexYuan]

考慮：擴增 user, vote, teacher, 重建record 以下是 Facebook API 可以提供的資訊：

}
   "id": "10203739867764562",
   "email": "r1218r1218\u0040gmail.com",
   "first_name": "Chih-cheng",
   "gender": "male",
   "last_name": "Yuan",
   "link": "https://www.facebook.com/app_scoped_user_id/10203739867764562/",
   "locale": "en_US",
   "name": "Chih-cheng Yuan"
}

From Graph API Reference > User:

1. id(numeric string): The id of this person's user account. This ID is unique to each app and cannot be used across different apps
2. email(string): The person's primary email address listed on their profile. This field will not be returned if no valid email address is available
3. first_name(string): The person's first name
4. gender(string): The gender selected by this person, male or female. This value will be omitted if the gender is set to a custom value
5. last_name(string): The person's last name
6. link(string): A link to the person's Timeline
7. locale(string): The person's locale
8. name(string): The person's full name

[RexYuan]

考量到 scraping 舊方法能直接在課綱頁面做蒐集，以下是可增加的資料：

資料	範例
每週授課時數	正課時數: 2 小時
開課系級	藝術與美感
課程簡介	歌劇發展雖僅有四百多年，但已成為全球公認的最高舞台藝術，並對所有的表演藝術領域有著直接而全面的影響。多位歌劇大師的經典作品裡，多面向地反映創作者的時代背景、個人人生觀與藝術觀，融合在創作者的音樂語法裡。這些經典作品在不同的時代被演出時，透過不同的詮釋者，又呈現出不同的樣貌。本課程依開課學年度，選取一位歌劇大師之經典作品，引導學生直接與世界歌劇舞台接軌，並就其作品，細細剖析歌劇相關之藝術美感，如劇本文學、音樂語言、舞台設計、肢體走位、導演理念等等，帶領學生直接接觸最高總體藝術的不同面向，培養個人的藝術品味、美感與判斷力。

[RexYuan]

更改幾個命名、改變命名風格、新增 Users 和 Votes 表格

更新 wiki 上的 Database Configuration

[RexYuan]

Table base redesign is mostly done. Check the Schema.

[RexYuan]

I'm closing this issue because the groundwork has been done. The rest of the minor enhancement or adjustment will be done at #25 because of the emerging potentials.