Wednesday, May 11, 2016

Databases - Advanced Level Lesson 2

In lesson 1 we looked at three specific areas in database theory. These were: search types, optimization and pointers. Today we will look at the following aspects:
  • Tables
  • Relational tables
  • Input formats
  • User interfaces

When you're dealing with databases, you are working in tables. If you are creating a customer database, one obvious thing you don't want to repeat every time is the ZIP code and connected city. That will be treated below in Relational tables.

You want elements to be strictly relevant for what you are creating. If you have a field for both first and last names, each containing up to 255 characters, you are wasting many bytes if you could settle with 20 characters for each field. In fact you are spending 470 bytes for each customer. When you add that up for each field in your tables, this will end up being a real waste of space - and you don't want that, regardless of the fact that USB-keys are getting cheaper by the day...

Relational Tables
As I mentioned above, having one table for all ZIP codes and cities can be very efficient, since you can then leave this information in a separate table that is accessible from all other tables you create. It reduces what is known as redundancy - and is quite effective in avoiding letters coming back when you send out parcels to your customers.

You could also have a product type table, so you don't have to add the type pencil to each new article you enter. The product type can help you get a much better overview.

Input Formats
Similarly, there might be a need to add some fields in other programs which your database is to interact with, and for that purpose you can define the format for what is being entered. You obviously don't want any comma separation for a telephone number. And you definitely want two commas in price lists - and such definitions will help keep everything working well once you begin using your database.

User Interfaces
You don't want people to enter data into your tables, so you create what is often known - at least when working in Access - as Forms. When you are working in a blogging system such as Wordpress, data is being stored in MySQL tables in a way that is unknown to you as a user. Your screen will appear almost like a word processing system, but WordPress converts each element into a predefined format that can easily be converted into XML - thus easily convertible when importing or exporting over to other blogging platforms.

If you have any questions, please feel welcome to write them below, as I welcome your feedback if something needs clarification or you need extra information beyond what I have already written. :-)

No comments: