Database Schema

Overview

Sistema Magdaleno uses a MySQL database (venezuela_naciones) with a comprehensive schema supporting content management, user authentication, ACL permissions, and business operations. The database follows CakePHP naming conventions with snake_case table and column names.

Core Tables

Users & Authentication

users

Stores user account information and credentials.

Column	Type	Description
`id_usuario`	INT(11)	Primary key (auto-increment)
`username`	VARCHAR(45)	Unique username for login
`email_usuario`	VARCHAR(45)	User email address
`password`	VARCHAR(45)	Hashed password (SHA1)
`perfil_usuario`	VARCHAR(255)	User’s full name/profile
`fecharreg_usuario`	TIMESTAMP	Registration timestamp
`groups_idgrupos`	INT(11)	Foreign key to groups table

Relationships:

belongsTo Group (via groups_idgrupos)
Integrates with Aro for ACL

groups

Defines user permission groups.

Column	Type	Description
`idgrupos`	INT(11)	Primary key (auto-increment)
`name_grupos`	VARCHAR(45)	Group identifier (e.g., “administradores”)
`titulo_grupos`	VARCHAR(45)	Group display title
`fechacrea_grupo`	TIMESTAMP	Creation timestamp

Relationships:

hasMany Users
Integrates with Aro for ACL

Access Control Lists (ACL)

acos (Access Control Objects)

Defines the tree structure of controllable resources.

Column	Type	Description
`id`	INT(10) UNSIGNED	Primary key (auto-increment)
`parent_id`	INT(10)	Parent ACO node for tree structure
`model`	VARCHAR(255)	Associated model name
`foreign_key`	INT(10) UNSIGNED	Foreign key to model
`alias`	VARCHAR(255)	Resource identifier (controller/action)
`lft`	INT(10)	Left value for nested set
`rght`	INT(10)	Right value for nested set

Structure:

Root node: controllers (id=1)
Child nodes: Controller names (Pages, Users, Galleries, etc.)
Leaf nodes: Action names (index, add, edit, delete, view)

aros (Access Request Objects)

Defines the tree structure of permission requesters (users/groups).

Column	Type	Description
`id`	INT(10) UNSIGNED	Primary key (auto-increment)
`parent_id`	INT(10)	Parent ARO node for tree structure
`model`	VARCHAR(255)	Model name (Group or User)
`foreign_key`	INT(10) UNSIGNED	Foreign key to Group or User
`alias`	VARCHAR(255)	Requester alias
`lft`	INT(10)	Left value for nested set
`rght`	INT(10)	Right value for nested set

Structure:

Groups at top level
Users nested under their respective groups

aros_acos

Junction table linking AROs to ACOs with CRUD permissions.

Column	Type	Description
`id`	INT(10) UNSIGNED	Primary key (auto-increment)
`aro_id`	INT(10) UNSIGNED	Foreign key to aros
`aco_id`	INT(10) UNSIGNED	Foreign key to acos
`_create`	CHAR(2)	Create permission (0, 1, -1)
`_read`	CHAR(2)	Read permission (0, 1, -1)
`_update`	CHAR(2)	Update permission (0, 1, -1)
`_delete`	CHAR(2)	Delete permission (0, 1, -1)

Permission Values:

1 = Allow
0 = Deny
-1 = Inherit from parent

Content Management

galleries

Stores image gallery information.

Column	Type	Description
`id_galeria`	INT(11)	Primary key (auto-increment)
`texto_galeria`	VARCHAR(255)	Gallery title
`publicar`	INT(11)	Publication status (0/1)
`descripcion`	TEXT	Gallery description
`thumbnails`	TEXT	Thumbnail image filename
`url`	VARCHAR(255)	SEO-friendly URL slug
`fechacre_galeria`	TIMESTAMP	Creation timestamp
`usuario_id_usuario`	INT(11)	Foreign key to users
`excluir`	TINYINT(4)	Exclusion flag (default: 0)
`claves`	VARCHAR(255)	Keywords/tags

Relationships:

belongsTo User
hasAndBelongsToMany Archivo (via archivos_galleries)

archivos (files)

Stores uploaded file metadata.

Column	Type	Description
`id_file`	INT(11)	Primary key (auto-increment)
`nombre_file`	VARCHAR(45)	Original filename
`vidthumbnail`	VARCHAR(255)	Video thumbnail
`embedthumb`	VARCHAR(255)	Embedded thumbnail
`viddesta`	VARCHAR(255)	Featured video indicator
`nombre_gal`	VARCHAR(255)	Gallery name
`nombre_thumb`	VARCHAR(255)	Thumbnail name
`dir`	VARCHAR(255)	Directory path
`fechacre_file`	TIMESTAMP	Upload timestamp
`mimetype`	VARCHAR(45)	MIME type
`tipodispositivo_file`	VARCHAR(45)	Device type
`usuario_id_usuario`	INT(11)	Foreign key to users

archivos_galleries

Junction table linking files to galleries.

Column	Type	Description
`files_id_file`	INT(11)	Foreign key to archivos (primary)
`galerias_id_galeria`	INT(11)	Foreign key to galleries (primary)

videos

Stores video content and metadata.

Column	Type	Description
`id_videos`	INT(11)	Primary key (auto-increment)
`videoembed`	LONGTEXT	Embedded video HTML/code
`titulo_video`	VARCHAR(255)	Video title
`descripcion`	TEXT	Video description
`etiqueta`	VARCHAR(255)	Tags/labels
`destacado`	VARCHAR(255)	Featured status
`url`	VARCHAR(255)	SEO-friendly URL slug
`fechacre_videos`	TIMESTAMP	Creation timestamp
`usuario_id_usuario`	INT(11)	Foreign key to users
`archivos_id_file`	INT(11)	Foreign key to archivos (thumbnail)
`excluir`	TINYINT(4)	Exclusion flag (default: 0)
`video_home`	TINYINT(1)	Show on homepage (default: 0)

Column	Type	Description
`id_categorias`	INT(11)	Primary key (auto-increment)
`parent_id`	INT(11)	Parent category for hierarchy
`nombre_categorias`	VARCHAR(45)	Category name
`link_categorias`	VARCHAR(45)	URL slug
`estilo_categorias`	VARCHAR(45)	Style/theme
`fechacre_categorias`	TIMESTAMP	Creation timestamp
`status_categorias`	INT(11)	Status indicator
`usuario_id_usuario`	INT(11)	Foreign key to users
`categorias_id_categorias`	INT(11)	Self-referential foreign key

Business/Campaign Management

registers

User registration and campaign participation.

Column	Type	Description
`idregistro`	INT(11)	Primary key (auto-increment)
`nombreape`	VARCHAR(45)	Full name
`sexo`	CHAR(1)	Gender (M/F)
`fechanac`	DATE	Birth date
`direccion`	VARCHAR(45)	Address
`pais`	VARCHAR(45)	Country
`correo`	VARCHAR(45)	Email
`telefono`	BIGINT(20)	Phone number
`password`	VARCHAR(45)	Password
`rpass`	VARCHAR(255)	Repeated password
`estado`	VARCHAR(45)	State/province
`ciudad`	VARCHAR(255)	City
`codigo`	INT(11)	Code/verification
`tecon`	INT(255)	Terms accepted flag
`scor`	INT(11)	Score
`cuentatwitter`	VARCHAR(255)	Twitter handle
`fbookid`	VARCHAR(255)	Facebook ID
`fblinkperfil`	VARCHAR(255)	Facebook profile link
`foto`	VARCHAR(45)	Photo filename
`pais_campana`	VARCHAR(255)	Campaign country

campanas

Marketing campaigns.

Column	Type	Description
`id_campana`	INT(11)	Primary key (auto-increment)
`pais_campana`	VARCHAR(255)	Target country
`descripcion`	TEXT	Campaign description
`monto`	DOUBLE	Budget amount
`fecha`	VARCHAR(25)	Campaign date
`activo`	INT(11)	Active status (0/1)
`idpais`	INT(11)	Foreign key to paises

Geographic Data

paises (countries)

Country reference data.

Column	Type	Description
`paisId`	INT(11)	Primary key (auto-increment)
`isoNum`	SMALLINT(6)	ISO numeric code
`iso2`	CHAR(2)	ISO 2-letter code
`iso3`	CHAR(3)	ISO 3-letter code
`nombre`	VARCHAR(80)	Country name (Spanish)
`codtel`	INT(11)	Phone country code
`prioridad`	INT(11)	Display priority

estados (states)

Venezuelan states.

Column	Type	Description
`estadoId`	INT(8)	Primary key
`nombre`	VARCHAR(50)	State name
`ord`	INT(8)	Display order

ciudades (cities)

Venezuelan cities.

Column	Type	Description
`ciudadId`	INT(8)	Primary key (auto-increment)
`nombre`	VARCHAR(100)	City name
`estadoId`	INT(8)	Foreign key to estados
`ord`	INT(8)	Display order

Entity Relationship Diagram

┌─────────────┐
│   groups    │
│             │
│ idgrupos PK │◄──────┐
└─────────────┘       │
       │              │
       │ hasMany      │ belongsTo
       │              │
       ▼              │
┌─────────────────────┴───┐       ┌──────────────┐
│       users             │       │    aros      │
│                         │       │              │
│ id_usuario PK           │◄──────┤ foreign_key  │
│ groups_idgrupos FK      │       │ model='User' │
│ username                │       └──────────────┘
│ email_usuario           │              │
│ password                │              │ HABTM
└─────────────────────────┘              │
       │                                 ▼
       │ hasMany              ┌─────────────────┐
       │                      │   aros_acos     │
       │                      │                 │
       ▼                      │ aro_id FK       │
┌─────────────────┐           │ aco_id FK       │
│   galleries     │           │ _create         │
│                 │           │ _read           │
│ id_galeria PK   │           │ _update         │
│ usuario_id FK   │           │ _delete         │
└─────────────────┘           └─────────────────┘
       │                                 ▲
       │ HABTM                            │ HABTM
       │                                 │
       ▼                      ┌──────────────────┐
┌──────────────────┐          │      acos        │
│archivos_galleries│          │                  │
│                  │          │ id PK            │
│ files_id_file FK │          │ parent_id        │
│ galerias_id FK   │          │ alias            │
└──────────────────┘          │ (controllers/    │
       │                      │  actions)        │
       │                      └──────────────────┘
       ▼
┌─────────────────┐
│    archivos     │
│                 │
│ id_file PK      │
│ nombre_file     │
│ dir             │
│ mimetype        │
└─────────────────┘

Database Conventions

Naming Patterns

Table Names: Lowercase, plural, snake_case (e.g., archivos_galleries)
Primary Keys: id or {singular}_id (e.g., id_usuario, id_galeria)
Foreign Keys: {table_singular}_id{column} (e.g., usuario_id_usuario, groups_idgrupos)
Timestamps: fecha{action}_{table} (e.g., fechacre_galeria, fecharreg_usuario)
Junction Tables: {table1}_{table2} alphabetically ordered

Character Set & Engine

Default Character Set: UTF-8 (utf8) / Latin1 for some tables
Collation: latin1_swedish_ci / utf8_general_ci
Storage Engine: InnoDB for transactional tables, MyISAM for ACL tables

Key Indexes

Performance Indexes

-- Galleries URL lookup
INDEX `url` ON galleries(url);

-- User authentication
INDEX `fk_users_groups1` ON users(groups_idgrupos);

-- File-Gallery relationships
PRIMARY KEY ON archivos_galleries(files_id_file, galerias_id_galeria);

Database Initialization

The database is created with:

CREATE DATABASE `venezuela_naciones` 
  DEFAULT CHARACTER SET latin1 
  COLLATE latin1_swedish_ci;

All tables use AUTO_INCREMENT for primary keys and CURRENT_TIMESTAMP for creation timestamps where applicable.

MVC Structure - CakePHP model relationships
Authentication - ACL implementation details

Architecture

API Reference

Database Schema

Overview

Core Tables

Users & Authentication

users

groups

Access Control Lists (ACL)

acos (Access Control Objects)

aros (Access Request Objects)

aros_acos

Content Management

galleries

archivos (files)

archivos_galleries

videos

categories

Business/Campaign Management

registers

campanas

Geographic Data

paises (countries)

estados (states)

ciudades (cities)

Entity Relationship Diagram

Database Conventions

Naming Patterns

Character Set & Engine

Key Indexes

Performance Indexes

Database Initialization

Build docs developers (and LLMs) love

Architecture

API Reference

​Overview

​Core Tables

​Users & Authentication

​users

​groups

​Access Control Lists (ACL)

​acos (Access Control Objects)

​aros (Access Request Objects)

​aros_acos

​Content Management

​galleries

​archivos (files)

​archivos_galleries

​videos

​categories

​Business/Campaign Management

​registers

​campanas

​Geographic Data

​paises (countries)

​estados (states)

​ciudades (cities)

​Entity Relationship Diagram

​Database Conventions

​Naming Patterns

​Character Set & Engine

​Key Indexes

​Performance Indexes

​Database Initialization

​Related Documentation

Build docs developers (and LLMs) love

Overview

Core Tables

Users & Authentication

users

groups

Access Control Lists (ACL)

acos (Access Control Objects)

aros (Access Request Objects)

aros_acos

Content Management

galleries

archivos (files)

archivos_galleries

videos

categories

Business/Campaign Management

registers

campanas

Geographic Data

paises (countries)

estados (states)

ciudades (cities)

Entity Relationship Diagram

Database Conventions

Naming Patterns

Character Set & Engine

Key Indexes

Performance Indexes

Database Initialization

Related Documentation