You've turned to Quick Base to help you track a process or to store important information. Whether you're creating an application from scratch or plan to expand upon an app from Quick Base Exchange, you'll need to examine your process and the information involved so you can structure your application in the most efficient way. The structure of your application affects not only how data is stored, but also the forms through which users enter data and the data available in reports.
The same application can be designed in many different ways. You might design an application one way and your colleague might take a slightly different approach. Both designs may work well. Get your staff members to test your design, then get their feedback. Is it easy to use? Does it help them do their job? Do your reports display the expected information? Based on their reviews, you can make changes. The best part about working with Quick Base is that it's flexible. Try an approach you think works well. If you find it doesn't suit your process, you can change your application structure at any time.
Follow these steps to design a Quick Base application.
Step One: Define what information you want to track - at its highest level
Identify general categories of information you need to track. These categories might be things like customer names, companies they belong to, and products you sell. Think big! What are the largest chunks of information you need to store and follow? Is it Projects and related Tasks? Or Salespeople and their Activities? Usually these general categories of information will have additional data that comprise or describe them. For example, a Product may have a name and a price. You'll get to these details in a minute. For now, decide what the largest categories of things are.
These general categories will become the tables that make up your Quick Base application. (What's a table?)
Note: You should give your table a pluralized name, such as Projects, Tasks, and Products, because the table will contain multiple individual records. For instance, the Tasks table contains many Task records.
How do you know what information belongs in its own table? Some guidelines:
Will you be typing certain values again and again? If so, those values belong in their own table.
Values that change frequently don't belong in the same table as values that never change. For instance, you may place a new order of books every day, but the information on the vendors (name, location, telephone) from whom you purchase these books rarely, if ever, changes.
Tip: Think in the most general terms about what tables you need. For example, say your goal is to track invoices by month. Should you create a table for each month? NO! May, June, July should not be tables. In fact, you should NEVER use a table to represent time periods. Date information is always tracked within a date field. (You'll read more about fields in a minute.) Instead you'd create one table called invoices. Then you could generate reports organized by month or create one large report grouped by month or by quarter based on invoice date.
Step Two: Itemize details within each category
Next, identify the components, the individual chunks of information you want to track for each category. In other words, what boxes do you want to appear on each data-entry form that your users fill out, and what data do you want to appear on your reports?
Tip: As you contemplate what chunks of information you need, keep in mind all the ways you'll want to use this information. For example, say you want to track Customer Name. Will this be one field? In other words, should users enter the first and last name together in one box? Or should they be separated out in case you want to sort records by last name or extract first names to generate personalized letter salutations? Usually, it's best to create two fields: First Name and Last Name.
For instance, if you were to create an Invoice, you'd need to record a date and an invoice number. It's your job to create a place for these individual pieces of information to live. In Quick Base, that place is called a field (Read more about fields.) So, to store this information, you'd create two fields within the Invoices table: Invoice Data and Invoice Number.
Great, but you also need to track Customer name and address. After all, somebody needs to pay this invoice and understand what item they're purchasing. This information definitely belongs on the invoice too. However, these additional details probably exist in one or more of the other "general information categories" (tables) that you defined in Step One. So, "customer" info belongs in its own table, but it also belongs on an invoice! What this conflict tells you is that information in different tables is related. Customers buy Products which are billed via Invoices.
Step Three: Identify relationships between tables
Are the general categories you defined in Step One related to each other? If so, how? Do they have a one-to-one relationship, a one-to-many relationship or a many-to-many relationship? For example, one employee may be assigned one workstation. One customer may buy many products. Many students may take many classes.
To illustrate the process outlined in Steps 1-3, imagine that you run an art gallery.
Say you examine your art gallery business and determine that your Quick Base application needs to track the following general items: Works, Artists, Customers and Invoices.
Next, outline the smaller kernels of information that relate to each general category:
Artists Works Customers Invoices First Name Title First Name Invoice # Last Name Price Last Name Date Tax ID# Media Address
Note: Wait! Invoices require much more information, like customer name and purchased art work(s). Yes, but all those values already exist in other tables. The beauty of Quick Base is that these details live in only one place, but can be displayed across tables. In other words, you'll make Customer and Work values appear on an invoice as a result of the relationships you're about to define.
Finally, figure out if and how each category (table) relates to others. In this case:
One Artist creates many works
One invoice can list many works
One customer can have many invoices.
If it helps, create a diagram that shows it all:
This diagram shows each table (rectangles) with fields attached in satellites (circles). Relationships between tables are indicated with a green line. The three-pronged end of that line is attached to the "many" table in a "one-to-many" relationship.
When you create a relationship between tables (similar to a join command in SQL), you designate a field with which to link them. This field is called a "reference" field. "Reference" is not a field type, but an attribute of a field. For instance, when you create a relationship between Invoices and Customers, the reference field in the Invoices table lets you select which Customer gets the Invoice. If you want to display additional details about the customer, like address, use a lookup field in the Invoices table do so. (Read more about relationships.)
The process you just read about and the resulting diagram are all part of what database professionals call "data modeling". Sounds fancy, but now you know that it's a simple process of breaking your information down into categories and components and deciding how each group relates to the others.
You can read more about database design principles, such as data modeling and entity-relationship diagrams, on Wikipedia (http://en.wikipedia.org)
Step Four: Identify your form and report needs
Now that you have identified the basic structure of the application, consider what data you want to see in your forms and your reports.
When you link tables together in a "one-to-many" relationship, the "one" table is called the "master" table, and the "many" table is called the "details" table. In the application we have been designing in this example, Customers is the master table and Invoices is the details table. You can set up your application so that your Customers table forms and reports include data from related Invoice records, and vice versa.
You can include lookup fields in the Invoices details table to pull in information from the related Customer master records, like the customer's name and address. You can include summary fields in the Customers master table to perform calculations on data in related Invoice detail records. You can even embed a report of Invoices records within the Customers form.
Read more about creating forms.
How do I create a field like Invoice Total, which gets its value from other fields on my form?
Often you'll need to include a field that gets its value by performing calculations on values from other fields. For example, a field like Total might display the sum of values from the Subtotal and Tax fields. You can accomplish this in Quick Base with a special type of field—a Formula field. Read all about it.
Restructuring data you've imported to Quick Base: Converting a column into a table
© 1999-2019 QuickBase, Inc. All rights reserved. Legal Notices.