The Data Layer - Tables and Relationships

Constructing Your Data Model

	Tables are the people, places, and things you want to track.
	Relationships connect Tables together.
	Fields collect and store data about Tables.

To create a usable application, you'll need to build a robust data model. A data model is a diagram of the people, places, and things you want to track with your software application,what information you want to track about them, and how they are interrelated. In WorkXpress, Tables, Relationships, and Fields comprise the data layer of an application and are the Building Blocks you'll use to realize your data model. This first lesson will teach you the key WorkXpress concepts you'll need know to begin constructing your own data model and will get you thinking about the people, places, and things you want to track with your application.

(What are Tables?)

We'll discover the first of the five WorkXpress Building Blocks, Tables, and learn how to use Tables to represent sets of people, places, or things. Relationships

(What are Relationships?)

After we've explained Tables, we'll move on to the second of our five Building Blocks, Relationships. We'll teach you to define Relationship Types that will connect Tables in powerful ways. Fields

(What are Fields?)

Finally, we'll briefly discuss the importance of our third Building Block, Fields, to your data model. Fields store and display data about your Tables and Relationships.

After completing this lesson, you should be able to answer these questions:

• What are Tables?
• What Tables do I need to define to accurately model the people, places, and things I'd like to track to meet my application building goals?
• What are Relationship Types?
• What Relationship Types should I use to connect the Tables in my application?
• How are Fields different from Tables?
• What Fields of data will I need to create to describe my Tables completely and accurately?
• When do I need to capture data about my Relationships using Fields?

Tables are the first and most fundamental Building Block in WorkXpress. Quite simply, Tables are the people, places, and things you want to track with your application. You'll represent sets or collections of those people, places, and things in WorkXpress as Tables. An Item Type is the unique name for a single group of people, places, or things.

• Let's say you have a list of many different companies with which you do business that you'd like to track with your application. Each of those companies is an Item, and you can represent all of those Tables in WorkXpress by creating an Item Type named Companies.
• Additionally, you have a set of people you would like to track. You can designate an Item Type, Contacts, to describe those people.
• You may want to have the ability to create and assign tasks to those people. So, let's create an Item Type named Tasks.
• Let's assume you've gathered that list of companies because you buy products from them or sell products to them. We'll create an Item Type called Products to represent them.

We have now defined four Tables: Companies, Contacts, Tasks, and Products.

Before building your own application, you'll need to define each of the Tables you want to track. Some of those Tables may not be obvious at first. Don't worry, as you organize your data model, you can continue to add more Tables to it.

Now that we've identified some Tables to track, let's learn how to relate or associate them. The Relationship Building Block exists to do just that. In WorkXpress, a Relationship Type identifies a connection between any two Tables Types. To create a Relationship Type, simply decide which two Tables you want to connect and assign a label to each side of the connection. For example, we've already decided to create Tables for Companies and for Contacts. Let’s suppose some Contacts work at some Companies; in this case we would describe them as employees of those employers. Conversely, we could just as easily describe our Companies as employers of those employees. So, let's connect Companies and Contacts using an Employer←–>Employee Relationship Type:

Now that we've created our first Relationship Type, let's think about some of the other natural associations that exist between our first four Tables and the Relationship Types we could create to model them. For instance, some of the Companies we've decided to track are vendors that sell products to your company. We can build a Vendor←–>Product Relationship Type that links those Companies and Products.

Some of the people on your list of Contacts may be contractors you'd like to assign Tasks. So, we could create a Contractor←–>Task Relationship Type to connect Contacts and Tasks.

Fields are the information you want to track about your Tables. For example, for each of your Contacts, you want to know their first name, last name, and email address. For each of your Companies you want to know the company name, address, and industry. A Field is both a Block that stores data in the data layer and a Block that presents information in the interface. First name, last name, email address, company name, address, and industry are all examples of Fields.

Fields in WorkXpress have the special ability to store data and present information about Relationships. Use this feature whenever the information you are tracking is not unique to a particular Item Type but rather to the connection between the Tables. For example, a Contact is an employee of one of our Companies. A history of the employee's title and salary would not really be about either the Company or the Contact; it would be about their connection as an employer-employee pair. For this example, we would create two Fields associated with the employee-employer Relationship, one called Title and the other called Salary.

The image above links everything we've discussed in this lesson and describes a simple Data Model that includes Tables, Fields, and Relationships.

To build software applications using WorkXpress, you must first define the Data Model you would like to employ. In other words, you must understand the Tables, Relationships, and Fields you'll need to create to achieve your goal. Keep thinking about the following questions as you continue to learn about WorkXpress:

What Tables are necessary to model the people, places and things your application will track? What Relationships will be needed to associate or connect your Tables? What Fields of data will you need to store about your Tables and Relations?