| Author |
Topic |
|
DiceK-BadMan
Starting Member
12 Posts |
 Posted - 2007-11-14 : 01:12:53
|
| Well, its kind of tricky, and i got into this.For example i have a column of zip, all US city state name and abbreviation, preffered name and an alternate name of each city in US, and also non-acceptable name of each preffered name..Can you give me a hint, how many databse should i used?for example, i search for a zip code, and the result will be the name of city or an acceptable name of city and there are non-acceptable name too..well.i just need your opinion..hope you could get into this one.tnx |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-14 : 04:03:53
|
| You should use only one database :)May be, you meant, how many tables? |
 |
|
|
TimmyC
Starting Member
10 Posts |
Posted - 2007-11-14 : 10:41:00
|
| Apologies, but I'm totally confused. Say that again? Reread what you write before you post.Non acceptable name of each preffered name? I'm not American so maybe i'm missing something. Do you mean something like:State Abbreviation Alt Name Bad alt NameFlorida FLR Orange County Apple County(assuming "apple county" offends the people of Florida and hence is "bad") |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-14 : 10:54:39
|
| Maybe you mean something like this:zip_code state11111 FL22222 FL33333 TX44444 AZCities:zip_code city_name acceptable11111 Miami 111111 Mimi 033333 Houston 133333 Houstn 133333 Hstn 0 |
|
|
|
DiceK-BadMan
Starting Member
12 Posts |
Posted - 2007-11-14 : 14:51:26
|
| Oh.I mean how many tables should i used..?thank you for the correction.. well @TimmyC i mean this for exampleI search for a US zip codeSearch : 98052Result :Actual City name in 98052REDMOND, WANot AcceptableADELAIDE, WAAMES LAKE, WAAVONDALE, WAEARLMOUNT, WA..Just like this,if i have to used a one table,should i also assign a PK?.. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-14 : 15:16:58
|
| Would the layout I gave you not work? You could take the state out of the first table and include it in the 2nd if you like, it just wouldn't be as normalized. Then you could have it all in one table. The key to it all is the "acceptable" field being a 1 or 0. Display the 1's first and then a seperate section for the 0's. There may be a better way though... |
|
|
|
DiceK-BadMan
Starting Member
12 Posts |
Posted - 2007-11-14 : 15:25:03
|
| I will take your advice..if i have any further question, I'll just post my message here..thanks for the help |
|
|
|
DiceK-BadMan
Starting Member
12 Posts |
Posted - 2007-11-16 : 18:17:25
|
| Is all tables required to have a primary key?..In my city state table? |
|
|
|
TimmyC
Starting Member
10 Posts |
Posted - 2007-11-19 : 03:34:21
|
| No its not required, but in most cases I generally just add an ID column of type int and set its identity to true and auto-increment (as a primary key). It helps to have a key particularly when you are deleting or updating records later as you can be 100% sure you get the right one. If you are not intending to change the data in that table at any time then you can probably get away without one.For example, you add 'Folrida' to your states table. 2 weeks later you are looking for 'Florida' but can't find it, so you add it in. 2 weeks later you spot a spelling mistake with 'Folrida' and correct it to 'Florida'.Now you have 2 identical records - exactly the same. Which do you delete? Maybe this example was rubbish, but the point is things can get messy without a PK. |
|
|
|
|
I got into this..i just need your opinion