need advice on mysql normalization and php class structure

12

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

+

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 :

{-code-2}

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:

{-code-3}

what do you guys think ??

thanks again

213

Answer

-+---------+------+-----+---------+
458

Answer

------+ | Field | Type | Null | Key | Default | Extra | +
148

Answer

-+---------+------+-----+---------+
8

Answer

------+ | 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 | | +
990

Answer

-+---------+------+-----+---------+
960

Answer

------+|||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; } } }|||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; } }
159

Answer

Solution:

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

+

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

{-code-2}

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

{-code-3}

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.

815

Answer

-+---------+------+-----+---------+
111

Answer

------+ | Field | Type | Null | Key | Default | Extra | +
49

Answer

-+---------+------+-----+---------+
956

Answer

------+ | 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 | | +
729

Answer

-+---------+------+-----+---------+
174

Answer

------+|||+
421

Answer

-+---------+------+-----+---------+
881

Answer

------+ | Field | Type | Null | Key | Default | Extra | +
269

Answer

-+---------+------+-----+---------+
216

Answer

------+ | media_type_id| int | NO | PRI | NULL | | | description | text | NO | | NULL | | +
842

Answer

-+---------+------+-----+---------+
560

Answer

------+|||1, audio 2, video 3, photo
684

Answer

Solution:

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.

People are also looking for solutions to the problem: php - Such a strange error while uploading a image file

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.