List:MySQL on Win32« Previous MessageNext Message »
From:Peter Carter Date:September 3 1999 12:35am
Subject:Re: general database question
View as plain text  
A bit off topic, but what the heck.....

I follow 4 simple rules to normalize.........

1) Always use an unrelated number as the primary key, ie: 'id' as autoinc
type. This makes a very convenient foreign key, does not confuses the
layout and makes the table adaptable to change.

2) The columns must contain collectively unique information. Redundant
information should be referred to another table, using the 'id' of the new
row. A prime example of this would be an 'address', as more than one person
could live there.

3) Tables should only expand down, as opposed to lots of columns with nulls
in them. Make these refer to a table. An example of this would be a table
of belongings for an individual. Each individual would have a table1 entry
for their name. Table 2 would have an entry if the id (or key) of the
owner, and an entry for the item.

	table1: id(auto) :  name(char)
	         1          'fred'
	         2          'lisa'

	table2: id(auto) : owner(int) : item(char)
	         1         1            'car'
	         2         1            'boat'
                3         1            'cat'
	         4         2            'cat'
                5         2            'house'

table2 still follows rule2, even though there are 2 cats. They have
different owners.

4) Don't rely on SQL Servers to keep users from deleting forien-key
relationships and do not rely on locks to reserve the data. You can't beat
good logic. I only use restraints when opening the table up to access and
the like, where users do not have a controlled data-entry. Keeping the
logic on the server is a waste of resources. 

So what? Ok, for small tables no big deal. When you get to 20,000 records
of data, you will change your mind. With the example in rule3, we have to
do 2 selects to get our data (or 1 combined). For small tables, this is a
waste of cpu. On large tables, it is a quick b-line to your data - simple
selects = quick results. It is also scaleable. As your split-tables grow,
it becomes easier to add and move data.

Long and short: pain-now = vacation
                vacation = redoing project for pain-now anyway
                           and making sure you do not loose any data


At 08:19 AM 9/2/99 -0700, you wrote:
>Hi...
>
>
>
>>What you're describing (dividing tables in a relational database into
>>smaller tables) is part of a the process called "Normalizing" the
>>database. There are well-defined rules for when you should divide a
>>table.
>
>I'm relatively new to relational databases as well. I have already searched
>the 'net for tutorials re: normalization and found them very helpful.
>However, nowhere did I see a step-by-step "how-to" on how to achieve
>normalization. They all give examples of tables that have been split to
>eliminate redundancies, but they don't show how to do it -- i.e. the logic
>used to decide whether or not a field should be jerked out of a table and
>included in a new table. To your knowledge, is there a "Normalization
>Wizard" out there somewhere, or a spreadsheet template or something that a
>person could use to see the logic involved.
>
>i ask because the site that I'm developing (I'm using php3/mysql) has come
>to somewhat of a grinding halt -- not because of not being able to use
>mysql, but because I'm stuck on setting up the tables correctly. TIA
>
>later.....duke
>
>
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail win32-thread466@stripped
>
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail win32-unsubscribe@stripped instead.
>
>
---
Peter B. Carter (peterc@stripped)
Pager: 613-751-4660
http://www.pbc.ottawa.on.ca

Thread
general database questionGagan Palrecha29 Aug
Re: general database questionJohn Millaway30 Aug
  • Re: general database question(Hood Gardner)30 Aug
Re: general database questionDuke Normandin2 Sep
  • Re: general database questionPeter Carter3 Sep
RE: general database questionJohn Millaway2 Sep
Re: general database questionDuke Normandin5 Sep
Re: general database questionDuke Normandin5 Sep
re: Re: Re: "Unresolved external call"karnatic28 Mar