Data Model

Overview

The DonaSF API uses the WSDonaciones SQL Server database to manage donations, missionaries, administrators, and receipt (talon) tracking. The schema is designed to support a donation management system with multi-level tracking and delivery confirmation.

Database Schema

The database consists of several interconnected entity groups:

Administration

Administrators and missionaries who manage the donation system

Donations

Donation keys (amounts), donors, and donation records

Receipts (Talones)

Receipt tracking across editions with state management

Delivery Tracking

Cash delivery records and delivery state management

Core Entities

Administration Entities

administrador

Administrators who oversee the donation system.

Column	Type	Description
`idadmin`	INT IDENTITY	Primary key
`nombre`	NVARCHAR(150)	Administrator name
`correo`	NVARCHAR(150)	Email (unique)
`telefono`	NVARCHAR(20)	Phone number
`activo`	BIT	Active status (default: 1)
`fechacreacion`	DATETIME	Creation date (default: GETDATE())

Key Characteristics:

Email must be unique
Soft delete via activo flag
Automatic timestamp on creation

misionero

Missionaries who collect donations and manage receipts.

Column	Type	Description
`idmisionero`	INT IDENTITY	Primary key
`nombre`	NVARCHAR(150)	Missionary name
`correo`	NVARCHAR(150)	Email
`telefono`	NVARCHAR(50)	Phone number
`activo`	BIT	Active status (default: 1)
`idadmin`	INT	Foreign key to administrador
`fecharegistro`	DATETIME	Registration date (default: GETDATE())

Relationships:

Each missionary belongs to one administrator
Constraint: fk_misionero_admin → administrador(idadmin)

Donation Configuration

clavedonativo

Predefined donation amounts with unique codes.

Column	Type	Description
`idclave`	INT IDENTITY	Primary key
`codigo`	NVARCHAR(10)	Unique donation code (e.g., “X”, “V”)
`cantidad`	DECIMAL(10,2)	Donation amount

Example Data:

INSERT INTO clavedonativo (codigo, cantidad) VALUES 
  ('X', 10.00),
  ('V', 20.00),
  ('Z', 50.00);

Constraints:

uq_codigo - Ensures each code is unique

estado

Receipt/donation states (e.g., “Pending”, “Collected”, “Delivered”).

Column	Type	Description
`idestado`	INT IDENTITY	Primary key
`descripcion`	NVARCHAR(100)	State description

estadoentrega

Delivery states for tracking cash delivery progress.

Column	Type	Description
`idestadoentrega`	INT IDENTITY	Primary key
`descripcionentrega`	NVARCHAR(100)	Delivery state description

Edition Management

edicion

Receipt editions with folio number ranges.

Column	Type	Description
`idedicion`	INT IDENTITY	Primary key
`nombre`	NVARCHAR(100)	Edition name
`folio_inicio`	INT	Starting folio number
`folio_fin`	INT	Ending folio number
`fechacreacion`	DATETIME	Creation date (default: GETDATE())

Constraints:

ck_rango_folio - Ensures folio_inicio < folio_fin

Purpose: Editions group receipts (talones) into batches with sequential folio numbers, allowing for organized distribution and tracking.

Receipt (Talon) Management

talon

Receipts assigned to missionaries for donation collection.

Column	Type	Description
`idtalon`	INT IDENTITY	Primary key
`folio`	INT	Receipt folio number
`idclave`	INT	Foreign key to clavedonativo
`idestado`	INT	Foreign key to estado
`idmisionero`	INT	Foreign key to misionero
`idedicion`	INT	Foreign key to edicion
`fecharegistro`	DATETIME	Registration date (default: GETDATE())

Constraints:

uq_folio_clave - Unique combination of folio and donation key
ck_folio_positivo - Folio must be positive
Foreign keys to: clavedonativo, estado, misionero, edicion

Business Rules:

Folio numbers must fall within the edition’s range
Each folio + clave combination is unique
Tracks receipt lifecycle through estado

Donation Tracking

donante

Donors who make donations.

Column	Type	Description
`iddonante`	INT IDENTITY	Primary key
`nombredonante`	NVARCHAR(150)	Donor name
`domicilio`	NVARCHAR(150)	Donor address

donacion

Actual donation records linking missionaries, receipts, donors, and amounts.

Column	Type	Description
`iddonacion`	INT IDENTITY	Primary key
`idmisionero`	INT	Foreign key to misionero
`idtalon`	INT	Foreign key to talon
`iddonante`	INT	Foreign key to donante
`idclave`	INT	Foreign key to clavedonativo
`idestado`	INT	Foreign key to estado
`idestadoentrega`	INT	Foreign key to estadoentrega

Relationships:

Six foreign key constraints linking to all related entities
Tracks both donation state and delivery state independently

Purpose: This is the central transaction table that records when a missionary collects a donation from a donor using a specific receipt for a particular amount.

Delivery Management

entregaefectivo

Cash delivery records tracking when donations are delivered.

Column	Type	Description
`identrega`	INT IDENTITY	Primary key
`idonacion`	INT	Donation ID (note: typo in schema, no FK)
`idedicion`	INT	Foreign key to edicion
`idtalon`	INT	Foreign key to talon
`idmisionero`	INT	Foreign key to misionero
`idestadoentrega`	INT	Foreign key to estadoentrega
`fechaentrega`	DATETIME	Delivery date (default: GETDATE())

Purpose: Tracks when missionaries deliver collected donations, organized by edition and receipt.

Entity Relationships

Primary Relationships

Workflow Relationships

Administrator Creates Missionary

An administrator (administrador) creates a missionary account (misionero) with idadmin linking them.

Edition and Receipts Created

An edition (edicion) is created with a folio range. Receipts (talon) are generated within this range, assigned to missionaries with specific donation keys (clavedonativo).

Donation Collected

A missionary collects a donation from a donor (donante). A donacion record is created linking the missionary, receipt, donor, donation key, and states.

Cash Delivered

The missionary delivers collected donations. An entregaefectivo record tracks the delivery with the edition, receipt, and delivery state.

Data Access Patterns

Stored Procedures

The API uses stored procedures for data operations, located in:

Database/ScriptDonaciones/Procedimientos/Procedimientos almacenados Donaciones.sql
Database/Stored Procedures/Procedimientos.sql

DataTable Returns

Data layer methods return DataTable objects that are processed in the business layer:

public InfoCompartidaCapas VerDonacion(JSONDonacioni jSONDonacioni)
{
    InfoCompartidaCapas info = new InfoCompartidaCapas();
    try
    {
        IDMDonacion consulta = new CDMDonacion(new DMDonacion());
        DataTable tabla = consulta.VerDonacion(jSONDonacioni);
        info.informacion = tabla;
        info.error = null;
    }
    catch (Exception ex)
    {
        info.informacion = null;
        info.error = ex.Message;
    }
    return info;
}

Result Handling

Stored procedures typically return results with:

Resultado (BIT) - Success/failure flag
resultados (NVARCHAR) - Message or error description
Additional columns with entity data

Key Design Decisions

Soft Deletes

The administrador and misionero tables use activo flags instead of hard deletes, preserving historical data and relationships.

Dual State Tracking

Donations track both donation state (idestado) and delivery state (idestadoentrega) independently, allowing granular workflow control.

Edition-Based Organization

Receipts are organized into editions with folio ranges, enabling batch management and reporting.

Unique Folio + Key Combination

The uq_folio_clave constraint ensures each receipt can only be used once per donation amount, preventing duplicate entries.

System Architecture - Three-tier architecture overview
Error Handling - Database error patterns
Donations API - Donation operations

Get Started

Core Concepts

Guides

Overview

Database Schema

Administration

Donations

Receipts (Talones)

Delivery Tracking

Core Entities

Administration Entities

administrador

misionero

Donation Configuration

clavedonativo

estado

estadoentrega

Edition Management

edicion

Receipt (Talon) Management

talon

Donation Tracking

donante

donacion

Delivery Management

entregaefectivo

Entity Relationships

Primary Relationships

Workflow Relationships

Data Access Patterns

Stored Procedures

DataTable Returns

Result Handling

Key Design Decisions

Build docs developers (and LLMs) love

Get Started

Core Concepts

Guides

​Overview

​Database Schema

Administration

Donations

Receipts (Talones)

Delivery Tracking

​Core Entities

​Administration Entities

​administrador

​misionero

​Donation Configuration

​clavedonativo

​estado

​estadoentrega

​Edition Management

​edicion

​Receipt (Talon) Management

​talon

​Donation Tracking

​donante

​donacion

​Delivery Management

​entregaefectivo

​Entity Relationships

​Primary Relationships

​Workflow Relationships

​Data Access Patterns

​Stored Procedures

​DataTable Returns

​Result Handling

​Key Design Decisions

​Related Documentation

Build docs developers (and LLMs) love

Overview

Database Schema

Core Entities

Administration Entities

administrador

misionero

Donation Configuration

clavedonativo

estado

estadoentrega

Edition Management

edicion

Receipt (Talon) Management

talon

Donation Tracking

donante

donacion

Delivery Management

entregaefectivo

Entity Relationships

Primary Relationships

Workflow Relationships

Data Access Patterns

Stored Procedures

DataTable Returns

Result Handling

Key Design Decisions

Related Documentation