Categories and Sub-Categories, Solution to cleaning "string" and "text" fields

by Albert Anthony D. Gavino

Region or Geographic Location Categories and Sub-Categories

Database Administrators have problems cleaning "strings" and "texts", to prevent this from happening you could always resort to putting categories and sub-categories, for example think of a solution for ZIP-CODES, it can be broken further into COUNTRY, REGION, CITY.

In my case I live in the Philippines, so its good to have LUZVISMIN which are 3 categories namely Luzon, Visayas and Mindanao and then Region has 17 regions including NCR, and lastly the difficult part would be CITY such as Metro Manila, Cavite and Quezon City for example. This gives you a clear idea that your variable ZIP code needs to be connected to at least three other fields, so when creating your ZIP code table, include these 3 additional variables to make life easier, hence no need to clean your "STRINGS" or "TEXT FILES" for other variables that you can't categorize, make sure you have a category such as "OTHERS", rule of thumb of what I do is limit your "OTHERS" category to at least 10 percent. 

for Geographic Region
here, your category is structured as: 
Category Design: COUNTRY > LUZVISMIN > Region > City

ex. Philippines > Luzon > NCR > Manila

Geographic Tip: 
Add Country: ABROAD, to make way for Foreign Students/ Applicants

University or College Categories or Sub-Categories

the same thing can be used for a School or University say for example:
Category Design: University > College > Degree Program 

Ex. De La Salle University > College of Engineering > B.S. in Mechanical Engineering

Personal Tip:

Don't use text boxes in your web app forms, if you want your Database to be clean, instead USE Drop Down Boxes and Radio buttons, this also follows for Survey Forms. As much as possible do not use text fields, Use them only for qualitative purposes such as Comments and Feedback, but do not use them for categories as this will be a big problem to your database cleaning.

Philippine Regions

Luzon

1. NCR
2. CAR
3. Ilocos
4. Cagayan Valley
5. Central Luzon
6. CALABARZON
7. MIMAROPA
8. Bicol

Visayas

1. Western
2. Central
3. Eastern

Mindanao 

1. Zamboanga
2. Northern Mindanao
3. Davao
4. SOCCSKSARGEN
5. Caraga
6. ARMM