Subtitles of the Movie

In this lesson we're going to get to know our database, our test database to be exact. In the support files provided with this tutorial we have a beginner level database with four tables. A Customer Table, the Office Table, Revenue Transaction and Employee Table. As you can see I've laid them out on the screen in the Database Expert Window of Crystal. This is where beginners especially need to be very, very careful. Crystal has some tools that will automatically link tables provided that the key field, i.e., the fields that define the relationships between the tables, are spelled exactly the same. Crystal cannot search the table for you, compare values and then make a connection, it is basing it's automatic ability to connect tables and make sense of them in a relational sense based on the spelling of the name. Now there are some databases I've seen out there that are perfect in that aspect, that they follow a standard naming convention that allows these relationships to share the same name across the way; that makes your job easier. However, there are many cases where that is not the case. Databases tend to evolve over time. Many modules are added on later, sometimes not by the same person or group. Rare is the database that is designed that can foresee future needs and plan accordingly. Before I walk you through the relationships of these specific tables, let me give you a little bit of background. This is designed to reflect a fictional vending company that sells gumballs and stuffed animals. High-tech and glamorous, I know, but please, try and contain yourself. Our main table is the Revenue Transaction, meaning that when we go out to a machine and collect the money from selling our gumballs and stuffed animals to clients at grocery stores. Then we have a Customer Table telling us who our customer is, i.e., it's location of our machines. Then we have the Office Table, which tells us which offices are in charge of said machines. Then we have our Employee Table, which tells us who services and picks up the machines, and who manages that office. Technically, the Customer is related to the Revenue Transaction Table between the ClientNum and the Client Number Field. Notice they're spelled differently. I do this so that you don't get into the habit of just letting Crystal decide for you. Databases can be often complex and sometimes if someone slips at something as easy as the wrong field name, you won't be able to make a join, or you'll make it even worse, and incorrect, go in and pull back garbage. The Office, as in the office that manages the customer relationships and machines at a client site, is connected via the Office and the OfficeNum Field, so the Customer Office Field to the Office OfficeNum Field. Now, the last relationship, and don't worry, we'll review these and we'll go through them as we're testing out our reports, is the Manager Field. The Manager connects to the EmployeeID in the Employee Table. Again, when you're pulling more than one table you need to get into the habit of understanding how the relationships flow between the tables. In some cases they're clearly marked. In other cases they are not. That will require a little more groundwork, but remember, as we stated before in the previous lessons, Crystal does not think critically for you. In this case, you're being guided through what relates to what. In your own case you'll need to discover these relationships and understand how they work. In some cases you may need thousands of tables; in some cases you may need a dozen. It all depends on your individual database center.