List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 25 2010 6:57am
Subject:Re: Mysql Schema design & Rollback necessity Question
View as plain text  

>I am new to using mysql. I want to prepare an application for my employer. 
>application will be accessed by staff from as many as 10 different 
>such as sales, marketing, admin, finance etc. The users will be using DML 
>on the tables. My question has two parts:
>Part I:
>While designing the schema of the database, I have two choices:
>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 

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

What should the application do?

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

>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

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

Database questions? Check the forum: 

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