Normalization and Indexing

trims30
trims30 used Ask the Experts™
on
I'm a beginner and need some guidance on setting up MySQL Tables.

Here are my issues:
1. I have two tables: EQUIPMENT and TRANSACTIONS

Equipment Table has fields:
EQUIP_ID                   {AutoIncrement Integer PrimaryIndex}
PropertyNumber       {Unique String Required}
Description                {String Required}
SerialNumber            {String Null OK}

Transaction Table has fields:
TRANS_ID                   {AutoIncrement Integer PrimaryIndex}
PropertyNumber       {String points to EQUIP PropertyNumber
Date
Description
Cost

The Transaction table is a Many to One relationship with the Equipment table.
I show relationship above by Property Number.  
Should I remove Property Number in Transaction File and replace it with EQUIP_ID?

Also, if relating by EQUIP_ID will I gain any benefit when querying transactions on a specific Equipment Item by having a unique Transaction Primary Index defined using three fields  (EQUIP_ID, Date, TRANS_ID) or should I just use TRANS_ID as primary index and query Transaction table for match on EQUIP_ID ordered by DATE?

2. I have a Table for Budget/Expense Tracking
It has Account_ID, Account Number, Account Description, and fields for Budget and Expense Values for each of 36 prior months.
Is it possible in MySql/VB.Net Connector to use indexing to reference these 36 fields such as Expense(1), Expense(2).... Budget(1), Budget(2)....
or should I create a separate table of Bud_ID,Account_ID, MonthNum, Budget, Expense linked back to Budget table by Account_ID?

Comments would be greatly appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
It's refreshing to see somebody really try to learn the basics =)

Should I remove Property Number in Transaction File and replace it with EQUIP_ID?

Yes I would do it this way.  Some would say for the Equipment table, since PropertyNumber is already unique, it should be the primary key and there's no more need for equip_id.  With PropertyNumber the unique identifier on the database is the unique identifier in the physical world.  While this is true, using an integer primary key enhances performances, since it is easier to compare numbers versus strings.

Also, if relating by EQUIP_ID will I gain any benefit when querying transactions on a specific Equipment Item by having a unique Transaction Primary Index defined using three fields  (EQUIP_ID, Date, TRANS_ID) or should I just use TRANS_ID as primary index and query Transaction table for match on EQUIP_ID ordered by DATE?

I would keep trans_id as primary key.  You would like to keep your primary key compact, so if one column is enough for a unique identifier, then stick to that one.  Then just create a concatenated index for equip_id and trans_id.  FYI, if you are using Innodb (read about storage engines at http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html), if you create such an index, it will also include the equip_id to it, since it is primary key (although you don't see it).

I have to step out first, will come back to address your other question.
Top Expert 2012

Commented:
2. I have a Table for Budget/Expense Tracking
It has Account_ID, Account Number, Account Description, and fields for Budget and Expense Values for each of 36 prior months.
Is it possible in MySql/VB.Net Connector to use indexing to reference these 36 fields such as Expense(1), Expense(2).... Budget(1), Budget(2)....
or should I create a separate table of Bud_ID,Account_ID, MonthNum, Budget, Expense linked back to Budget table by Account_ID?

If there's really only one set of data for each account I don't see anything wrong with having one column for each, except, if I follow your thinking correctly, it might be easier to code if you use the separate table.  In some programming languages it's easy to dynamically construct the names of the columns to refer to so you can name Expense_1, Expense_2, etc (though not Expense(1), etc).  I don't code on VB.Net so I don't know about that.  Ultimately I think either options you mentioned are okay, it's more of a matter of how you can code your application.

Author

Commented:
Thanks for your comments - am now starting to build three tables for testing before I go any further.  Am running into problem with error 1005  errno 150 when creating Budget_Master_File table.

I have three Tables

Departments
Budget Master File
Budget Transactions

May have foreign key problem but cant figure it out.

Many Transactions belong to one Budget Master File Record
Many Budget Master File records belong to one Department

Here's my code - can you see where the problem may be?
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `TRIMS_SQL_Data` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `TRIMS_SQL_Data` ;

-- -----------------------------------------------------
-- Table `TRIMS_SQL_Data`.`Budget_Transactions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TRIMS_SQL_Data`.`Budget_Transactions` (
`Trans_ID` INT UNSIGNED NULL AUTO_INCREMENT ,
`BudID` INT UNSIGNED NOT NULL ,
`Descr` CHAR(30) NOT NULL ,
`Reference` CHAR(10) NULL ,
`Invoice` CHAR(14) NULL ,
`Date` DATE NOT NULL ,
`Src` CHAR(1) NOT NULL ,
`OpenItem` TINYINT(1) NULL ,
`Amount` DECIMAL(19,4) NOT NULL ,
`Closed` CHAR(1) NULL ,
`Tsrc` CHAR(1) NULL ,
`Tsrn` INT UNSIGNED NULL ,
PRIMARY KEY (`Trans_ID`) ,
CONSTRAINT `fk_Budget_Master_File`
FOREIGN KEY (`BudID` )
REFERENCES `TRIMS_SQL_Data`.`Budget_Master_File` (`Bud_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TRIMS_SQL_Data`.`Budget_Master_File`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TRIMS_SQL_Data`.`Budget_Master_File` (
`Bud_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`Sequence` INT UNSIGNED NOT NULL ,
`DeptID` INT UNSIGNED NOT NULL ,
`AccountNo` CHAR(15) NOT NULL ,
`Descr` CHAR(30) NOT NULL ,
`Type` TINYINT(4) NOT NULL ,
`MstSub` CHAR(1) NULL ,
`EjGr` CHAR(1) NULL ,
`OpenOrders` DECIMAL(19,4) NULL ,
`Budget` DECIMAL(19,4) NULL ,
`BudgetLY` DECIMAL(19,4) NULL ,
`BudgetPY` DECIMAL(19,4) NULL ,
PRIMARY KEY (`Bud_ID`) ,
UNIQUE INDEX `AccountNo_UNIQUE` (`AccountNo` ASC),
CONSTRAINT `fk_Budget_Departments`
FOREIGN KEY (`DeptID` )
REFERENCES `TRIMS_SQL_Data`.`Budget_Departments` (`Dept_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TRIMS_SQL_Data`.`Budget_Departments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TRIMS_SQL_Data`.`Budget_Departments` (
`Dept_ID` INT NOT NULL ,
`Descr` CHAR(30) NOT NULL ,
PRIMARY KEY (`Dept_ID`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Open in new window

Top Expert 2012
Commented:
1.  You should create the the referenced table Budget_Departments before Budget_Master_File.
2.  The data types of the referencing and the referenced columns should be exactly the same.  When I removed "UNSIGNED" from the definition of DeptID in Budget_Master_File the table was created successfully.

Author

Commented:
Johann...
Thank you very much.  I am now able to enter data into tables and my next challenge is learning to use JOINs.

Regards,

Lee

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial