Russell, > I am contemplating a Historical Database > I barely can spel redundant let alone normalize so I am handicapped. > The aim is to enter a name only once. I am studying linking tables. > In the [Contact] > idContact > First_Name,Last_Name,Middle,Suffix,Full_Name,idCity,idState,City_State,i > dZip_Code, > Address_1,Address_2, > > [City] > id > Cityname > > id (al, ar etc) > Statename > > [ZipCode] > id > Zip_Code > > Comment please,
I note two previous responses and agree with the basic premise that if you are not going to use the three supporting tables for some information-processing purpose they are overkill, ie if the city, state, and zip data is purely for information-labeling forget it. Similarly if your motivation is some sort of space-saving effort, eg idCity=123 takes less space than "Minneapolis", and multiplied by dozens/hundreds of instances seems like a good plan; but what is the real cost of disk storage these days? However, if this motivation is based on "spelling" - or to be more constructive, consistency of spelling a given word (eg I have to think about some of the longer, repeating-character names like Minneapolis - with apologies to St Paul and all the other good residents of said fair city) then I will disagree with another correspondent and suggest that using the City table to construct the combo/list box in a data-entry form would do wonders for data-entry consistency and spell-checking - although at the expense of response time if the tables grow to be huge. Similarly State data-entry will definitely benefit because of the typing time saved by entering MS instead of M-I-S-S (oops, there's that spelling and character repetititititition issue again). The Zip code is unlikely to save space, and depending upon how 'historical' your data/enquiry, of only limited/recent use. The comment that replaying a five digit zip code with another integer is valid, however it is also parochial in that many non-US countries use postal/location codes that are not exclusively numeric. If 'history' spans more than 200 years the observation is that there will be no zip code to enter and the data will likely require an additional field: Country! At which point it is worth mentioning that "State" is a parochial term too - many countries don't use them/know their regional subdivisions by that term. I don't think anyone has mentioned City_State. What is the purpose of this field given that there are already two separate fields? You have gone to quite a bit of detail with the physical address and (normalising) breaking out repeating components into separate sub-tables, however what about the concept of a person moving about? Should there be allowance for the fact that an address was only good on a particular date or for a given period of history? Perhaps this is the purpose behind Address_1 and Address_2 (except there is no temporal component)? Why would you have one person and three addresses - and why in a single db row? Shouldn't there be three address rows (or however many are necessary) for each person - how would you otherwise construct a search based upon place, or place and time? There is a Suffix but not a Prefix, eg "Sir" or "Captain". There is space for formal names, but not for nicknames, eg a lot of people are known by their initials, or some familial address, eg "Doc" (never play cards with him) - my buddy calls me "Buckwheat" for some reason beyond my understanding but presumably because he thinks I am a little... Hope this is a little...helpful, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
