I am writing this tutorial into database design to help assist anyone who needs a crash course into good database design.   This is just a intro, there is far more to database design then what is going to be described in this tutorial

So lets say you wanted to create a database to list every city in the US.   Where do you start.   Simple, you list what you need to store, its fairly simple:

CityName	CountyName	StateName

Now, lets put data into our table (note this data is totally made up)

CityName	CountyName	StateName	StateAddv	Population

City1		County1		StateA		SA		1000
City2		County1		StateA		SA		2000
City3		County2		StateA		SA		3000
City4		County2		StateA		SA		4000
City5		County3		StateB		SB		5000
City6		County3		StateB		SB		6000
City7		County4		StateB		SB		7000
City8		County4		StateB		SB		8000
City9		County5		StateC		SC		9000
City10		County5		StateC		SC		10000
City11		County6		StateC		SC		11000
City12		County6		StateC		SC		12000

In this table, we have 12 cities, in 6 unique counties, in 3 states.   Its perfect, each city is can be uniquely identified, no issues... right?

Well no, What is a state name is entered incorrectly, or misspelled, you created a entire state!   Plus your saving a state name for each city in that state, so we need to reduce redundancy.  Lets try it

OK, first we find our entities.   We have three.   State, City, and County.  Each one of these is a unique thing, with their own attributes.  A attribute could be something such as Names, StateAddv, and population.

State:
StateName, StateAddv

County:
CountyName

City:
CityName, Population

Next thing we must to, is be able to uniquely identify each entity.  For a state, Both StateName, and StateAddv can be used to uniquely identify, but StateNames can be different lengths, and long.   But StateAddv will ALWAYS be 2 charters longs, so it would make a much better option.

For the County, the name of the county can not be used to uniquely identify a county, since it would be possible that two counties in the entire US could be the same, plus you run into issues with StateName such as length and spelling.  So we need to add the key, we will call it 'CountyID'

The city will be similar to the county, it would be possible for two cities to have same name, or population, so we will add a unique key.

State:
(StateAddv), StateName

County:
(CountyID), CountyName

City:
(CityID), CityName, Population

Now we have to setup our relation ships, lucky in this situation its pretty easy.   There are three types of relation ships, one to one, many to one, and many to many.  How do we see these relationships?

There are many counties in one state (County-State is a Many to One)
There are many cities in one county (City-County is a Many to One)
There are many cities in one state (City-State is a Many to one)

Now there is a special situation, County is dependent on State, and City is dependent on County.  So are diagram looks somewhat like this:

(City)M - 1(County)M - 1(State)

This would be a effective way to setup out database, so how do it look?



StateAddv	StateName

SA		StateA
SB		StateB
SC		StateC

CountyID	CountyName	StateAddv

1		County1		SA
2		County2		SA
3		County3		SB
4		County4		SB
5		County5		SC
6		County6		SC

CityID		CountyID	CityName	Population

1		1		City1		1000
2		1		City2		2000
3		2		City3		3000
4		2		City4		4000
5		3		City5		5000
6		3		City6		6000
7		4		City7		7000
8		4		City8		8000
9		5		City9		9000
10		5		City10		10000
11		6		City11		11000
12		6		City12		12000

And there we have it, a small intro into the world of database design