Database Basics

This isn't all inclusive and it's a work in progress, so please bare with me.
Envision your model.


When you start out on creating a database the place to start is with a vision. Lots of people do this without even thinking about it. They already know how they want their forms to look and work and by doing this they are basically envisioning the model. They see a 'Customer' form with a sub form of 'Contacts.' They remember how one application had a certain feature and they want that same functionality. This is basically envisioning.


Some people suggest filling out paper forms and I don't disagree with this but the core need when starting out is to understand the model. That's what the forms are for but for some people, they can see the objects themselves and start building tables. I think the model is the most important foundation of the application. What does your data LOOK like? That's right, what does it look like? Every good application keeps track of 'items' of some sort. Be they 'subjects' or 'actions' or whatever, they record something. These 'somethings' have a basic structure. A 'Customer' has a 'Name', 'Address', 'email', and etcetera. The items may have sub items. 'Orders' may have 'Line Items' 'Contacts' may have 'Calls' or 'Meetings' or 'Engagements' 'Rooms' may have 'Reservations' etcetera. Envisioning these items and how they interact is the basic starting point of a database application.


When I started, I said people do this already. They do it in their mind when they think of the forms and how they work. By understanding the functionality they want they are really building the model. Forms that contain data can represent items and sub items in your database. Drawing forms out on paper can help you identify the topics/items you need to create. Something I do from time to time is create a blank form with no data bound to it and just add controls to make it look like I want. This helps me 'envision' my object. Usually, for me, I can see it in my minds eye but you may want to schetch it out.


The reason I'm talking about forms in the conversation of the database model is because forms are how most people already 'see' data. I think that over the years, especially in the 'internet age', people have become acustom to 'seeing' data via forms. I don't have statistics to prove this but my experience is such and my contact with others in the community seem to bear testimony to the same. This is not a hard and fast rule but if it helps someone, all the better.


The model is important because a good or bad model will eventually make or break the whole application. When building a database, the better the foundation, the much better the application. If the foundation is bad, no matter how pretty the building, it will become shakier and shakier the taller it gets. The further you get down the road, the harder it will be to impliment the desired results. Knowing the end will be the best place to start.

Once you've envisioned the forms and even reports you want to produce, you can step into other questions. Some of these are:
- How do I want to sort my data?
- How would I like to group my data?
- How do I want to filter/find my data?
These questions are important because they help you identify field breakdown and indexing. Fields need to be in the smallest unit needed to sort, group and find. The postal system breaks addresses down into number and street where most people find they don't need this accuracy. Understanding how much accuracy you need identifies what fields you need. For small applications the need is not that great because you can break fields apart manually when querying but for larger ones the need to have information broken up correctly is paramount.

Indexing the fields that will be sorted, grouped and filtered the most will aid in the speed and performance of your application. No one likes waiting around for a form to produce the results they requested. Picking out the items now help solve these issues. Envision how your form sorts it's information. Envision how you group items. What are the major factors of an item? Let's take an example. Imagine you are creating a database to store survey information. You've identified how you want to present this data but what are some of the important sorting, grouping and indexing issues you may have with this data? How is survey data important? When it was collected is of importance. What the results were are important. Demographics i.e. where the data came from is important. So we may start may ensuring collection dates, results, and demographic information are index and uniquely identifiable. What I mean by 'uniquely identifyable' is it has it's own place and type. These fields need to be separated and set aside for these purposes. Say my survey collects numeric results for a person's satisfaction, 'on a scale of 1 to 10 how would you rate...?' I don't want to place any other data in that field other than 1 to 10. I may add a field for a note on this field but I would not store the note value in that field. 'Not applicable', 'Refused to answer', or other textual responses can be represented by a number and identified in a look up table. If identifying street names is important (as in the postal system example) then you ensure that Address number and Address Street are two separate fields enabling you to sort, group and filter easily by that field. (example: 123 Main St would be recorded as Address Number: 123, Street Name: Main St)

Envisioning your model is the starting place for building your application. Knowing what you want data to present and how you want to manipulate that data helps you know how to begin building.