Scribd
Upload
4 views4 pages

User and Dataset Management Schema

The document outlines the database schema for a data management system, detailing various entities such as Users, Datasets, Sources, Locations, Organizations, and their respective attributes. It includes relationships between these entities, such as foreign keys and enumerated types for roles and statuses. Additionally, it covers aspects like dataset access logs, comments, ratings, analytics, notifications, and user roles and permissions.

Uploaded by

edeljhondy05
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views4 pages

User and Dataset Management Schema

The document outlines the database schema for a data management system, detailing various entities such as Users, Datasets, Sources, Locations, Organizations, and their respective attributes. It includes relationships between these entities, such as foreign keys and enumerated types for roles and statuses. Additionally, it covers aspects like dataset access logs, comments, ratings, analytics, notifications, and user roles and permissions.

Uploaded by

edeljhondy05
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Users

user_id (Primary Key)

name (VARCHAR)

email (VARCHAR, Unique)

organization_id (Foreign Key → Organizations)

role (ENUM: "Admin", "Data Contributor", "Researcher", "General User")

password_hash (VARCHAR)

date_joined (DATETIME)

last_login (DATETIME)

Datasets
dataset_id (Primary Key, Auto Increment)

title (VARCHAR)

description (TEXT)

source_id (Foreign Key → Sources)

organization_id (Foreign Key → Organizations)

category_id (Foreign Key → Categories)

tags (TEXT)

created_at (DATETIME)

updated_at (DATETIME)

status (ENUM: "Published", "Draft", "Archived")

license_id (Foreign Key → Licenses)

visibility (ENUM: "Public", "Restricted", "Private")

Sources
source_id (Primary Key, Auto Increment)

name (VARCHAR)

organization_id (Foreign Key → Organizations)

contact_email (VARCHAR)

website_url (VARCHAR)

Locations
location_id (Primary Key, Auto Increment)
name (VARCHAR)

country_code (VARCHAR)

region (VARCHAR)

latitude (DECIMAL)

longitude (DECIMAL)

Organizations
organization_id (Primary Key, Auto Increment)

name (VARCHAR)

type (ENUM: "NGO", "Government", "Research Institute", "UN Agency", "Private


Sector")

contact_email (VARCHAR)

website_url (VARCHAR)

created_at (DATETIME)

Data Files
file_id (Primary Key, Auto Increment)

dataset_id (Foreign Key → Datasets)

file_name (VARCHAR)

file_type (ENUM: "CSV", "JSON", "XML", "GeoJSON", "PDF", etc.)

file_size (INTEGER)

upload_date (DATETIME)

download_url (VARCHAR)

checksum (VARCHAR) (for integrity verification)

Dataset Access Logs


log_id (Primary Key, Auto Increment)

user_id (Foreign Key → Users)

dataset_id (Foreign Key → Datasets)

access_time (DATETIME)

action (ENUM: "Viewed", "Downloaded", "Updated")


Dataset Tags
tag_id (Primary Key, Auto Increment)

dataset_id (Foreign Key → Datasets)

tag_name (VARCHAR)

Dataset Categories
category_id (Primary Key, Auto Increment)

name (VARCHAR)

description (TEXT)

Dataset Licensing
license_id (Primary Key, Auto Increment)

name (VARCHAR)

description (TEXT)

url (VARCHAR)

Dataset Versions
version_id (Primary Key, Auto Increment)

dataset_id (Foreign Key → Datasets)

version_number (VARCHAR)

release_date (DATETIME)

notes (TEXT)

Dataset Comments
comment_id (Primary Key, Auto Increment)

dataset_id (Foreign Key → Datasets)

user_id (Foreign Key → Users)

comment_text (TEXT)

timestamp (DATETIME)

Dataset Ratings
rating_id (Primary Key, Auto Increment)
dataset_id (Foreign Key → Datasets)

user_id (Foreign Key → Users)

rating (DECIMAL) (1-5 scale)

comment (TEXT)

Dataset Analytics
analytics_id (Primary Key, Auto Increment)

dataset_id (Foreign Key → Datasets)

total_views (INTEGER)

total_downloads (INTEGER)

last_accessed (DATETIME)

Notifications
notification_id (Primary Key, Auto Increment)

user_id (Foreign Key → Users)

message (TEXT)

status (ENUM: "Unread", "Read")

created_at (DATETIME)

User Roles & Permissions


role_id (Primary Key, Auto Increment)

role_name (VARCHAR)

description (TEXT)

User-Permissions (Many-to-Many Relationship)


user_id (Foreign Key → Users)

role_id (Foreign Key → User Roles)

assigned_at (DATETIME)

You might also like