Databases from scratch II Simple Database Design. Have you ever noticed advertisements in the job classifieds for database architects or database administrators It makes database design sound pretty intimidating, doesnt it If theyre so difficult to use that they require architects and administrators, what hope do we mere mortals have In fact, databases range from being ultra simple to use to ultra complex. In the world of personal computers, there are two main types of database programs. For the sake of our discussion a relational database is a persistent storage mechanism that enables you to both store data and optionally implement functionality. The Realm of Chaos within the Empyrean. Insane savants argue that the material universe experienced by mortals represents only the tip of the iceberg and unseen. Walter J. Travis January 10, 1862 July 31, 1927 was an American amateur golfer in the during the early 1900s. He was also a noted golf journalist and publisher. Leon Orlov October 6, 2016 at 504 pm. Dmitri, Great presentations during 2015 PASS. Looking forward attending PASS 2016 sessions. Amor Estranho Amor Completo Dubladores on this page. Leon. With an unusually long delay since our last newsletter, we would like to inform you about the latest release of the Reptile Database which was released a. At the simple end of the scale are flat file databases, also called single file or list managers. These programs are as easy to learn as a word processor or spreadsheets. I/51i4%2BdP06mL.jpg' alt='Database Design Mere Mortals' title='Database Design Mere Mortals' />The initial concepts may take a little more time to absorb than word processing, but theyre well within anyones grasp. Drivers License Parsing Software Downloads. If youre using a database at home, in a class at school or in a small club or organisation, chances are the simple flat file database will fill your needs. Relational databases. Things can get more complex when you use the other type of personal computer database program, called a relational database. With a relational database program you can create a range of databases, from flat file structures to demanding multi file systems. If youre using a database in your small business, a large organisation, or an ambitious school project, youre likely to need at least some of the features of a more complex relational database. Whichever type of database program you use, the most crucial step in using it is to design your database structure carefully. The way you structure your data will affect every other action. It will determine how easy it is to enter information into the database how well the database will trap inconsistencies and exclude duplicate records and how flexibly you will be able to get information out of the database. A simple example. Lets take an ultra simple example the phonebook. Say youve been given the job of placing your school or organisations phone directory on computer. It should be easy all you need is the name, the address and the phone number of each person. M Macbeth Serial. Your initial table design thus consists of three fields name, address and phone number. Right Lets check. Testing your design. The way to see if your database design works is to test it with some sample data, so feed the following records into your hypothetical table J. T. Apples, 1. 00 Megalong Dr, Haberfield, 4. B. York, 25. 3 Alice St, Leichhardt, 5. M. R. Sullivan, 9 Jay St, Leichhardt, 4. B. J. Anderson, 7. Wally Rd, Glebe, 2. Now tell the database program to sort the information B. J. Anderson, 7. 1 Wally Rd, Glebe, 2. B. York, 25. 3 Alice St, Leichhardt, 5. J. T. Apples, 1. 00 Megalong Dr, Haberfield, 4. M. R. Sullivan, 9 Jay St, Leichhardt, 4. Revising your design. Immediately, you can see this is not what you want. You want the table sorted alphabetically by last name, not initials. How can you fix this Well, you could do some complex manipulation using a database feature called string functions if your program supports such a feature. Or, you could come up with a better table design in the first place last name, initials, address and phone number. Feed your test data into this revised structure then tell the database program to sort your table using the last name followed by the initials. This time, youll get the correct alphabetical listing Anderson, B. J., 7. 1 Wally Rd, Glebe, 2. Apples, J. T., 1. Megalong Dr, Haberfield, 4. Sullivan, M. R., 9 Jay St, Leichhardt, 4. York, B., 25. 3 Alice St, Leichhardt, 5. Keep on refining. Dont stop there. The table can be even more effective if you break the structure down further. For instance, if youd like to have an easy way to list only those people who live in Leichhardt, this design wont help you. But with a little more work, you can break your database structure down further into last name, initials, street address, suburb, and phone number. With this structure, youll be able to sort your database alphabetically by last name or by suburb, and youll be able to pluck out all those people who live in a particular suburb. A visual example. Heres another example of this process in action, starting with a badly designed table structure Figure 1 A simple and flawed students table. This fragment of a students table suffers from various defects. The combined name field makes it hard for us to locate students by using their last names, and also difficult to sort the table alphabetically by last name. Even worse, how do we uniquely identify each student Currently, this table uses a combination of the Name and Date of Birth fields to identify each student. What if we happen to have two Jim Smiths, both born on 5th September 1. Its unlikely, yes, but not impossible. The composite address field is also a problem for instance, how do we pre sort mail by postal code so we can do bulk mailouts at discount ratesBy breaking the fields down we end up with double the number of fields but a much simpler and more effective structure. The new Student ID field lets us uniquely identify any student, even when they share names and birthdates. Weve split the Names field into first and last names so we can get an alphabetical listing. Weve done the same to the address field, allowing us to sort by postal code or suburb. Figure 2 An improved students table. More is less. Notice how creating an efficient table structure consists of breaking down your fields into simpler and simpler components You end up with a table with many more fields than you might originally have thought necessary, but each of those fields houses much more basic information. Theres a technical term for this process normalisation. If you wanted to become a database architect youd have to become conversant with normalisation and functional dependencies and normal forms. If youre happy to remain a lesser mortal, you can safely ignore these terms, provided you keep in mind that your task is to create a database structure that provides an efficient store for your information and that makes it flexible and easy to extract useful information. In some ways, creating a database thats effective and simple to use is almost an anti intuitive process. For example, our initial structure for the phone directory nameaddressphone numberseems like its a simpler design than our end result first namelast namestreet addresssuburbphone number. Creating useful fields. What you need to remember is that while the structure might look more complex, the contents of each field have been reduced to the simplest useful components. I say useful because we could, of course, break each field down further. For instance, we could break the street address field into two fields, street number and street name. But what would be the pointTheres no need to extract or sort information in the database simply by street number or name, and so its not a useful basis for breaking up the field. On the other hand, if we wanted to deal with multi line addresses which are common for businesses, such as Suite 5. Jones Streetthen it makes sense to break the address field down into two simpler fields, address line 1 and address line 2. Youre not likely to want to sort information based on only one of these fields, nor are you likely to use either of these fields in isolation. What is likely is that youll want to have an easy way to print address line 1 and address line 2 as separate lines when addressing envelopes.