From: Shawn Green
Date: May 25 2010 11:56am
Subject: Re: Mysql Schema design & Rollback necessity Question
List-Archive: http://lists.mysql.com/mysql/221696
Message-Id: <4BFBBAF6.7070909@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello Lightingale,

Lightingale wrote:
> 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:
> 

First problem: you are letting your users run direct commands against 
the database.

One of the biggest roles in an application is to isolate and protect the 
data from stupid user mistakes.  Not only should your application 
filter, validate, and sanitize their input but you also need to 
encapsulate (with your application code) all of the functions they need 
to perform against the data. That way, if there is a problem with how 
things are going you will know exactly where to look.

If it is a requirement that the users change data directly, then why 
write an application in the first place?


> 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. 
> 

You actually have more choices than that. You could create multiple 
databases, each with a full compliment of application tables.

#2 may be a bad option - it's fine to have columns that only certain 
users can update but if you propose to have several sets of columns 
copies where each set belongs to a single group, that would be horrible.

Work up from a rational database design and build an application to 
support it. Try very hard to not design a database that works with your 
code. Databases operate most efficiently when you use "set theory" and 
not "iterative application design principles" to access your data. What 
that means, specifically, is avoid writing code that does dozens or 
hundreds of small single-row manipulations when one statement could be 
written to process the entire batch of data.  Of course, there are rare 
exceptional cases to consider but at this stage, I don't think you are 
there yet.


> 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?

It depends on how you use the table, how it is organized, and which 
storage engine you choose.

> 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?
> 

For me, the design any table with more than about 20 or so columns is 
suspicious. Please do some homework and learn more about relational data 
modeling and the principles of "normalization"

We, the other members on the list,  will be happy to answer any specific 
questions you may have.

> 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. 
> 

As mentioned in the other reply, ROLLBACK only applies to active 
transactions. Please do some additional homework and figure out which 
storage engines support transactions and how you start and end a 
multiple-statement transaction.

-- 
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN