From: Martijn Tonies
Date: May 25 2010 6:57am
Subject: Re: Mysql Schema design & Rollback necessity Question
List-Archive: http://lists.mysql.com/mysql/221694
Message-Id: <006001cafbd7$80f7e2f0$1601a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

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