como hacer la estructura de la base de datos para un contador general de visitas

[roboty]

Hola, alguien me podria orientar como tendria que hacer la estructura de la base de datos para poder contabilizar las visitas que tiene la web, osea las visitas que tiene cada dia y cada mes de cada año, alguna orientacion?
Saludos.

[Heimish2000]

Si sólo quieres el numero te basta dia y numero de visitas, aunque te recomiendo que guardes los datos de cada una de las visitas (por ejemplo, IP de quien hace la visita, fecha y hora, etc, etc)

[roboty]

Hola Heimish2000, gracias por contestar, comentarte que esa idea la tengo, osea guardar la ip de cada visita y si se conecta el mismo dia no contarla, la duda la tengo en como tengo que montar las tablas, yo pense en tener dos tablas una tabla seria las ip y fecha de visitas y la otra tabla seria un campo para el año y otra para el mes y 31 campos para cada dia del mes osea algo asin:

tabla guardar ip
campo_ip
campo_ultima_visita

tabla_visitas
campo_año_actual
campo_mes_actual
campo_dia_1
campo_dia_2
campo_dia_3
asin hasta...
campo_dia_31


No se si se entiendo lo que quiero hacer, de esta forma tengo que crear una tabla con 32 campos no se sihay otra manera de hacerlo, de esta forma tendria guardada todas mis visitas organizadas en cada dia total de visitas y total visitas al mes, asin sucesivamente con cada año, pero no se si hay otra forma mas correcta de hacerlo, un saludo.

[gnzsoloyo]

tabla_visitas
campo_año_actual
campo_mes_actual
campo_dia_1
campo_dia_2
campo_dia_3
asin hasta...
campo_dia_31

Esto no tiene ninguna utilidad ni sentido.
Es guardar un registro con montones de campos vacíos que puede que jamás se use.
Además no respeta ni siquiera la normalzación más básica.
Piensalo así:
Una visita tiene:
- Un usuario.
- Una IP desde donde el usuario se conecta.
- Una fecha y hora de ingreso.
Con esos tres datos tu puedes establecer toda la información necesaria:
- Cuándo fue su última visita.
- Cuantas veces entró.
- Cuantas veces entró en un día.
- En qué horarios entra.
Etc., etc.
Si además quieres el historial de navegación sólo deperías crear una tabla adicional relacionada con esa que especifique qué paginas y en qué orden visitó en la ocasión en que entró.

Es decir, sólo requiere una tabla de tres campos para la visita y de otros tres para la navegación (relación 1:N).

[roboty]

Hola gnzsoloyo, gracias por tu colaboracion, bueno tadavia no lo acabo de entender, aparte de eso lo que quiero es tener almacenado en mi base de datos todas las visitas que ha tenido mi web, osea las visitas que ha tenido cada dia y cada mes en el año que estemos, no se si me estoy liando y no lo entiendo pero es eso tener almacenado las estadisticas de visitas de mi web, por eso yo puse lo de los campos campo_dia_1, campo_dia_2,etc...

ya que de esta forma la primera linea de registro seria todas las visitas de un mes y aunque serian bastantes campos solo tendria una linea de registro por mes, no se si me explique bien, pero entonces como puedo lograr esto.

Saludos.

[David]

Hola, roboty.

Me parece que te estás confundiendo con qué es lo que necesitas almacenar. Sólo necesitas almacenar todas las visitas, de la forma en que te indicó gnzsoloyo; luego, las estadísticas las obtienes al vuelo con consultas SQL; a no ser que realmente necesites por alguna razón almacenar esas estadísticas también, pero no podría entender el motivo.

[roboty]

Hola David, pero por ejemplo si un usuario se conecta por ejemplo con una ip xxx y la fecha 20013-10-10 10:10 ahi actualizo el campo a 1 visita si por ejemplo se conecta al dia siguiente actualizo el campo visita que era 1 a 2 y la fecha entiendo que la actualizo a la ultima visita, hasta ahi es lo que entiendo pero claro si yo quiero mirar las visitas que tube en el 2012 como lo hago no podre ya que tengo que ir actualizando el campo fecha del usuario que se conecta nuevamente, no se si me explique, esque no acabo de entender la logica que debo usar, alguna ayuda, saludos.

[gnzsoloyo]

No actualizas.
Insertas un registro por cada visita. En eso consiste un sistema de visitas que soporte estadísticas.
Le agregarías entre 64 y 256 Bytes por vuelta, si manejas todo con valores numéricos (incluyendo las IP, que se pueden representar numéricamente).

En un sistema de visitas no existe el concepto de "actualizar". La trazabilidad se da por las altas de las visitas, una a una.

¿Alcanzas a ver el concepto?

[roboty]

Bueno ahora si que lo entiendo, pero tengo una duda, os la cuento para ver si me la aclarais.
imaginemos que tengo un cierto numero de registros (visitas), como tendria que hacer la consulta sql para recuperar las visitas de un dia en concreto por ejemplo del dia de ayer y claro teniendo en cuenta que la ip no se repita dos veces ya que un cliente puede entrar y salir varias veces en un mismo dia, algun ejemplo para poder entenderlo.
Saludos

[roboty]

bueno creo que seria algo asin:
partiendo de que tenemos estos campos de la tabla visitas

id_usuario
ip
fecha

SELECT * FROM visitas WHERE fecha=2012-28-11

Pero claro de esta forma me mostrara las visitas que tube ese dia pero como seria para que sean visitas reales osea que un usuario que se conecta 10 veces al dia me la cuenta como una visita.
Saludos.

[vimon]

Iniciado por roboty

bueno creo que seria algo asin:
partiendo de que tenemos estos campos de la tabla visitas

id_usuario
ip
fecha

SELECT * FROM visitas WHERE fecha=2012-28-11

Pero claro de esta forma me mostrara las visitas que tube ese dia pero como seria para que sean visitas reales osea que un usuario que se conecta 10 veces al dia me la cuenta como una visita.
Saludos.

Si pusieras

Código MySQL:

SELECT count(id_usuario), id_usuario, fecha FROM visitas group by  id_usuario, fecha
 

te daira las visitas de cada fecha por cada usuario y solo te sacaria un registro por cada fercha y usuario y a mas sabrias las visitas diarias

saludos

[roboty]

ok gracias ahora me mirare bien la consulta que hicistes para entenderla, aunque volviendo a lo de las visitas pero esta vez a lo de controlar la navegacion de cada visita que serian crear estos tres campos.
id_usuario
url_visitada
fecha

siendo relacional como comento gnzsoloyo, pero una vez que el usuario entra en mi web osea en el index ahi registro la visita pero como tendria que hacer para guardar las paginas que visita, seria con variables de session ya que necesito saber el id_usuario de la visita, se que este foro ya se va de lo que pregunto seria en el foro del lado del servidor e incluso del lado del cliente, pero ya que estoy aqui si me podeis dar alguna explicion de como seria la manera correcta de hacerlo.
Un saludo.

[gnzsoloyo]

Vamos a hacer una descripción más ajustada a tu caso:
Una visita tiene:
- Un usuario
- Un inicio (día y hora de entrada).
- Una IP de origen.
- Un ID de sesión (único por visita).

Cada visita además tiene varias páginas recorridas, y en cada caso hay:
- Sesión a la que pertenece
- Pagina a la que entró.
- Fecha y hora de acceso a la pagina.

en ese contexto, hay dos tablas:

VISITA(user_id, fecha_hora, ip_origen, sesion_id)
NAVEGACION_VISITA(sesion_id, fecha_ingreso, pagina_id)

En el caso de la primera tabla, la PK quedaría definida por (user_id, fecha_hora), teniendo una clave alternativa en sesion_id.
La segunda tabla tiene por PK (sesion_id, fecha_ingreso), siendo sesion_id la FK (MySQL admite usar un campo UNIQUE de una tabla como FK).
Para cumplir esto, cada vez que en la sesión de usuario navega a otra parte, debe enviar a insertar la página en la que entra en esa navegación. Puede parecer excesivo, pero si las conexiones son por sesión, esto no debería tener un mayor impacto en la performance.

¿Se entiende mejor la idea?

Ese tipo de estructura permitiría incluso el seguimiento específico de lo que hizo un usuario, y también cuáles son las páginas más visitadas en un site, o en que hora de qué dias hay mayor uso ed unas u otras, etc., todo con muy poco costo adicional.

[roboty]

Hola gnzsoloyo, pedona por mi torpeza pero no acabo de enteder de donde saco el valor para el campo sesion_id, no se si tendria que ser el mismo valor del campo usuario_id, asin sabria lo que hace cada visita idependientemente de que sea el mismo, o te refieres a que contenga un valor como por ejemplo la ip asin sabria lo que hace cada usuario que entra con la misma ip, espero que entiendas mi duda y me la puedas aclarar.

o te refieres con la sesion_id al usuario que esta registrado en mi web, ya que en mi web no haria falta registrarse.
Un saludo.

[gnzsoloyo]

no acabo de enteder de donde saco el valor para el campo sesion_id

¿Nunca generaste un valor de sesión en PHP (ver link)?
Es un valor único de identificación para una sesión de navegación (todas las aplicaciones en web donde te logueas hacen lo mismo), que te permite entre otras cosas administrar el paso de objetos entre las diferentes páginas, como por ejemplo con las que usan carritos de compras.
En general eso está más allá de MySQL, es programación (off-topic aquí), pero no es el único modo de generarlo.
Eso te conviene tratarlo en el foro de tu lenguaje de programación, pues ellos están más habituados a eso.

No todo se soluciona con la base de datos...

[roboty]

Hola gnzsoloyo, entiendo que para poder seguir el rastro necesito variables de session, pero lo que no entiendo porque en la tabla visitas pones el campo sesion_id, yo antes de tu explicacion ya cree las dos tablas de una manera distinta por eso luego al ver tu ultima explicacion mas detallada me quede dudoso de tu idea, en tu explicacion estas son tus dos tablas

VISITA(user_id, fecha_hora, ip_origen, sesion_id)
NAVEGACION_VISITA(sesion_id, fecha_ingreso, pagina_id)

y yo las habia creado asin:

VISITA(user_id, fecha_hora, ip_origen,)
NAVEGACION_VISITA(user_id, fecha_ingreso, pagina_id)

lo que yo hice fue cuando el usuario entra en mi web le creo una variable de sesion con el id que acabo de insertar en la tabla visita despues ese id es el que sigue la pista en la navegacion de la web que seria el user_id la FK de la tabla navegacion.

Esto es lo que hice pero seguro que por algun motivo que desconozco hay que crear aparte ese sesion_id y si pudieras darme una explicacion de porque ahi que hacerlo asin te lo agradeceria ya no por hacerlo y ya esta si no por entenderlo ya que estoy en proceso de aprendizaje, un saludo.

[roboty]

Bueno volvi para comentar que el codigo funciona perfectamente y aclarar gnzsoloyo que supuestamente las dos formas son validas pero lo pusistes asin a modo de ejemplo para poderlo entender, gracias nuevamente por enseñar a los mas novatos.

Saludos.

[gnzsoloyo]

Si te refieres a cómo generar un ID de sesión, te recuerdo que eso se hace en la programación, y es completamente off-topic de este foro. Por eso no posteo ejemplos de ese tipo.
Las Normas del Foro de Bases de Datos dicen específicamente que no se postea código de programación en este foro. Si quieres ejemplos de eso en PHP debes consultar en el Foro de PHP, o verificar en el manual de PHP on line.
También puedes simplemente buscar en esta forma: Crear ID de session en PHP

Broma aparte, hay mucho y muy documentado en la web sobre ese tema. Incluso en Maestros del Web, Desarrollo Web, etc, hay mucho de ese asunto.

[roboty]

Hola gnzsoloyo, no me entendistes bien dije q el codigo con la edtructura de la tabla ya me funcionaba bien, pero en tu ejemplo de estructura era asin:
tabla VISITAS

Usuario_id
fecha
ip
Sesion_id

tabla NAVEGACION

sesion_id
url
fecha

y yo te comentaba si asin es correco

VISITAS
usuario_id
Ip
fecha

NAVEGACION
usuario_id
Url
fecha

donde usuario_id esla FK

Es correco como lo hice?
Saludos

[gnzsoloyo]

!Ah! Ahora entiendo.
Bueno, lo que propones, puede andar, pero no podrías diferenciar las diferentes veces que un usuario ingresa en un mismo día con sesiones distintas.
Incluso, no te permitiría restringir el acceso indebido de una persona que use el mismo usuario desde otro terminal, con sesiones solapadas (superpuestas).
El uso de un ID de sesión es mucho mejor para darle trazabilidad al uso del sistema y a las visitas.
Por eso es algo muy habitual.
Una de las opciones que puedes usar es generar un identificador único con MySQL (si no quieres hacerlo en PHP), con la función uuid() de MySQL, que llamarías al momento del login del usuario en cuestión.
Este UUID es un identificador único se supone que a nivel planetario (o algo así):

UUID()

Retorna un Universal Unique Identifier (UUID) (Identificador Único Universal) generado según la “DCE 1.1: Remote Procedure Call” (Apéndice A) CAE (Common Applications Environment) Especificaciones publicadas por The Open Group en Octubre 1997 (Número de Documento C706).

Se designa un UUID como número que es único globalmente en espacio y tiempo. Dos llamadas a UUID() generan dos valores distintos, incluso si estas llamadas se realizan en dos máquinas separadas y no están conectadas entre ellas.

Un UUID es un número de 128 bits representado por una cadena de cinco números hexadecimales en formato aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:

Los primeros tres números se generan de un valor temporal.

El cuarto número preserva unicidad temporal en caso de que el valor temporal pierda moniticidad (por ejemplo, debido al cambio horario).

El quinto número es un número IEEE 802 de nodo que proporciona unicidad espacial. Un número aleatorio se sustituye si el último no está disponible (por ejemplo, debido a que la máquina no tenga tarjeta Ethernet, o no sabemos encontrar la dirección hardware de una interfaz en el sistema operativo). En este caso, la unicidad espacial no se puede garantizar. Sin embargo, una colisión debe tener una probabilidad muy baja.

Actualmente, la dirección MAC de una interfaz se tiene en cuenta sólo en FreeBSD y Linux. En otros sistemas operativos, MySQL usa un número generado aleatoriamente de 48 bits.

Código MySQL:

mysql> SELECT UUID();
        -> '6ccd780c-baba-1026-9564-0040f4311e29'
 

En definitiva, combinas ambas cosas (PHP y MySQL):
- Cuando loguea, tomas un UUID generado en MySQL.
- Lo almacenas como ID de sesión (PHP) y en la Visita (MySQL).
- En cada operación de navegación, validas que sea el mismo valor que el último ID de visita y lo insertas en la navegación (MySQL).
- Al cerrar la sesión (o salir) destruyes el objeto sesión (PHP), de modo que al volver a entrar generas otro.

¿Se entiende la idea?

Sobre la posibilidad de que una persona intente entrar con un user que tiene una sesión abierta, se deben realizar validaciones preventivas:
En tu sistema debes verificar si el usuario ha tenido un inicio de visita cuya última navegación haya sido anterior al máximo timeout que el sistema soporte. Si el timeout (tiempo en que no ha habido actividad por el usuario con sesión abierta) se venció, entonces puede loguear.
De ese modo puedes asegurarte que no hay sesiones "vivas" en curso, y avisarle al usuario entrante que aún no puede loguearse con ese usuario.
Son temas de prevención en sistemas asincrónicos como estos.

[roboty]

Hola gnzsoloyo, pero yo me refiero que en la tabla navegacion en el campo usuario_id seria la sesion que creo, donde esta sesion la saco de la tabla visitas del campo usuario_id que seria autoincrement, osea que cada vez que entra un usuario a la web creo en la tabla visitas un usuario_id que seria unico con este id creo una sesion que seria en la tabla navegacion sesion_id, entiedo que de esta forma si podria hacer lo que me comentas antes como saber cuantas veces entro un usuario el mismo dia etc.... e incluso me aseguro que la sesion seria unica ya que la sesion la saco de la tabla visitas que seria un PK, no se si te estoy liando espero que se entienda mejor ahora mi curiosidad, otra cosa seria que en mi sitio web no se tienen que loguear seria solo utilizar la sesion para seguir el rastra de navegacion, saludos.

[gnzsoloyo]

Funcionalmente se parecen, pero la ventaja del UUID es que al no depender de un AI, si es necesario restaurar los datos de un backup no generaría conflictos, como tampoco pasaría si decidieses administrar dos sistemas de visitas y luego necesitases consolidar los datos en una sola base.
Los AI son sencillos y comprensibles por los programadores. Los que nos dedicamos profesionalmente a las bases de datos tratamos de evitarlos porque a largo plazo suelen causar dolores de cabeza (por lo que te digo: migraciones, consolidaciones, integraciones de sistemas).
En cambio un ID que efectivamente sea único en todo el universo de bases de datos... será más largo, pero es más seguro.
De todos modos si estás iniciándote, cualquiera de las dos opciones sirve. Lo importante es que la sesión sea trazable, y que un mismo usuario no pueda entrar si hay una sesion viva del mismo.
Esto último es especialmente importante en sistemas de control visitas de sitios donde se manejan carritos de compras.

Fuera de eso, es una decisión de diseño (en la jerga: lo decide el que diseña el sistema).

[roboty]

Mucha gracias gnzsoloyo por compartir tu tiempo y tu saviduria, aunque este en proceso de aprendizaje seguire tus pasos y dejare los AI para lo que son y si generare otro campo unico para la session, un saludo.