How do you select what fields to store in your database? To a large extent this is determined by what data you actually have to store but there may still be several options as to how you actually divide that information up.
Let's take a rather obvious example and see what the alternatives are. Let's take a look at how we might store an address.
Now an address could be considered to be one field to store in our database provided that we know that we will never need to access any of the parts of the address separately. This probably isn't the way we will want to store it though if there is any possibility that we might want to search for addresses in a particular location (or anything else which will require looking at just a part of the address).
Another alternative is to break the address up into a whole lot of different fields not all of which will necessarily contain values for every address. We could split the address into floor/unit number, street/po box number, street name, suburb/city, state/province, post/zip code, country. This is probably going too far the other way since unless we need to be able to access the unit or street number separately from the name of the street placing these parts of the address into separate fields just makes it more difficult to process since we will need to retrieve two or three fields every time that we want that part of the address.
Having the suburb/city, state/province, and post/zip code as separate fields probably will be useful since these are the parts of the address that we are likely to want to look at in splitting the addresses up into groups and being able to provide answers to queries such as what addresses can be found in a given suburb or post code.
While there is a relationship between these fields so that only a few (or perhaps even one) post code will be valid for a given suburb and state, or perhaps the other way around with only one city/province valid for a given zip code, there is probably no point in trying to separate out this particular relationship into a separate table in the database. The instances where this particular relationship changes will be extremely rare, the cases where such changes affect the actual data in your database will be even rarer, and the overhead of maintaining that relationship separately will far outweigh the benefits of having the relationship there. Also where we have a many to many relationship for something like this we would end up having to invent key fields just in order to be able to define the relationships without duplicating the entire content of the relationship in any case.
Considerations such as these help us to decide what fields we should break our data up into when saving them in our database and also what relationships to maintain between those fields. The same sorts of considerations can be looked at with any data that you have to go in your database when determining what fields you need to have and the relationships between those fields.
This article written by Stephen Chapman, Felgall Pty Ltd.