Mysql Schema design & Rollback necessity Question

------=_NextPart_000_00A3_01CAFB2E.82A577B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

Please advise which choice is better.=20

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=3D"update users set =
id=3D'$id',password=3D\"$password\",pin=3D\"$pin\",hint=3D\" $hint\",fname=
=3D\"$fname\",lname=3D\"$lname\",manager=3D\"$manager\",dept t=3D\"$deptt\=
" where username=3D\"$myuser\"";
if ($debug && $dbgusr == $ses_username) { echo("$query"); }
if (!($rs1 =3D $db->execute("$query")))
{
DisplayErrMsg(sprintf("Data Select Error: %d:%s\n", =
mysql_errno(), mysql_error()));
return 0;
}
else=20
{
// =
updatelog($id,"users","$query","usrmgr.php",$ses_username,$m yip);
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.=20

Regards,
Lightingales
------=_NextPart_000_00A3_01CAFB2E.82A577B0--

Re: Mysql Schema design & Rollback necessity Question

Hi,

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

I fail to see what kind of data a table ("one for each department") would
hold?

How would a single table ("one master table") hold the data for the
processes of this
application?

What should the application do?


>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 the storage engine.

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

Have you ever read a book on database design & normalization?

>PART II:
>Secondly, I am using PHP, Mysql, ADODB, APACHE on windows 7 platform. This
>is my typical DML command:
>
>
>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 ?

If the DML command failed and modifies a single row only, the "rollback"
won't do anything,
but if it updates multiple rows or trying to do a "unit of work" inside the
same transaction,
things become different.

Have you ever read about atomicy on database transactions?


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Mysql Schema design & Rollback necessity Question

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,$m yip);
> 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org