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
