PHP, MySQL, Apache, Xampp y otras hierbas - Diseño de la base de datos parte I

Esta vez vamos a ver un tema mas que medular a la hora de hacer un sistema o web y es donde vamos a almacenar los datos necesarios para el funcionamiento de nuestra pagina (estadísticas, registros, visitas, etc).

¿por que es medular? pues mucha gente no le da la importancia necesaria al diseño de la base de datos (que debería ser producto de un buen análisis de sistemas) o desconocen o no entienden correctamente la normalización y la integridad de los datos, los cuales si no son bien pensados a la hora de querer obtener datos y reportes, se vuelve en un verdadero caos y reto sacar la data de forma lógica.

Pues bien, para poder expresarme mejor y no nos sintamos tan aburridos como en las primeras partes de esta serie de “tips” comenzaremos a ponernos manos a la obra, se me ocurre hacer un proyecto lo mas completo posible para que podamos ver todas las funcionalidades, ventajas y mas bla bla bla que digo en un proyecto mas o menos real, ya que por ahí se encuentran solo porciones o pedazos que al final nos da mas dolor de cabeza intentando entenderlo que lo que las funcionalidades que tiene cuando ya lo logramos entender y hacer funcionar. Para no acongojarnos desde el inicio, empezaremos por lo mas básico de nuestro sitio que es el registro y login (ingreso) de usuarios, y para adelantarles, el sitio que haremos funcionará en varios países manteniendo usuarios individuales por país asi que manos a la obra.

Lo mas importante en todo lo que hacemos en la vida son los estándares (insisto) así que deben considerar y meterse en la cabeza que “debo hacer las cosas lo mas estándar posible” con esto me refiero a que la forma en que nombraremos nuestra base de datos, los usuarios de la base de datos, las tablas, etc mantenga un cierto orden lógico y estándar ¿por que? pues como siempre, porque lo digo yo :D,  de esta manera cualquiera que vea nuestro diseño a parte de que le de envidia, lograremos que sea fácil de recordar los nombres de campos, tablas, etc asi que para nuestro caso adoptaremos como norma lo siguiente:

1. A las bases de datos les pondremos el prefijo “bd_” (sin las comillas por favor). Ej: bd_mibase
2. Los usuarios de la base de datos tendrán el prefijo “usr_”. Ej: usr_haroldcrow
3. Las tablas principales se nombraran utilizando la palabra plural de la data que contienen y siempre en minúsculas. Ej: paises, usuarios, visitas
4. Las tablas secundarias (tipos, estados, etc) se nombraran utilizando el prefijo “tipos_de/estados_de” seguido de la tabla principal en singular. Ej: estados_de_pais, tipos_de_visita, tipos_de_usuario, estados_de_usuario, etc.
5. Para el caso de los procedimientos almacenados y funciones de base de datos establecer un estándar es un poco complicado, pero haremos lo siguiente, el prefijo será “sp/fu” (respectivamente)  y luego debemos hacer 4 procedimientos para realizar las 4 funciones básicas de una base de datos SQL (Insert, Delete, Select, Update) los cuales nos ayudaran en la parte administrativa en todos los casos y solo algunos utilizaremos en el front-end pero es recomendable tener los 4 básicos siempre a la mano; entonces luego del prefijo continuaremos con la letra “I/D/S/U/*sall” segun corresponda y el nombre de la tabla. Ej: sp_ipaises, sp_dpaises, fu_upaises, etc. *sall: usaremos S cuando nos devuelva un registro en especifico y sall cuando nos devuelva varias filas.

Nota: el estándar que utilicen para sus tablas, bases de datos, funciones y SP, queda muy a su criterio y lógica, pero si no tienes uno o el que tienes no te satisface, te recomiendo que utilices este que te propongo pues yo ya tengo mas de 10 años con él y le ha sido útil a muchas personas.

Lo primero que haremos es crear las primeras tablas en nuestra base de datos con el heidi o comandos, las cuales serán:

• Paises: Contendrá la información básica de los paises donde operará la pagina y su configuracion.
• Usuarios: contendrá el listado de usuarios registrados en el sitio por pais.
• estados_de_usuario: contiene la lista de los diferentes estados que pueda tener un usuario (activo, inactivo, bloqueado, etc)
• tipos_de_usuario: esta tabla contiene los tipos de usuario que podemos tener por ejemplo si quisieramos diferenciar acciones que puedan realizar los usuarios dependiendo de esta clasificacion.

Código SQL para la base de datos y tablas:

Base de datos

CREATE DATABASE IF NOT EXISTS `db_shopping` /*!40100 DEFAULT CHARACTER SET utf8 */;

Tablas

CREATE TABLE `paises` (
`pais_id` INT(11) NOT NULL AUTO_INCREMENT,
`pais_nombre` VARCHAR(50) NULL DEFAULT NULL,
`pais_codigo` CHAR(2) NULL DEFAULT NULL,
`pais_simbolo_moneda` CHAR(10) NULL DEFAULT NULL,
`pais_moneda_nombre` CHAR(20) NULL DEFAULT NULL,
`pais_moneda_nombre_plural` CHAR(20) NULL DEFAULT NULL,
`pais_moneda_iso` CHAR(3) NULL DEFAULT NULL,
`pais_estado` ENUM('Testing','Produccion') NULL DEFAULT NULL,
`pais_activo` ENUM('Activo','Inactivo') NULL DEFAULT NULL,
`pais_url_testing` VARCHAR(100) NULL DEFAULT NULL,
`pais_url_produccion` VARCHAR(100) NULL DEFAULT NULL,
`pais_timezone` CHAR(50) NULL DEFAULT NULL,
`pais_email_contacto` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`pais_id`),
UNIQUE INDEX `codigo` (`codigo`),
UNIQUE INDEX `codigo_2` (`codigo`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

CREATE TABLE IF NOT EXISTS `estados_de_usuario` (
`usuario_estado_id` int(11) NOT NULL AUTO_INCREMENT,
`usuario_estado_nombre` varchar(50) DEFAULT NULL,
PRIMARY KEY (`usuario_estado_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `estados_de_usuario` (`usuario_estado_id`, `usuario_estado_nombre`) VALUES
(1, 'Creado'),
(2, 'Activado'),
(3, 'Bloqueado');

CREATE TABLE IF NOT EXISTS `tipos_de_usuario` (
`tipo_de_usuario_id` int(11) NOT NULL AUTO_INCREMENT,
`tipo_de_usuario_nombre` varchar(50) DEFAULT NULL,
PRIMARY KEY (`tipo_de_usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tipos_de_usuario` (`tipo_de_usuario_id`, `tipo_de_usuario_nombre`) VALUES
(1, 'Normal'),
(2,'Preferencial');

CREATE TABLE IF NOT EXISTS `usuarios` (
`pais_codigo` char(2) DEFAULT NULL,
`usuario_id` int(11) NOT NULL AUTO_INCREMENT,
`usuario_nombres` varchar(50) DEFAULT NULL,
`usuario_apellidos` varchar(50) DEFAULT NULL,
`usuario_email` varchar(100) DEFAULT NULL,
`usuario_clave` char(32) DEFAULT NULL,
`usuario_fecha_nacimiento` date DEFAULT NULL,
`usuario_telefono_casa` varchar(20) DEFAULT NULL,
`usuario_telefono_celular` varchar(20) DEFAULT NULL,
`usuario_sexo` enum('Femenino','Masculino') DEFAULT NULL,
`usuario_fecha_registro` datetime DEFAULT NULL,
`usuario_estado_id` int(11) DEFAULT NULL,
`usuario_fecha_activacion` datetime DEFAULT NULL,
`usuario_fecha_bloqueo` datetime DEFAULT NULL,
`usuario_codigo_activacion` varchar(100) DEFAULT NULL,
`usuario_spam` int(5) DEFAULT '0',
`usuario_facebook_id` varchar(50) DEFAULT NULL,
`tipo_de_usuario_id` int(11) DEFAULT NULL,
PRIMARY KEY (`usuario_id`),
UNIQUE KEY `cod_email` (`pais_codigo`,`usuario_email`),
KEY `codigo_3` (`pais_codigo`,`usuario_id`),
KEY `tipos_de_usuario_id` (`tipo_de_usuario_id`),
KEY `usuario_estado_id` (`usuario_estado_id`),
KEY `cod_ema_cla` (`pais_codigo`,`usuario_email`,`usuario_clave`),
CONSTRAINT `FK_usuarios_user_estados` FOREIGN KEY (`usuario_estado_id`) REFERENCES `estados_de_usuario` (`usuario_estado_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

Con esto ya tenemos listas la base de datos y las primeras tablas a utilizar. Si quieres descargar el script para las tablas puedes hacerlo aquí.

Nota: Podrán notar un pequeño detalle, y es que si ven la tabla paises y la tabla usuarios, el campo codigo_pais se repite en la tabla usuarios, y realmente el par codigo_pais,usuario_email se volverá nuestro índice único (unique) y espero poder explicar esto bien, si hubiese en este momento una tabla adicional que jerárquicamente este arriba de usuarios (es decir se relacione con usuarios, siendo la tabla usuarios el parent) esta tabla deberá contener este par de campos también, y con este par realizaremos la relación y así sucesivamente, por favor mantener en mente esto para que cuando creemos las siguientes tablas puedan comprender un poco mejor el concepto.

En la parte II crearemos los procedimientos almacenados a utilizar y probaremos su funcionamiento a nivel de código SQL.

 

Un saludo

PHP, MySQL, Apache, Xampp y otras hierbas exóticas de la programación de la realidad. Parte I-b

Un saludo, ahora sigo con la parte B de la primera parte. Si leyeron por completo la primera entrega en su ambiente de desarrollo ya tienen instalado y configurado:


1) Servidor Web con PHP y MySQL con un usuario con privilegios suficientes
2) PHP Designer y
3) HeidiSQL


COLLATION Y CHARSETS

Ahora bien, algo que siempre nos rompe la cabeza es cuando hemos pasado programando un buen rato y ya tenemos buenos avances, y a alguien se le ocurre probar con tildes y ñ's y que pasa??? se muestra mal en la pagina y/o peor aun la información queda guardada en la base de datos con unos caracteres tan raros que te hacen recordar a Q*bert, como evitamos esto? pues es muy sencillo de hacer, todo esta en que tengamos un buen sentido del "estándar" (algo así como un sentido de orientación), lo que sucede es que tanto tu base de datos y su configuración manejan "juegos de caracteres (charset)", "collation" y la conexión a ella también tiene un juego de caracteres... también tu webserver, osea apache, maneja un chartset default, y tu HTML generado por el lenguaje de programación osea PHP también usa un tipo de caracteres (cuando hable de juego de caracteres, charsets, collation, tipo de caracteres, etc deben recordar que conceptualmente no es lo mismo, pero nos referimos a lo mismo).


Ahora bien, la mayoría de personas la palabra "estandar" le suena a arameo antiguo y a veces es como la religión, todos "son", "pertenecen" y saben que es, pero no practican para nada el concepto, lo mismo nos pasa con ser estándares, a veces por ignorancia otras por arrogancia, y muchas veces por "huevonancia", pero como hoy estamos viendo las cosas desde un punto de vista diferente, vamos a estandarizar nuestros charsets y collation desde un inicio para que todo nos quede bien desde el inicio.


Primero debemos tomar en cuenta donde estará hospedada la aplicación/web, a) si es en un lugar donde podemos manipular todas las configuraciones (php, apache, mysql), o b) en un lugar donde algunas configuraciones están predeterminadas y no tenemos acceso como en la mayoría de los webhostings.
Si tenemos el ambiente (a) es el mas sencillo, si tenemos el ambiente (b) es un poco mas complicado porque dependerá mucho de la configuración predeterminada que tenga, pero igual con un poco de código siempre podremos arreglar esto.


Configurando el ambiente
Tomemos el ambiente (a), empecemos por configurar Apache (como asumo que me hicieron caso y están usando XAMPP) busquen el archivo que esta en la carpeta conf de apache llamado httpd.conf y agregamos al final la siguiente linea:

AddDefaultCharset utf-8

Ademas, asegurense que en el archivo llamado charset.conv contenga esta linea (sino la agregan)

UTF-8       utf8        UTF-8

Reiniciamos apache, y con esto basta para con Apache.


Ahora vamos con MySQL, y es aquí donde debemos de tener un poco mas de cuidado ya que nuestra decisión puede ser difícil de revertir luego de haber creado bases de datos y tablas, así que estén seguros de su decisión primero, por ejemplo si eligieran que el charset default de mysql fuera UTF-8 y Collation utf8_general_ci y luego resulta que necesitaban que collation fuera utf8_unicode_ci, cambiar su BD y tablas les juro que les dará un poco de dolor de cabeza, así que les recomiendo crear una BD de pruebas y una tabla con un par de campos de caracteres (char, varchar, text) y hacer pruebas extrayendo y guardando datos desde php para asegurarnos que todo esta bien (es decir, hacer lo que les digo).
Ok, nos vamos al archivo my.ini que esta en la carpeta xampp\mysql\bin y agregan las siguientes lineas:

init-connect='SET NAMES utf8'

character-set-server=utf8

collation-server=utf8_general_ci

La primera linea nos define la conexión, la segunda el charset y la tercera el collation, ahora bien, esto es para que por default el ambiente quede así, pero no significa que no lo podamos cambiar con comandos ya sea de mysql o de php, así que ojo.
Guardamos my.ini y reiniciamos el servicio de MySQL, y con esto tenemos listo casi todo.


Por ultimo, y no por eso menos importante, esta el charset que usaremos en nuestro HTML, esto se define con el siguiente meta tag (si no sabes que es un meta tag, mejor búscate otro oficio por favor jajajja ) 

< meta http-equiv="content-type" content="text/html; charset=UTF-8" >

Y con esto tendríamos configurado todo el ambiente para que no tengamos problemas con los caracteres latinos (tildes, eñes, etc).




Nota: si quieren saber como esta configurada su base de datos, pueden ejecutar el siguiente comando show variables like '%collation%'

Si todas las variables tienen lo mismo, estén seguros que no tendrán problemas con la base de datos, deberia mostrarles algo asi:

Espero que les halla sido util lo que escribi, y si no les fue util... pues ni modo xD, si llegaron a este post y no han leido el primero, pueden leerlo aqui.


Saludos.

PHP, MySQL, Apache, Xampp y otras hierbas exóticas de la programación de la realidad. Parte I

CREANDO EL AMBIENTE DE DESARROLLO


Introducción

Bueno, luego de haberme perdido algunos días, quiero comenzar a darle un enfoque de programación al blog, ¿por que? bueno se que a muchos no les importa el motivo, pero bueno, programar y hacer bases de datos es lo  que mejor hago, y esta será una manera de dejar un poco de lo que he aprendido en mi tiempo como programador, analista de sistemas, es decir todologo, a la humanidad, será un granito de conocimiento, pero se que a mas de uno le puede servir.

Luego de la introducción, si aun no se han aburrido, o no se han cortado las venas (verdad Miguel) quiero aclarar que la información que se presentará no esta destinada a aquellos que empiezan a programar (en general) es decir esta destinado a aquellos que ya saben programar un poco, lastimosa y desgraciadamente la pedagogia no es una fortaleza con la que cuento, soy pésimo enseñando a otros, pero haré mi mejor esfuerzo para explicarme lo mejor que pueda, y si no me sale bien, pues me contactan y ya jajajja.

Bueno, para empezar a usar PHP con MySQL es necesario que tengan un webserver configurado, claro, APACHE es genial, y pues, podríamos hacer una configuración individual, es decir, bajarnos Apache, luego PHP y MySQL, y claro complicarnos un poquitin la vida instalándolos y configurándolos uno por uno (no es la gran cosa pero no es el objetivo), y de hecho es lo que recomiendo, pero para este caso que trabajaremos en un ambiente de desarrollo, recomiendo que mejor se bajen XAMPP donde ya viene integrado todo y con unas mínimas configuraciones pueden dejarlo funcional para un ambiente aceptable de trabajo y desarrollo. Y luego les recomiendo bajar php designer que es muy bueno para escribir el codigo, pero al final escribelo donde quieras, notepad, notepad++, dreamweaver, etc realmente es opcional, yo te recomiendo php designer.
Y por ultimo, te recomiendo usar algun software para manejar las bases de datos, de mi gusto estan heidisql y sqlyog, puedes utilizar el que te guste, yo te recomiendo heidiSQLy para los que tienen plata pueden usar EMS Mysql Manager.


Nota: El ambiente de trabajo elegido por uds. es muy su prob. puede ser windows, gnu/linux, mac, etc pero quiero aclarar POR QUE HE ELEGIDO WINDOWS para el proceso de estos posts. Es muy sencillo, para bien o para mal, windows es el ambiente laboral mas común, me atrevería a decir que un 98% de las compañías trabajan y te obligan a trabajar con él, y el objetivo en si de estas publicaciones es que puedas desempeñarte mas tranquilamente en lo que vas a trabajar, pero a nivel de código de programación todo es igual, aqui y en la china asi que el ambiente es tu decisión.


Bueno luego de tener configurado tu servidor web (Apache + PHP + Mysql) quiero aclarar que utilizaremos mysqli para conectarnos a la BD, ¿por que? porque así quiero yo xD jaja, bueno no daré mayores detalles del porque pero para resumir les puedo decir que es mas rápida la conexión con mysql y es mas eficiente. 


Bueno recuerden, deben tener instalado (segun mis recomendaciones):

1. Apache + PHP + MySQL = XAMPP
2. Tener usuario para MySQL preferiblemente root o con GRANT para que puedan realizar todo lo que vamos a ver
3. Instalar PHP Designer
4. Instalar heidisql

Bueno por aquí dejamos la primera parte, en la siguiente parte, mostraré como hacer para que no tengamos problemas con las tildes, eñes y mas caracteres unicode en nuestro desarrollo... aja que pensaban, que estos detalles no los iba a tomar en cuenta!! y pues para aquellos que creyeron que solo iban a entrar y a programar pues se equivocaron, deben leer un poco para ser eficientes, porque muchos saben programar pero detalles pequeños les dan buenos tropiezos.

Nos vemos en el próximo post, nose cuando, recuerden, programar muchos pueden, pero hacer las cosas bien, es de pocos.