Data structure

Data structure

This page describe the ER model and database tables of RePortal. The default drupal database structure is ignored here, only the users and node tables which are used directly by reportal.

ER model

(to be updated later)

Database description

Database Diagram

(to be updated later)

Tables Overview

Table name	Description	Module Folder
research_projects	Basic information of research project.	reProject
research_projects_operation_logs	Records all user operations belongs to research projects.	reProject
research_projects_persons_list	Stores the persons list of the project	reProject
research_projects_contents	Content nodes of the research project	reProjectContent
research_projects_onetime_invite_code	Stores the one-time invite code for research project.	reProjectUser
research_projects_participants_cards	Stores participants information for project	reProjectUser
research_projects_participants_tags	Stores tags that are used to group participants in the project.	reProjectUser
research_project_invitation_templates	Stores invitation templates contents for project.	reProjectUser
project_message_templates	Message templates contents for research projects	ProjectMessageTemplate
research_tools	Stores the information of research tools	reTool
research_project_tools_list	The list of tools for each project	reTool
research_tool_links	Stores the links related with the tools	reTool
research_project_tool_links	The links that related with the project but only used in specific project	reTool
research_project_user_profile	The specific user profile for project used	reProjectUserProfile

Table Details

Table: research_projects

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
creator	int	not null	FK		FK→users.uid
name	varchar(255)	not null
path	varchar(45)	not null			unique
url	varchar(255)
founder	varchar(255)	not null			The name of the project founder
introduction	text	not null
created	datetime	not null
changed	datetime	not null

Table: research_projects_operation_logs

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
operator	int	not null	FK		FK→users.uid
type	varchar(128)	not null			The category of the operation
ip	varchar(15)	not null			The ip address of the operator
content	text	not null
link	varchar(255)	not null			The link where operation happened
module	varchar(45)	not null			The module name where call the log function
time	datetime	not null			The time when operation logged

Table: research_projects_persons_list

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
user	int				Fk→users.uid
role	varchar(128)	not null
active	int(1)	not null		1
created	datetime	not null
changed	datetime	not null

Table: research_projects_contents

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
node	int	not null	FK		FK→node.nid
title_path	varchar(128)	not null			the path to visit the content page
permission	varchar(45)	not null		CONFIDENTIAL	e.g. CONFIDENTIAL, PUBLIC
delectable	int(1)	not null		1	User can delete the content from the project or not.
created	datetime	not null
changed	datetime	not null

Table: research_projects_onetime_invite_code

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
email	varchar(128)
phone	varcahr(20)
person	int		FK		FK→research_projects_persons_list.id
code	varchar(32)	not null			unique
used	int(1)	not null		0
created	datetime	not null
changed	datetime	not null

Table: research_projects_participants_cards

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
user	int		FK		FK→users.uid
name	varchar(45)	not null
email	varchar(255)
phone	varcahr(20)
tabs	varchar(255)			’’
notes	text				Some description or note about the participant
created	datetime	not null
changed	datetime	not null

Table: research_projects_participants_tags

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
tag	text	not null
created	datetime	not null
changed	datetime	not null

Table: research_project_invitation_templates

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
template	int	not null	FK		FK→project_message_templates.id
for_role	varchar(20)	not null
created	datetime	not null
changed	datetime	not null

Table: project_message_templates

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
title	varchar(255)	not null
type	varchar(16)	not null			e.g. email, sms
content	text	not null
created	datetime	not null
changed	datetime	not null

Table: research_tools

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
name	varchar(255)	not null
logo	varchar(255)				The logo image path
introduction	text	not null
created	datetime	not null
changed	datetime	not null

Table: research_project_tools_list

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
tool	int	not null	FK		FK→research_tools.id
created	datetime	not null

Table: research_tool_links

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
tool	int	not null	FK		FK→research_tools.id
title	varchar(45)	not null
url	varchar(255)	not null
created	datetime	not null
changed	datetime	not null

Table: research_project_tool_links

Field	Type	Null	Key	Default	Extra
id	serial(int)	not null	PRI		auto_increment
project	int	not null	FK		FK→research_projects.id
tool	int	not null	FK		FK→research_tools.id
title	varchar(45)	not null
url	varchar(255)	not null
permission	varchar(45)	not null		PUBLIC	e.g. PUBLIC, CONFIDENTIAL
weight	int(1)	not null		0
created	datetime	not null
changed	datetime	not null

Table: research_project_user_profile

Field	Type	Null	Key	Default	Extra
user	int	not null	FK		FK→users.uid
firstname	varchar(45)	not null		’’
lastname	varchar(45)	not null		’’
gender	varchar(1)	not null
birthday	varchar(10)	not null
weight	float				unit is kg
height	float				unit is cm
created	datetime	not null
changed	datetime	not null