[mysql] aiuto su come organizzare un db

[the_nutria]

Ciao a tutti,
vi chiedo un consiglio sulla struttura di un database, se qualcuno ha qualche minuto da perdere gliene sarei grato


io ho una tabella che contiene circa 1600 righe (sono video di episodi di una sitcom televisiva), di questo tipo:

CREATE TABLE IF NOT EXISTS `episodi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stagione` int(11) NOT NULL,
  `numero` int(11) NOT NULL,
  `titolo` text,
  `trama` text,
  `daily` char(6) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `data_ora` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `visualizzazioni` int(11) NOT NULL,
  `attivo` tinyint(1) NOT NULL DEFAULT '1',
  `voti_up` int(11) NOT NULL,
  `voti_down` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stagione` (`stagione`,`numero`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1636 ;

e un'altra tabella 'classifica':

CREATE TABLE IF NOT EXISTS `classifica` (    
`epi_id` int(5) NOT NULL,    
`stag` int(2) NOT NULL,   
`punteggio` decimal(7,4) NOT NULL,    
PRIMARY KEY (`epi_id`),    
KEY `punteggio` (`punteggio`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

che ogni 30 minuti viene rigenerata (tutte le 1635 righe) calcolando per ogni episodio, un valore nel campo 'punteggio' calcolato con un algoritmo su dei valori che prende dalla tabella episodi (visualizzazioni, voti positivi e negativi, che considera anche data_ora e la stagione di appartenenza ma questo non importa...)


quindi ogni 30 minuti parte uno script che prima tronca la tabella, poi la ripopola calcolando per ogni episodio il suo punteggio...
poi c'è un altro script che viene eseguito ogni 15 minuti (sfalsato rispetto a questo) e crea il file statico della classifica, con un
select * from classifica order by punteggio
questo file statico poi viene letto dalla pagina che visualizza la lista, tipo questa: http://www.cameracoffee.com/stagione-5/


Ecco fino a qui è tutto funzionante, ma vorrei poter salvare la storia della classifica, in modo da poter sapere, per ogni episodio, che posizione occupava ad esempio 7 giorni fa e poter mostrare nelle info episodio del player, la sua variazione relativa, e il trend degli ultimi giorni/settimane... oppure mettere degli achievement del tipo "questo episodio è stato in top ten per 10 settimane" o cose del genere...

si tratterebbe quindi di salvare, per ogni generazione della classifica (quindi ogni 30 minuti, ma volendo posso anche aumentare l'intervallo a 1-2-4-6 ore...) 1635 coppie di valori (epi_id e posizione in classifica), più un'informazione sulla data/ora della classifica e un id progressivo... solo che anche il numero di episodi è dinamico, nuovi episodi possono venire aggiunti, quindi se facessi una tabella 'storia_classifica' con 1635 colonne (che comunque non mi pare molto efficiente da gestire, una tabella con 1600 colonne) andrebbe aggiunta una colonna ogni volta che si aggiunge un episodio...



qualcuno saprebbe, anche solo a livello teorico, se c'è un modo per fare una cosa del genere?

[Joe Taras]

Ciao,
direi che la tabella classifica potrebbe essere storicizzata in questo modo:

CREATE TABLE classifica
(id, data)

e creerei una nuova tabella "posizione" in questo modo
CREATE TABLE posizione
(id, id_classifica, id_episodio e poi tutte le proprietà inerenti la posizione)

Ovviamente prendi questa risposta come parziale in quanto non ho idea di cosa precisamente tu debba fare e come hai modellato il resto della base dati.