Here are the principles that informed ny choices regarding the data map.
1) Fixed or constant data should be entered once and referenced indefinitely. This reduces the likelihood of errors and increases the ability to address errors as they are introduced. Examples of constant data are Imprints and Alternate Reference Sources.
2) Referenced data must be entered before the data that references it. This prescribes a sequence of data entry. For example, the Imprints table can essentially stand alone, since it does not depend on any other tables. Other tables depend on the Imprints table, so Imprints should be among the first tables created. In fact, I've already created that table in the actual database.
3) Tables and fields should be subdivided to allow maximum flexibility for the application, while also considering the effort required to enter and maintain the data. To that end I wanted to maintain the Haxby Notes information separate from the Notes Sold information, despite the obvious similarity in the data. This allows me to easily separate Notes that exist from those that are merely being described. I suppose I could do this by putting a flag field in the Notes data and leaving other cells vacant on the Haxby reference information, but to me it made more sense to separate the data (if for no other reason than the Haxby data is static and serves as reference data for the notes sold).
To better understand what I hope to accomplish by all this, and to see how this method is an improvement over Excel, I'll need to illustrate by example.
Using the current format of All_Lots there is only one column containing numerous pieces of critical data (State, City, Bank, Denomination, printed date, etc.). My ability to sort on that data is based entirely on the fact that I have organized the data within those cells in a specific way, and I cannot easily alter that sort pattern. So when I sort on that column I will always see things in the order of State/City/Bank/Denomination. Now suppose I wanted to do a report showing all banks named "Farmers and Mechanics". The current spreadsheet doesn't allow me to do that. Likewise, if I want to sort by denomination, I cannot do that.
Using a database with SQL queries I can combine and organize the data any way I want by simply composing the query correctly.
By maintaining the Haxby Note information separate from the Notes Sold information I can structure a query that essentially says "show me all the notes for which the Haxby Note imprints are different from the Notes Sold imprints". I could create a second query that seeks out all the notes for which the printed dates are different. I could combine these queries into a single report showing face different proofs. Though I didn't include the fields in the original map, I could also add information to track tints and overprints, pledges, security information, and so on... and write queries that show when those things don't match the Haxby description.
If these things are possible using Excel, I don't know how to do them.
By the way, the database described by the data map does not include the ability to maintain a census, since there are no count fields, save for the count of the number of plates on a sheet (which is not a cumulative count field).
As you can see, it's still a work in progress. I have, however, already learned how to import data from Excel and append it to existing tables.
- Greg
|