Data Sources and Storage

The next question that you, as a developer, need to consider is where the data from the application is coming from and where it will be stored. This application is particularly complex because there will be data coming from outside sources (stock quotes), data entered by the user (transactions and account information), and data entered by the staff (news stories).

Because historical as well as current stock information will be needed (eventually, for example, it would be good to be able to display charts of stock prices over time), the stock quotes will be stored in the database and updated hourly. In the finished application, a separate program would run, getting the quotes and writing them into the database. For this example, the quotes will be made up randomly and stored for future use.

A quick pass over the requirements can pull out all the objects that will be needed to make the application run:

• A user object that stores the account information

• A stock object that stores permanent information about each stock

• A stock price object that stores a stock quote at a given moment for a stock

• A transaction object that stores a user's purchase or sale of a stock

CHOOSING PRIMARY KEYS

It might be tempting to use the stock symbol as the primary key for the stock table. After all, there's a one-to-one relationship between stock symbols and stocks. However, a general rule of thumb in database schema design is to avoid that kind of direct mapping in favor of sequential ID numbers .

There are several good reasons for this. For one, most databases can do a join against a number much faster than they can against a string. In addition, stock symbols do change on occasion, and if you were using the symbol as a key, you'd have to update it wherever it was being used as a foreign key. By using a sequence number instead, you would have to update the value in only one database row.

This object list can be turned into an entity relation diagram (ERD), as shown in Figure 6.7.

Figure 6.7. The ERD for the Stock Tracker application.