Free Newsletters:
DatabaseJournal  
DBANews
Database Journal
Search Database Journal:
 
HOME News MS SQL Oracle DB2 Access MySQL PostgreSQL PHP SQL Etc Scripts Links Discussion Video
internet.com

» HOME
» NEWS
» VIDEO
» FEATURES
» SERIES
MS SQL
Oracle
MS Access
MySQL
DB2
» RESOURCES
Products
Scripts
Links
» DISCUSSION
» TECH JOBS

Marketplace Partners
Be a Marketplace Partner




internet.commerce
Be a Commerce Partner


















HP Snaps Up Network Storage Firm LeftHand

Media Giants Tap Slide to Show Facebook Videos

11th Hour Save for Internet Radio

internet.com
IT
Developer
Internet News
Small Business
Personal Technology
International

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers


DBA Support SQLCourse SQLCourse2 Swynk MSSQL


Related Articles
Database Normalization for the Average Jane/Joe
High Order Normalization: By Popular Demand

Software Developer
Vindigo, Inc.
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume
SQL Etc.
March 22, 2002
Database Normalization
By Ian Gilfillan


By now some of you are familiar with the basics of using databases in your cgi scripts. Many of your databases will be small, with one or two tables. But as you become braver, tackling bigger projects, you may start finding that the design of your tables is proving problematic. The SQL you write starts to become unwieldy, and data anomalies start to creep in. It is time to learn about database normalization, or the optimization of tables.

Let's begin by creating a sample set of data. Imagine we are working on a system to keep track of employees working on certain projects.

Project number Project name Employee number Employee name Rate category Hourly rate
1023 Madagascar travel site 11 Vincent Radebe A $60
    12 Pauline James B $50
    16 Charles Ramoraz C $40
1056 Online estate agency 11 Vincent Radebe A $60
    17 Monique Williams B $50

A problem with the above data should immediately be obvious. Tables in relational databases, which would include most databases you'll work with, are in a simple grid, or table format. Here, each project has a set of employees. So we couldn't even enter the data into this kind of table. And if we tried to use null fields to cater for the fields that have no value, then we cannot use the project number, or any other field, as a primary key (a primary key is a field, or list of fields, that uniquely identify one record). There is not much use in having a table if we can't uniquely identify each record in it.

So, our solution is to make sure that each field has no sets, or repeating groups.
Now we can place the data in a table.

employee_project table

Project number Project name Employee number Employee name Rate category Hourly rate
1023 Madagascar travel site 11 Vincent Radebe A $60
1023 Madagascar travel site 12 Pauline James B $50
1023 Madagascar travel site 16 Charles Ramoraz C $40
1056 Online estate agency 11 Vincent Radebe A $60
1056 Online estate agency 17 Monique Williams B $50

Notice that the project number cannot be a primary key on it's own. It does not uniquely identify a row of data. So, our primary key must be a combination of project number and employee number. Together these two fields uniquely identify one row of data. (Think about it. You would never add the same employee more than once to a project. If for some reason this could occur, you'd need to add something else to the key to make it unique).


Database Normalization: Part 2


 » See All Articles by Columnist Ian Gilfillan

Go to page: 1  2  3  4  5  6  Next  

Tools:
Add databasejournal.com to your favorites
Add databasejournal.com to your browser search box
IE 7 | Firefox 2.0 | Firefox 1.5.x
Receive news via our XML/RSS feed

SQL etc Archives







Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
Alias\Alternate name searching? khagan 2 September 25th, 11:43 AM
Stored Procedure and IF Condition ny3ranger 1 August 9th, 08:31 PM
Need help with IF FUnction in MYSQL reduday 1 August 5th, 03:14 AM
JOIN with multiple WHERE cjjubb 4 July 31st, 09:56 AM







JupiterOnlineMedia

internet.comearthweb.comDevx.commediabistro.comGraphics.com

Search:

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info


Legal Notices, Licensing, Reprints, & Permissions, Privacy Policy.

Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers

Solutions
Whitepapers and eBooks
Intel Article: Using Power & Display Context in the Intel Mobile Platform SDK
Internet.com eBook: Real Life Rails
IBM SCA Center Article: Simplifying Composite Applications with Service Component Architecture
Intel PDF: Quad-Core Impacts More Than the Data Center
Internet.com eBook: The Pros and Cons of Outsourcing
Go Parallel Article: Scalable Parallelism with Intel(R) Threading Building Blocks
Intel PDF: Analysis of Early Testing of Intel vPro in Large IT Departments
Internet.com eBook: Best Practices for Developing a Web Site
Intel PDF: IT Agility through Automated, Policy-based Virtual Infrastructure
IBM CIO Whitepaper: The New Information Agenda. Do You Have One?
Microsoft Article: BitLocker Brings Encryption to Windows Server 2008
Microsoft Article: RODCs Transform Branch Office Security
Go Parallel Article: James Reinders on the Intel Parallel Studio Beta Program
Avaya Article: Advancing the State of the Art in Customer Service
IBM Whitepaper: How are other CIOs driving growth?
Adobe Acrobat Connect Pro: Web Conferencing and eLearning Whitepapers
Avaya Article: Avaya AE Services Provide Rapid Telephony Integration with Facebook
Go Parallel Article: Getting Started with TBB on Windows
HP eBook: Storage Networking , Part 1
MORE WHITEPAPERS, EBOOKS, AND ARTICLES
Webcasts
Go Parallel Video: Intel(R) Threading Building Blocks: A New Method for Threading in C++
HP Video: Is Your Data Center Ready for a Real World Disaster?
HP On Demand Webcast: Virtualization in Action
Go Parallel Video: Performance and Threading Tools for Game Developers
Rackspace Hosting Center: Customer Videos
Intel vPro Developer Virtual Bootcamp
HP Disaster-Proof Solutions eSeminar
HP On Demand Webcast: Discover the Benefits of Virtualization
MORE WEBCASTS, PODCASTS, AND VIDEOS
Downloads and eKits
Actuate Download: Free Visual Report Development Tool
Red Gate Download: SQL Backup Pro
Microsoft Download: Silverlight 2 Software Development Kit Beta 2
30-Day Trial: SPAMfighter Exchange Module
Red Gate Download: SQL Toolbelt
IBM SCA Download: Start Building SCA Applications Today
Iron Speed Designer Application Generator
Microsoft Download: Silverlight 2 Beta 2 Runtime
MORE DOWNLOADS, EKITS, AND FREE TRIALS
Tutorials and Demos
IBM IT Innovation Article: Green Servers Provide a Competitive Advantage
Microsoft Article: Expression Web 2 for PHP Developers--Simplify Your PHP Applications
Featured Algorithm: Intel Threading Building Blocks - parallel_reduce
MORE TUTORIALS, DEMOS AND STEP-BY-STEP GUIDES