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