# How should I design a database for multi-year data analysis with JMP?

I'm building a database of economic and political indicators provided by the World Bank and other similar sources. I would like to run uni- and multivariate analysis of these indicators over the past 20 years of data. I'll be using JMP to analyze the data.

Here's an excerpt of the database structure:

ID          | Pop. | GDP | (20ish other columns)
------------------------------------------------
Afghanistan | xx   | xx  | xx
Albania     | xx   | xx  | xx
Algeria     | xx   | xx  | xx
Andorra     | xx   | xx  | xx
etc.        | xx   | xx  | xx


What would be the best way to input this data so that JMP can deal with all 20 years of data? Should I have 20 separate tables, one per year? Or should I have a column for each year all in one table (GDP_1990, GDP_1991, GDP_1992, etc.)

## 1 Answer

20 separate tables means 20 times as much work for everything you do: forget that!

I notice your "database structure" does not seem to include an attribute for year. How is that information going to get into JMP?

There are two useful structures for these data: "long" and "wide". The long structure consists of tuples of (ID, Pop., GDP, ..., Year). The wide structure lists all years at once in each row: (ID, Pop_1990, ..., Pop_2009, GDP_1990, ..., ...). Depending on the software, some procedures are easiest (or feasible only) with one structure and other procedures need the other structure. Typically, tasks that query or sort the data by year (e.g., do separate regressions by year) benefit from the long structure and tasks that make pairwise comparisons (e.g., a scatterplot matrix of GDP over all years) benefit from a wide structure. You will need both structures.

Good stats software supports interconversion between long and wide structures. It's usually easiest to convert long to wide, and there are powerful reasons on the database management side in favor of long, so as a rule, I start with the long format in all projects. I can't remember what JMP does--I recall it's somewhat limited--so you might need to enlist the help of a real database management system, which is not a bad idea anyway.

I notice your question is specifically about inputting data. That issue is really separate from how the data are to be maintained. For example, if you are manually transcribing tables, by far the best procedure is to create computer-readable copies of those tables. This makes checking the input easier and more reliable. Then write little scripts, if you have to, to assemble, restructure, and clean the input as needed. In particular, do not let the structure most convenient for getting the data into JMP determine how you will manage the data throughout the project! By the same token, do not let the format of typical summaries or reports determine the database structure, either.

• Whoa. That posted prematurely… the table structure didn't account for year since I was assuming that I'd have a separate table for each year… so the year was inherent. I think, like you suggested, I'll have separate tables while I input the data (which I have to mangle from existing Excel files), but I'll combine them into a wide-structured final analytical database. Feb 3 '11 at 19:18
• Now I just need to figure out how JMP works with long and wide structures… Feb 3 '11 at 19:18