phpOpenTracker/phpOpenTracker/docs/sql/mysql innodb.sql,1.2,1.3 myisam.sql,1.4,1.5 myisam_merge.sql,1.3,1.4

Update of /cvsroot/phpopencounter/phpOpenTracker/phpOpenTracker/docs/sql/mysql
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv16214/phpOpenTracker/docs/sql/mysql

Modified Files:
	innodb.sql myisam.sql myisam_merge.sql 
Log Message:
De-normalize database schema.

Index: myisam.sql
===================================================================
RCS file: /cvsroot/phpopencounter/phpOpenTracker/phpOpenTracker/docs/sql/mysql/myisam.sql,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -d -r1.4 -r1.5
--- myisam.sql	8 May 2004 13:04:30 -0000	1.4
+++ myisam.sql	27 May 2004 05:53:53 -0000	1.5
@@ -18,94 +18,46 @@
 # $Id$
 #
 
-DROP TABLE IF EXISTS pot_accesslog;
-CREATE TABLE pot_accesslog (
-  accesslog_id      INT(11)                NOT NULL,
-  timestamp         INT(10)    UNSIGNED    NOT NULL,
-  weekday           TINYINT(1) UNSIGNED    NOT NULL,
-  `hour`            TINYINT(2) UNSIGNED    NOT NULL,
-  document_id       INT(11)                NOT NULL,
-  exit_target_id    INT(11)    DEFAULT '0' NOT NULL,
-  entry_document    TINYINT(1) UNSIGNED    NOT NULL,
+DROP TABLE IF EXISTS page_impressions;
+CREATE TABLE page_impressions (
+  visit_id       INT(11)               NOT NULL,
+  timestamp      INT(10)      UNSIGNED NOT NULL,
+  weekday        TINYINT(1)   UNSIGNED NOT NULL,
+  `hour`         TINYINT(2)   UNSIGNED NOT NULL,
+  document       VARCHAR(255)          NOT NULL,
+  document_url   VARCHAR(255)          NOT NULL,
+  exit_target    VARCHAR(255)          NOT NULL,
+  entry_document TINYINT(1)   UNSIGNED NOT NULL,
 
-  KEY accesslog_id   (accesslog_id),
-  KEY timestamp      (timestamp),
-  KEY document_id    (document_id),
-  KEY exit_target_id (exit_target_id)
+  KEY visit_id    (visit_id),
+  KEY timestamp   (timestamp),
+  KEY document    (document),
+  KEY exit_target (exit_target)
 ) ENGINE=MyISAM DELAY_KEY_WRITE=1 PACK_KEYS=1;
 
-DROP TABLE IF EXISTS pot_add_data;
-CREATE TABLE pot_add_data (
-  accesslog_id INT(11)      NOT NULL,
-  data_field   VARCHAR(32)  NOT NULL,
-  data_value   VARCHAR(255) NOT NULL,
-
-  KEY accesslog_id (accesslog_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_documents;
-CREATE TABLE pot_documents (
-  data_id      INT(11)      NOT NULL,
-  string       VARCHAR(255) NOT NULL,
-  document_url VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_exit_targets;
-CREATE TABLE pot_exit_targets (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_hostnames;
-CREATE TABLE pot_hostnames (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_operating_systems;
-CREATE TABLE pot_operating_systems (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_referers;
-CREATE TABLE pot_referers (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_user_agents;
-CREATE TABLE pot_user_agents (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
+DROP TABLE IF EXISTS pot_visits;
+CREATE TABLE pot_visits (
+  visit_id          INT(11)               NOT NULL,
+  visitor_id        INT(11)               NOT NULL,
+  client_id         INT(10)      UNSIGNED NOT NULL,
+  timestamp         INT(10)      UNSIGNED NOT NULL,
+  weekday           TINYINT(1)   UNSIGNED NOT NULL,
+  `hour`            TINYINT(2)   UNSIGNED NOT NULL,
+  returning_visitor TINYINT(1)   UNSIGNED NOT NULL,
+  host              VARCHAR(255)          NOT NULL,
+  operating_system  VARCHAR(255)          NOT NULL,
+  user_agent        VARCHAR(255)          NOT NULL,
+  referer           VARCHAR(255)          NOT NULL,
 
-  PRIMARY KEY (data_id)
+  PRIMARY KEY         (visit_id),
+  KEY client_time     (client_id, timestamp)
 ) ENGINE=MyISAM DELAY_KEY_WRITE=1;
 
-DROP TABLE IF EXISTS pot_visitors;
-CREATE TABLE pot_visitors (
-  accesslog_id        INT(11)             NOT NULL,
-  visitor_id          INT(11)             NOT NULL,
-  client_id           INT(10)    UNSIGNED NOT NULL,
-  operating_system_id INT(11)             NOT NULL,
-  user_agent_id       INT(11)             NOT NULL,
-  host_id             INT(11)             NOT NULL,
-  referer_id          INT(11)             NOT NULL,
-  timestamp           INT(10)    UNSIGNED NOT NULL,
-  weekday             TINYINT(1) UNSIGNED NOT NULL,
-  `hour`              TINYINT(2) UNSIGNED NOT NULL,
-  returning_visitor   TINYINT(1) UNSIGNED NOT NULL,
+DROP TABLE IF EXISTS pot_additional_data;
+CREATE TABLE pot_additional_data (
+  visit_id   INT(11)      NOT NULL,
+  data_field VARCHAR(32)  NOT NULL,
+  data_value VARCHAR(255) NOT NULL,
 
-  PRIMARY KEY         (accesslog_id),
-  KEY client_time     (client_id, timestamp)
+  PRIMARY KEY (visit_id, data_field)
 ) ENGINE=MyISAM DELAY_KEY_WRITE=1;

Index: innodb.sql
===================================================================
RCS file: /cvsroot/phpopencounter/phpOpenTracker/phpOpenTracker/docs/sql/mysql/innodb.sql,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -d -r1.2 -r1.3
--- innodb.sql	8 May 2004 07:59:57 -0000	1.2
+++ innodb.sql	27 May 2004 05:53:53 -0000	1.3
@@ -18,94 +18,46 @@
 # $Id$
 #
 
-DROP TABLE IF EXISTS pot_accesslog;
-CREATE TABLE pot_accesslog (
-  accesslog_id      INT(11)                NOT NULL,
-  timestamp         INT(10)    UNSIGNED    NOT NULL,
-  weekday           TINYINT(1) UNSIGNED    NOT NULL,
-  `hour`            TINYINT(2) UNSIGNED    NOT NULL,
-  document_id       INT(11)                NOT NULL,
-  exit_target_id    INT(11)    DEFAULT '0' NOT NULL,
-  entry_document    TINYINT(1) UNSIGNED    NOT NULL,
-
-  KEY accesslog_id   (accesslog_id),
-  KEY timestamp      (timestamp),
-  KEY document_id    (document_id),
-  KEY exit_target_id (exit_target_id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS pot_add_data;
-CREATE TABLE pot_add_data (
-  accesslog_id INT(11)      NOT NULL,
-  data_field   VARCHAR(32)  NOT NULL,
-  data_value   VARCHAR(255) NOT NULL,
-
-  KEY accesslog_id (accesslog_id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS pot_documents;
-CREATE TABLE pot_documents (
-  data_id      INT(11)      NOT NULL,
-  string       VARCHAR(255) NOT NULL,
-  document_url VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS pot_exit_targets;
-CREATE TABLE pot_exit_targets (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS pot_hostnames;
-CREATE TABLE pot_hostnames (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS pot_operating_systems;
-CREATE TABLE pot_operating_systems (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS pot_referers;
-CREATE TABLE pot_referers (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
+DROP TABLE IF EXISTS page_impressions;
+CREATE TABLE page_impressions (
+  visit_id       INT(11)               NOT NULL,
+  timestamp      INT(10)      UNSIGNED NOT NULL,
+  weekday        TINYINT(1)   UNSIGNED NOT NULL,
+  `hour`         TINYINT(2)   UNSIGNED NOT NULL,
+  document       VARCHAR(255)          NOT NULL,
+  document_url   VARCHAR(255)          NOT NULL,
+  exit_target    VARCHAR(255)          NOT NULL,
+  entry_document TINYINT(1)   UNSIGNED NOT NULL,
 
-  PRIMARY KEY (data_id)
+  KEY visit_id    (visit_id),
+  KEY timestamp   (timestamp),
+  KEY document    (document),
+  KEY exit_target (exit_target)
 ) ENGINE=InnoDB;
 
-DROP TABLE IF EXISTS pot_user_agents;
-CREATE TABLE pot_user_agents (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
+DROP TABLE IF EXISTS pot_visits;
+CREATE TABLE pot_visits (
+  visit_id          INT(11)               NOT NULL,
+  visitor_id        INT(11)               NOT NULL,
+  client_id         INT(10)      UNSIGNED NOT NULL,
+  timestamp         INT(10)      UNSIGNED NOT NULL,
+  weekday           TINYINT(1)   UNSIGNED NOT NULL,
+  `hour`            TINYINT(2)   UNSIGNED NOT NULL,
+  returning_visitor TINYINT(1)   UNSIGNED NOT NULL,
+  host              VARCHAR(255)          NOT NULL,
+  operating_system  VARCHAR(255)          NOT NULL,
+  user_agent        VARCHAR(255)          NOT NULL,
+  referer           VARCHAR(255)          NOT NULL,
 
-  PRIMARY KEY (data_id)
+  PRIMARY KEY         (visit_id),
+  KEY client_time     (client_id, timestamp)
 ) ENGINE=InnoDB;
 
-DROP TABLE IF EXISTS pot_visitors;
-CREATE TABLE pot_visitors (
-  accesslog_id        INT(11)             NOT NULL,
-  visitor_id          INT(11)             NOT NULL,
-  client_id           INT(10)    UNSIGNED NOT NULL,
-  operating_system_id INT(11)             NOT NULL,
-  user_agent_id       INT(11)             NOT NULL,
-  host_id             INT(11)             NOT NULL,
-  referer_id          INT(11)             NOT NULL,
-  timestamp           INT(10)    UNSIGNED NOT NULL,
-  weekday             TINYINT(1) UNSIGNED NOT NULL,
-  `hour`              TINYINT(2) UNSIGNED NOT NULL,
-  returning_visitor   TINYINT(1) UNSIGNED NOT NULL,
+DROP TABLE IF EXISTS pot_additional_data;
+CREATE TABLE pot_additional_data (
+  visit_id   INT(11)      NOT NULL,
+  data_field VARCHAR(32)  NOT NULL,
+  data_value VARCHAR(255) NOT NULL,
 
-  PRIMARY KEY         (accesslog_id),
-  KEY client_time     (client_id, timestamp)
+  PRIMARY KEY (visit_id, data_field)
 ) ENGINE=InnoDB;

Index: myisam_merge.sql
===================================================================
RCS file: /cvsroot/phpopencounter/phpOpenTracker/phpOpenTracker/docs/sql/mysql/myisam_merge.sql,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- myisam_merge.sql	8 May 2004 13:04:31 -0000	1.3
+++ myisam_merge.sql	27 May 2004 05:53:53 -0000	1.4
@@ -18,63 +18,14 @@
 # $Id$
 #
 
-DROP TABLE IF EXISTS pot_accesslog;
+DROP TABLE IF EXISTS page_impressions;
 DROP TABLE IF EXISTS pot_visitors;
 
-DROP TABLE IF EXISTS pot_add_data;
-CREATE TABLE pot_add_data (
-  accesslog_id INT(11)      NOT NULL,
-  data_field   VARCHAR(32)  NOT NULL,
-  data_value   VARCHAR(255) NOT NULL,
-
-  KEY accesslog_id (accesslog_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_documents;
-CREATE TABLE pot_documents (
-  data_id      INT(11)      NOT NULL,
-  string       VARCHAR(255) NOT NULL,
-  document_url VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_exit_targets;
-CREATE TABLE pot_exit_targets (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_hostnames;
-CREATE TABLE pot_hostnames (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_operating_systems;
-CREATE TABLE pot_operating_systems (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_referers;
-CREATE TABLE pot_referers (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
-
-  PRIMARY KEY (data_id)
-) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-
-DROP TABLE IF EXISTS pot_user_agents;
-CREATE TABLE pot_user_agents (
-  data_id INT(11)      NOT NULL,
-  string  VARCHAR(255) NOT NULL,
+DROP TABLE IF EXISTS pot_additional_data;
+CREATE TABLE pot_additional_data (
+  visit_id   INT(11)      NOT NULL,
+  data_field VARCHAR(32)  NOT NULL,
+  data_value VARCHAR(255) NOT NULL,
 
-  PRIMARY KEY (data_id)
+  PRIMARY KEY (visit_id, data_field)
 ) ENGINE=MyISAM DELAY_KEY_WRITE=1;



-------------------------------------------------------
This SF.Net email is sponsored by: Oracle 10g
Get certified on the hottest thing ever to hit the market... Oracle 10g. 
Take an Oracle 10g class now, and we'll give you the exam FREE.
http://ads.osdn.com/?ad_id=3149&alloc_id=8166&op=click