Author: | Simon Wiles, DDBC |
---|---|
Contact: | digital_archives@ddbc.edu.tw |
Web site: | http://authority.ddbc.edu.tw/ |
Date: | Oct, 2010 |
Contents
This image above shows the schema for the Database as form of Entity Relation Diagram (ERD).
The database contains four major entities: dynasty, emperor, era and month.
A dynasty entity represents a dynasty (朝代) in Chinese history, or a period (時代) in Japanese or Korean history. Each dynasty has one or more dynasty_names and none or more dynasty_comments. A dynasty will usually encompass the reigns of several different emperors (皇帝). Each emperor entity will also have one or more emperor_names and none or more emperor_comments, and is linked to its parent dynasty by a Foreign Key relation. Each emperor's reign consists of one or more eras (年號), represented as era entities in the database. An era entity has era_names and may have era_comments, and is linked to its parent emperor. Finally, each era consists of one or more month entities, which stand for lunar months. A month entity has a Foreign Key dependency on an era, and may have month_comments.
There is also a day_comments table which is indexed by Julian Day Numbers, and a by_year VIEW, which facilitates easier querying on a year-by-year basis.
Finally, there is a period table, which contains references to time periods which do not fit into the schema outlined above. A period may or may not be linked directly to a dynasty, in which case it gives the actual historical extent of that dynasty, and must be unique.
In the DDBC Time Authority Database, the lunar month is the smallest unit of data: i.e. the database does not store a record for each day. Nevertheless, the details of an individual day can be derived from the corresponding month entity.
See the comments to each table and field below for more information.
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | tinyint(3) | true | false | |||
code | varchar(8) | false | false | |||
long_name_zho | varchar(20) | false | false | |||
short_name_zho | varchar(10) | false | false | |||
long_name_eng | varchar(30) | false | false | |||
short_name_eng | varchar(10) | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
era_id | smallint(5) | false | false | |||
yearGanzhi | char(6), | false | false | |||
first | mediumint(8) | false | false | |||
last | mediumint(8) | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | smallint(5) | true | false | |||
jd | mediumint(8) | false | false | |||
comment | text | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | smallint(5) | true | false | |||
type | enum('chinese','japanese','korean') | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | smallint(5) | true | false | |||
comment | text | false | false | |||
dynasty_id | t_dynasty.id | smallint(5) | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
dynasty_id | t_dynasty.id | smallint(5) | true | false | 0 | |
name | varchar(10) | false | false | |||
ranking | tinyint(3) | true | false | 0 | ||
language_id | c_languages.id | tinyint(3) | false | false | 0 |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | smallint(5) | true | false | |||
dynasty_id | t_dynasty.id | smallint(5) | false | false | 0 |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | t_emperor.id | smallint(5) | true | false | ||
emperor_id | smallint(5) | false | false | |||
comment | text | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
emperor_id | t_emperor.id | smallint(5) | true | false | 0 | |
name | varchar(10) | false | false | |||
ranking | tinyint(3) | true | false | 0 | ||
language_id | c_languages.id | tinyint(3) | false | false | 0 |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | smallint(5) | true | false | |||
emperor_id | t_emperor.id | smallint(5) | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | smallint(5) | true | false | |||
era_id | t_era.id | smallint(5) | false | false | ||
comment | text | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
era_id | t_era.id | smallint(5) | true | false | 0 | |
name | varchar(10) | false | false | |||
ranking | tinyint(3) | true | false | 0 | ||
language_id | c_languages.id | tinyint(3) | false | false | 0 |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | mediumint(8) | true | false | |||
year | tinyint(3) | false | false | Ordinal Year of Era | ||
month | tinyint(3) | false | false | |||
month_name | varchar(3) | false | false | |||
leap_month | tinyint(1) | false | false | 0 | ||
era_id | t_era.id | smallint(5) | false | false | ||
first | mediumint(8) | false | false | |||
last | mediumint(8) | false | false | |||
ganzhi | char(6) | false | false | |||
start_from | tinyint(3) | false | false | 1 | First Day of Month is which ordinal? | |
status | enum('S','P') | false | false | S | ||
eclipse | tinyint(3) | false | false | 0 | First Day of Month is a Solar Eclipse? |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | smallint(5) | true | false | |||
month_id | t_month.id | mediumint(8) | false | false | ||
comment | text | false | false |
Name | Foreign Key | Data-type | PK? | NULL? | Default | Comment |
---|---|---|---|---|---|---|
id | smallint(5) | true | false | 0 | ||
dynasty_id | t_dynasty.id | smallint(5) | false | true | NULL | |
first | mediumint(8) | false | false | |||
last | mediumint(8) | false | false | |||
description | varchar(30) | false | false | |||
note | text | false | false |