Database

note

Different databases have different data types. The following table presumes use of mysql/mariadb as an example.

activity

activity table records various actions of users, such as votes, etc.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		activity id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
cancelled_at	timestamp	YES			cancelled time
user_id	bigint(20)	NO	MUL		the user ID that generated the activity or affected by the activity
trigger_user_id	bigint(20)	NO	MUL	0	the trigger user ID that generated the activity or affected by the activity
object_id	bigint(20)	NO	MUL	0	the object ID that affected by the activity
original_object_id	bigint(20)	NO		0	the original object ID that activity
activity_type	int(11)	NO			activity type, correspond to config id
cancelled	tinyint(4)	NO		0	mark this activity if cancelled
rank	int(11)	NO		0	rank of current operating user affected
has_rank	tinyint(4)	NO		0	this activity has rank or not
revision_id	bigint(20)	NO		0	revision id

answer

answer table records the answer info.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		answer id
created_at	timestamp	NO		CURRENT_TIMESTAMP	create time
updated_at	timestamp	YES			update time
question_id	bigint(20)	NO		0	question id
user_id	bigint(20)	NO	MUL	0	answer user id
last_edit_user_id	bigint(20)	NO		0	last edit user id
original_text	mediumtext	NO			original text
parsed_text	mediumtext	NO			parsed text
status	int(11)	NO		1	answer status(available: 1;deleted: 10)
adopted	int(11)	NO		1	adopted (1 failed 2 adopted)
comment_count	int(11)	NO		0	comment count
vote_count	int(11)	NO		0	vote count
revision_id	bigint(20)	NO		0	revision id

collection

collection table records user collection about any object.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI	0	collection id
created_at	timestamp	NO		CURRENT_TIMESTAMP	created time
updated_at	timestamp	NO		CURRENT_TIMESTAMP	updated time
user_id	bigint(20)	NO	MUL	0	user id
object_id	bigint(20)	NO		0	object id
user_collection_group_id	bigint(20)	NO		0	user collection group id

collection_group

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		id
created_at	timestamp	NO		CURRENT_TIMESTAMP	created time
updated_at	timestamp	NO		CURRENT_TIMESTAMP	updated time
user_id	bigint(20)	NO	MUL	0	user id
name	varchar(50)	NO			the collection group name
default_group	int(11)	NO		1	mark this group is default, default 1

comment

comment table records the comment about question or answer.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		comment id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
user_id	bigint(20)	NO		0	user id
reply_user_id	bigint(20)	YES			reply user id
reply_comment_id	bigint(20)	YES			reply comment id
object_id	bigint(20)	NO	MUL	0	object id
question_id	bigint(20)	NO		0	question id
vote_count	int(11)	NO		0	user vote amount
status	tinyint(4)	NO		0	comment status(available: 1;deleted: 10)
original_text	mediumtext	NO			original comment content
parsed_text	mediumtext	NO			parsed comment content

config

config records the site configuration.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	int(11)	NO	PRI		config id
key	varchar(128)	YES	UNI		the config key
value	text	YES			the config value, custom data structures and types

meta

meta records some extra information about the object.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	int(10)	NO	PRI		id
created_at	timestamp	NO		CURRENT_TIMESTAMP	created time
updated_at	timestamp	NO		CURRENT_TIMESTAMP	updated time
object_id	bigint(20)	NO	MUL	0	object id
key	varchar(100)	NO			key
value	mediumtext	NO			value

notification

notification table records the notification that user received.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		notification id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
user_id	bigint(20)	NO	MUL	0	user id
object_id	bigint(20)	NO	MUL	0	object id
content	text	NO			notification content
type	int(11)	NO		0	notification type(1:inbox; 2:achievement)
is_read	int(11)	NO		1	read status(unread: 1; read 2)
status	int(11)	NO		1	notification status(normal: 1;delete 2)

power

power table records all permissions

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	int(11)	NO	PRI
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
name	varchar(50)	NO			name
power_type	varchar(100)	NO			power type
description	varchar(200)	NO			description

question

question table records the question info.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		question id
created_at	timestamp	NO		CURRENT_TIMESTAMP	create time
updated_at	timestamp	NO		CURRENT_TIMESTAMP	update time
user_id	bigint(20)	NO	MUL	0	user id
last_edit_user_id	bigint(20)	NO		0	last edit user id
title	varchar(150)	NO			question title
original_text	mediumtext	NO			original text
parsed_text	mediumtext	NO			parsed text
status	int(11)	NO		1	question status(available: 1;deleted: 10)
view_count	int(11)	NO		0	view count
unique_view_count	int(11)	NO		0	unique view count
vote_count	int(11)	NO		0	vote count
answer_count	int(11)	NO		0	answer count
collection_count	int(11)	NO		0	collection count
follow_count	int(11)	NO		0	follow count
accepted_answer_id	bigint(20)	NO		0	accepted answer id
last_answer_id	bigint(20)	NO		0	last answer id
post_update_time	timestamp	YES		CURRENT_TIMESTAMP	answer the last update time
revision_id	bigint(20)	NO		0	revision id

report

report table records the content of user reports

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
user_id	bigint(20)	NO			reporter user id
object_id	bigint(20)	NO			object id
reported_user_id	bigint(20)	NO		0	reported user id
object_type	int(11)	NO		0	revision type
report_type	int(11)	NO		0	report type
content	text	NO			report content
flagged_type	int(11)	NO		0	flagged type
flagged_content	text	YES			flagged content
status	int(11)	NO		1	status(normal: 1; pending:2; delete: 10)

revision

revision table records the object revision and the content of the version.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
user_id	bigint(20)	NO		0	user id
object_type	int(11)	NO		0	revision type(question: 1; answer 2; tag 3)
object_id	bigint(20)	NO	MUL	0	object id
title	varchar(255)	NO			title
content	text	NO			content
log	varchar(255)	YES			log
status	int(11)	NO		1	revision status(normal: 1; delete 2)
review_user_id	bigint(20)	NO		0	review user id

role

role table records all roles

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	int(11)	NO	PRI
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
name	varchar(50)	NO			name
description	varchar(200)	NO			description

role_power_rel

role_power_rel records the relationship between roles and powers

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	int(11)	NO	PRI
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
role_id	int(11)	NO		0	role id
power_type	varchar(200)	NO			power

site_info

site_info table records the site information about interface or something related

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	int(11)	NO	PRI		id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
type	varchar(64)	NO			type
content	mediumtext	NO			content
status	int(11)	NO		1	site info status(available: 1;deleted: 10)

tag

tag table records the tag information.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		tag_id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
main_tag_id	bigint(20)	NO		0	main tag id
main_tag_slug_name	varchar(35)	NO			main tag slug name
slug_name	varchar(35)	NO	UNI		slug name
display_name	varchar(35)	NO			display name
original_text	mediumtext	NO			original comment content
parsed_text	mediumtext	NO			parsed comment content
follow_count	int(11)	NO		0	associated follow count
question_count	int(11)	NO		0	associated question count
status	int(11)	NO		1	tag status(available: 1;deleted: 10)
revision_id	bigint(20)	NO		0	revision id

tag_rel

tag_rel table records the relationship between objects and tags

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		tag_list_id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
object_id	bigint(20)	NO	MUL		object_id
tag_id	bigint(20)	NO	MUL		tag_id
status	int(11)	NO		1	tag_list_status(available: 1;deleted: 10)

uniqid

uniqid table recorded the object_id that can uniquely identify an object.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		uniqid_id
uniqid_type	int(11)	NO		0	uniqid_type

user

The user table recorded basic information about the user.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	bigint(20)	NO	PRI		user id
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
suspended_at	timestamp	YES			suspended time
deleted_at	timestamp	YES			delete time
last_login_date	timestamp	YES			last login date
username	varchar(50)	NO	UNI		username
pass	varchar(255)	NO			password
e_mail	varchar(100)	NO			email
mail_status	tinyint(4)	NO		2	mail status(1 pass 2 to be verified)
notice_status	int(11)	NO		2	notice status(1 on 2off)
follow_count	int(11)	NO		0	follow count
answer_count	int(11)	NO		0	answer count
question_count	int(11)	NO		0	question count
rank	int(11)	NO		0	rank
status	int(11)	NO		1	user status(available: 1; deleted: 10)
authority_group	int(11)	NO		1	authority group
display_name	varchar(30)	NO			display name
avatar	varchar(255)	NO			avatar
mobile	varchar(20)	NO			mobile
bio	text	NO			bio markdown
bio_html	text	NO			bio html
website	varchar(255)	NO			website
location	varchar(100)	NO			location
ip_info	varchar(255)	NO			ip info
is_admin	int(11)	NO		0	admin flag(deprecated)

user_role_rel

The user_role_rel table records the relationship between users and roles.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	int(11)	NO	PRI
created_at	timestamp	YES			create time
updated_at	timestamp	YES			update time
user_id	bigint(20)	NO		0	user id
role_id	int(11)	NO		0	role id

version

The version of the current answer is recorded in version table for upgrade.

COLUMN	DATA TYPE	NULLABLE	KEY	DEFAULT	COMMENT
id	int(11)	NO	PRI		id
version_number	int(11)	NO		0	version_number