How do I manage database relationships in objects on iPhone with Sqlite

Question

For instance, if I am able to display a UITableView with all the movies and the Genre. Clicking Edit on the movie should load a new view(is this the right way, loading an edit view?) with a text box and some sort of selector or another view to choose the Genre. Once the Genre is selected, how do I capture the GenreId to save in the movie table?

CREATE TABLE movie(pk INTEGER PRIMARY KEY, title TEXT, genreId INTEGER);

INSERT INTO make(title) VALUES('GoldFinger', 1);
INSERT INTO make(title) VALUES('Octopussy', 1);
INSERT INTO make(title) VALUES('Love Story', 3);
INSERT INTO make(title) VALUES('Where Eagles Dare', 1);
INSERT INTO make(title) VALUES('Zombies', 2);

CREATE TABLE genre(pk INTEGER PRIMARY KEY, title TEXT);

INSERT INTO genre(title) VALUES('Thriller');
INSERT INTO genre(title) VALUES('Horror');
INSERT INTO genre(title) VALUES('Romance');

I am using the Sqlitebooks sample as a guide.

// Movie.h
@interface Movie : NSObject {
// Opaque reference to the underlying database.
sqlite3 *database;
// Primary key in the database.
NSInteger primaryKey;
// Attributes.
NSString *title;
NSInteger *genreKey;
// Internal state variables. Hydrated tracks whether attribute data is in the object or the database.
BOOL hydrated;
// Dirty tracks whether there are in-memory changes to data which have no been written to the database.
BOOL dirty;
NSData *data;
}
// Property exposure for primary key and other attributes. The primary key is 'assign'     because it is not an object,
// nonatomic because there is no need for concurrent access, and readonly because it     cannot be changed without
// corrupting the database.
@property (assign, nonatomic, readonly) NSInteger primaryKey;
// The remaining attributes are copied rather than retained because they are value     objects.
@property (copy, nonatomic) NSString *title;
@property (copy, nonatomic) NSInteger *genreKey;
// Finalize (delete) all of the SQLite compiled queries.
+ (void)finalizeStatements;
// Creates the object with primary key and title is brought into memory.
- (id)initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db;
// Inserts the book into the database and stores its primary key.
- (void)insertIntoDatabase:(sqlite3 *)database;
// Brings the rest of the object data into memory. If already in memory, no action is     taken (harmless no-op).
- (void)hydrate;
// Flushes all but the primary key and title out to the database.
- (void)dehydrate;
// Remove the book complete from the database. In memory deletion to follow...
- (void)deleteFromDatabase;

// Movie.m
// Flushes all but the primary key and title out to the database.
- (void)dehydrate {
    if (dirty) {
    // Write any changes to the database.
    // First, if needed, compile the dehydrate query.
    if (dehydrate_statement == nil) {
        const char *sql = "UPDATE movie SET title=?, genreid=? WHERE pk=?";
        if (sqlite3_prepare_v2(database, sql, -1, &dehydrate_statement, NULL) != SQLITE_OK) {
            NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
        }
    }
    // Bind the query variables.
    sqlite3_bind_text(dehydrate_statement, 1, [title UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(dehydrate_statement, 2, [genreId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(dehydrate_statement, 3, primaryKey);
    // Execute the query.
    int success = sqlite3_step(dehydrate_statement);
    // Reset the query for the next use.
    sqlite3_reset(dehydrate_statement);
    // Handle errors.
    if (success != SQLITE_DONE) {
        NSAssert1(0, @"Error: failed to dehydrate with message '%s'.", sqlite3_errmsg(database));
    }
    // Update the object state with respect to unwritten changes.
    dirty = NO;
}
// Release member variables to reclaim memory. Set to nil to avoid over-releasing them 
// if dehydrate is called multiple times.
[movie release];
movie = nil;
[data release];
data = nil;
// Update the object state with respect to hydration.
hydrated = NO;
}
@end

Answer 1

This does not directly answer your question, however, I would suggest looking into the new CoreData APIs added in the 3.0 SDK. They will allow you to model your relational data and abstract away any manual SQL work allowing for less code and if done properly, better performance.