List:General Discussion« Previous MessageNext Message »
From:Lightingale Date:May 24 2010 2:47pm
Subject:Mysql Schema design & Rollback necessity Question
View as plain text  
Hi there,
I am new to using mysql. I want to prepare an application for my employer. The application
will be accessed by staff from as many as 10 different departments such as sales,
marketing, admin, finance etc. The users will be using DML commands on the tables. My
question has two parts:

Part I:
While designing the schema of the database, I have two choices:

Scenarios:
1. Create multiple tables, one for each department. The relationship for most of the
tables is one-to-one.
2. Create one master table so that each department updates its respective columns in the
same table. 

Please advise which choice is better. 

Questions:
1. With single table will table locking become an issue if multiple users edit the table
simultaneously or is it something that mysql can handle without problem?
2. What is the maximum recommended size of a table for mysql? How many columns should be
master table should have ? Is it recommended to design a master table having more than
200 columns?

PART II:
Secondly, I am using PHP, Mysql, ADODB, APACHE on windows 7 platform. This is my typical
DML command:

      $query="update users set
id='$id',password=\"$password\",pin=\"$pin\",hint=\"$hint\",fname=\"$fname\",lname=\"$lname\",manager=\"$manager\",deptt=\"$deptt\"
where username=\"$myuser\"";
      if ($debug && $dbgusr == $ses_username) { echo("$query"); }
      if (!($rs1 = $db->execute("$query")))
      {
         DisplayErrMsg(sprintf("Data Select Error: %d:%s\n", mysql_errno(),
mysql_error()));
         return 0;
      }
      else 
      {
//         updatelog($id,"users","$query","usrmgr.php",$ses_username,$myip);
         DispMsg("User Profile edited successfully");
      }

I am not using any rollback statement to rollback the db if the DML command is not
completed successfully. Is it advisable to use rollback? If it is how should I modify the
above statement to include it ?

Thanks in advance for your help. 

Regards,
Lightingales
Thread
Mysql Schema design & Rollback necessity QuestionLightingale24 May
  • Re: Mysql Schema design & Rollback necessity QuestionMartijn Tonies25 May
  • Re: Mysql Schema design & Rollback necessity QuestionShawn Green25 May