need advice on mysql normalization and php class structure

Question

i am trying to build a system that logs, media (photo, video, audio) upload statistic. so i come up with 3 tables, 1 for audio, 1 for video, and 1 for photo. and here's the structure

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| member_id | int(10) | NO   |     | NULL    |                |
| counter   | int(11) | NO   |     | NULL    |                |
| daydate   | text    | NO   |     | NULL    |                |
| epochtime | text    | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

all three tables has the same fields, since i think (so far) i need to differentiate the medias on each and particular table, is this redundant ??

anyway since each of the media is treated the same, so i think i should only build once class and kinda use the methods depending of what media i am accessing at the time. here's the class :

require_once(INC_PATH.DS.'database.php');

    class Log extends DatabaseObject {

        protected static $db_fields = array('id', 'member_id', 'counter',  'daydate', 'epochtime');

        public $id;
        public $member_id;
        public $counter;
        public $daydate;
        public $epochtime;

        public function find_counter($table_name){
            global $database;
            $time = date('d-m-Y');
            $timestamp = time();

            $sql  = "SELECT * FROM ". $table_name;
            $sql .= " WHERE daydate = '".$this->daydate."'";            
            $sql .= " AND member_id = '".$this->member_id."'";

            return self::find_by_sql($sql);
        }

        public function add_log($table_name){
            global $database;
            $tes = $this->find_counter();

            if(!empty($tes)){
                $sql  = "UPDATE ".$table_name;
                $sql .= " SET counter = counter+1";
                $sql .= " WHERE daydate = '".$this->daydate."'";
                $sql .= " AND member_id = '".$this->member_id."'";

                $database->query($sql);
                return ($database->affected_rows() == 1) ? true : false;
            }else{

                $sql  = "INSERT INTO ".$table_name;
                $sql .= " (id, member_id, user_privelege, counter, daydate, epochtime)";
                $sql .= " VALUES ('', '".$this->member_id."'";
                $sql .= " , '".$this->user_privelege."', '1', '".$this->daydate."', '".$this->epochtime;
                $sql .= "')";

                $database->query($sql);
                return ($database->affected_rows() == 1) ? true : false;
            }
        }

  }

so far this is pretty much working but, i still have doubts on the way i break the table in to 3 tables, and the way i use the $table_name as parameter. any suggestions for better approach ?? i really want to improve my codes, thank you very much

Regards

UPDATE

alright, based on everybody's answer, i would like to clear somethings up:

1. i only need to log the uploaded media
2. i already have another separate table to record other information (length, filename, titles, etc) for each media.

lets just say i add a field called "media_id" and merge the table into one, and since the way that i add and update my rows is based on the day-date, it will INSERT if the given daydate and the member_id is not found, and otherwise it will UPDATE, i think i should also change the way my add_log() method works, into something like:

public function add_log($table_name, $media_id){
    global $database;
    $tes = $this->find_counter();

    if(!empty($tes)){
        $sql  = "UPDATE ".$table_name;
        $sql .= " SET counter = counter+1";
        $sql .= " WHERE daydate = '".$this->daydate."'";
        $sql .= " AND member_id = '".$this->member_id."'";
        $sql .= " AND media_id = '".$media_id."'";

        $database->query($sql);
        return ($database->affected_rows() == 1) ? true : false;
    }else{

        $sql  = "INSERT INTO ".$table_name;
        $sql .= " (id, member_id, media_id, counter, daydate, epochtime)";
        $sql .= " VALUES ('', '".$this->member_id."'";
        $sql .= " , '".$media_id."', '1', '".$this->daydate."', '".$this->epochtime;
        $sql .= "')";

        $database->query($sql);
        return ($database->affected_rows() == 1) ? true : false;
    }
}

what do you guys think ??

thanks again

Answer 1

Put it all in one table and have a media_type column.

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| member_id | int(10) | NO   |     | NULL    |                |
| counter   | int(11) | NO   |     | NULL    |                |
| daydate   | text    | NO   |     | NULL    |                |
| epochtime | text    | NO   |     | NULL    |                |
| media_type| int     | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

Create a foreign key relationship to another table that defines media_type.

+-----------+---------+------+-----+---------+----------------+
| Field        | Type | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| media_type_id| int  | NO   | PRI | NULL    |                |
| description  | text | NO   |     | NULL    |                |
 +-----------+---------+------+-----+---------+----------------+

This would hold media_type_id (e.g. 1) and media_type_description (e.g. audio). e.g.

1, audio
2, video
3, photo

The main table would then just include on each row the id of 1, 2, 3 to denote which media type it is. You can then use this from your application to constrain via the WHERE clause, which media you're referring to. e.g. SELECT * FROM maintable WHERE media_type_id = 3 to just get the photos.

Answer 2

Make it one table with an extra column "media_type". Your design would require you to make an extra table for every new media type and that is possibly poor design.