QuickBase Straight Up

We’re going to depart briefly from our normal focus on “All QuickBase, All the Time” because no matter how much you know about QuickBase, if you don’t understand how to design your database, it’s not going to work well.

If you have a database and you start seeing problems like the following, it is probably because your database is not designed correctly:

• You are trying to create a report that seems like it should be simple, but you can’t get QuickBase to produce the report, no matter how hard you try.
• You need to change some data, and you need to make the change in more than one place.
• You can’t figure out where to enter data. For example, you have a table of “Lawyers” with a field for the name of their assistant — but then you run into a lawyer with two assistants and there is no place to put the name of the second assistant.
• You want to delete something that you don’t need, but the only way you can do it is by deleting something you do need. In that same example, if a lawyer’s assistant leaves, then you want to delete their information — but since the lawyer is in the same table, you end up deleting the lawyer too!

OK, so what do I need to know to get the design right?

Some good news here: QuickBase takes some steps to make sure you do it right.

There is a Record ID# (a unique number) assigned to every record in every table. That is called the key field, and the first rule is: Don’t change the key field unless you know what you are doing.

I’m not going to go into why this is important - if you are curious, look up the First Normal Form of Data Normalization. QuickBase users used to change the key field in order to change how the record title displayed, but that is no longer necessary since you can now change the record label on the table’s Advanced Properties tab.

More good news: When you set up a relationship between two tables, QuickBase automatically uses the key field to make the connection. In this case, QuickBase won’t even let you mess it up, so no worries here.

But one thing QuickBase cannot help you do is set up the tables correctly in the first place. To get that correct, read on:

What you really need to know

Before you create your database, think of what you are trying to keep track of. Make a list of those things. Then for each thing, figure out whether it corresponds to one, or more than one, of the other things.

If the answer is one, then those things must be in the same table. If the answer is more than one, then those things must be in different, related tables.

For example, say you are keeping track of law offices, lawyers, their mobile phone numbers, their spouse’s name, and the cases the lawyers are working on.

Now let’s figure out whether each of those corresponds to one, or more than one, of the others. Start at the top.

Can each law office have more than one lawyer? Yes — so Law Offices and Lawyers should go in different, but related tables.

Can each lawyer have more than one mobile phone number? Theoretically they could, but we can’t design for every weird eventuality. I would say one lawyer, one cell phone — so they should go in the same table. (Not everyone would agree.)

Can each lawyer have more than one spouse? Here I think we are on pretty clear ground. Absent TV sitcoms, each lawyer needs to be content with no more than one spouse - so lawyers and spouses go in the same table.

Finally, can each lawyer have more than one case? Definitely yes - so cases and lawyers go in separate, but related tables.

Of course, there is a lot more to database design. You’ll learn as you get experience. But if you design according to the very simple rules above, you will avoid 95% of the mistakes in designing a database.

Next Issue: Back To QuickBase

For better or for worse, there are a lot of places to get help with QuickBase. We’ll give you a guide of what to look for, and what you might find when you need a little help.

Plus — tips on making your QuickBase application look like an Iphone app, from friend-of the-newsletter David Bruton.