Relearning Databases

Unfortunately, I didn't get too much done over the short school week. After rechecking though, I noticed that there is an MS Access version of the USDA database. While I think that Access databases are backed by SQL, I will still need to convert to a raw SQL database (I think). The database is fairly large (120 MBs), so for the final product I can't store the entire database locally (I didn't really expect to be able to either). In the end, I will like just have to keep frequently used items local, and look up everything else through a server. But for now, assuming it doesn't make uploading to my dev phone impossible, I will probably just work with the entire database locally and work out remote access once I have a working prototype.

The WP database interface is Linq to SQL which is an Object Relation Mapping framework. Basically what this means is that I can look up a food by its ID and automatically I get the entire food object with any of its references loaded from the database automatically. Essentially if I have a table for food and a table for manufacturers, when I load a food with a reference to a manufacturer, the manufacturer will be loaded as an object as well. All you need to do is define the object structure and Linq does the queries for you. This works well, but for the nutritional information I want to have dynamic nutrient information (some foods have long lists of vitamins and minerals while some foods have very little nutritional information). I will need to figure out the best way to design my classes for dynamic tables. The way it is currently set up in the USDA database is that there is a table with non-unique id entries. The ID corresponds to a particular food, and each entry tracks a specific nutrient, and so in order to find all the nutritional information you can simply search for all entries with the foods id.