DataBase Schema

1. member (使用者)
2. country (國家)
3. usercountry (訂閱國家)
4. c_covid (國家疫情)
5. bulletin (國家留言板)
6. state (美國洲)
7. country_state (國家與洲關係 *有問題懶的改)
8. s_covid (洲疫情)

Total List of relations

Schema	Name	Type	Owner
public	bulletin	table	user
public	c_covid	table	user
public	country	table	user
public	country_state	table	user
public	member	table	user
public	s_covid	table	user
public	state	table	user
public	usercountry	table	user

member

• PRIMARY KEY (account)

Column	Type	Collation	Nullable	Default
account	text		not null
passwd	text		not null
name	text		not null
activate	boolean		not null
useridentify	text		not null

country

• PRIMARY KEY (country_name)

Column	Type	Collation	Nullable	Default
country_name	text		not null
population	integer		not null
flag_url	text
geo	text

usercountry

• PRIMARY KEY (account,country_name)
• FOREIGN KEY (account) FROM member
• FOREIGN KEY (country_name) FROM country

Column	Type	Collation	Nullable	Default
account	text
country_name	text

c_covid

• PRIMARY KEY (country_name,c_date)
• FOREIGN KEY (country_name) FROM country

Column	Type	Collation	Nullable	Default
c_date	date		not null
country_name	text		not null
confirmed	integer
deaths	integer
recovered	integer

bulletin

• PRIMARY KEY (country_name,account,b_time)
• FOREIGN KEY (account) FROM member
• FOREIGN KEY (country_name) FROM country

Column	Type	Collation	Nullable	Default
account	text		not null
country_name	text		not null
name	text
b_time	timestamp without time zone		not null
message	text

state

• PRIMARY KEY (state_name)

Column	Type	Collation	Nullable	Default
state_name	text		not null
population	integer
flag_url	text
geo	text

country_state

@ 這裡有點問題 可是有點懶的改

• PRIMARY KEY (country_name,state_name)
• FOREIGN KEY (country_name) FROM country
• FOREIGN KEY (state_name) FROM state

Column	Type	Collation	Nullable	Default
country_name	text		not null
state_name	text		not null

s_covid

• PRIMARY KEY (country_name,state_name,s_date)
• FOREIGN KEY (country_name) FROM country_state
• FOREIGN KEY (state_name) FROM country_state

Column	Type	Collation	Nullable	Default
country_name	text		not null
state_name	text		not null
s_date	date		not null
confirmed	integer
deaths	integer