BaseTen supports the same types of relationships as Core Data: one-to-one, one-to-many and many-to-many.

One-to-many is the simplest type of these three: foreign keys in one table referring another will be interpreted as such. Both of the tables need to be BaseTen enabled and BaseTen's cache tables need to be up-to-date (see the BaseTen Assistant for details). Calling -[BXDatabaseObject valueForKey:] for an object on the to-one side with the name of the foreign key constraint will return the object on the other side of the reference. For the to-many side, -valueForKey: can be called for an object that is referred to in another table's foreign key constraint. In that case, a collection of objects is returned. They key used is the other table's name.

Consider this example:

CREATE TABLE person (
        id SERIAL PRIMARY KEY,
        firstname VARCHAR (255),
        surname VARCHAR (255)
);

CREATE TABLE email (
        id SERIAL PRIMARY KEY,
        address VARCHAR (255),
        person_id INTEGER CONSTRAINT person REFERENCES person (id)
);

Now, lets say we have two objects: aPerson and anEmail which have been fetched from the person and email tables, respectively. [aPerson valueForKey: @"email"] will now return a collection of email objects. [anEmail valueForKey: @"person"] will return a single person object.

If we modify the previous example, we get a one-to-one relationship:

ALTER TABLE email ADD UNIQUE (person_id);

Now both [aPerson valueForKey:@"email"] and [anEmail valueForKey:@"person"] will return a single object from the corresponding table.

Many-to-many relationships are modeled with helper tables. The helper table needs to have columns to contain both tables' primary keys. It needs to be BaseTen enabled as well.

Another example:

CREATE TABLE person (
        id SERIAL PRIMARY KEY,
        firstname VARCHAR (255),
        surname VARCHAR (255)
);

CREATE TABLE title (
        id SERIAL PRIMARY KEY,
        name VARCHAR (255)
);

CREATE TABLE person_title_rel (
        person_id INTEGER REFERENCES person (id),
        title_id INTEGER REFERENCES title (id),
        PRIMARY KEY (person_id, title_id)
);

Lets say aPerson has been fetched from the person table and aTitle from the title table. In this case, [aPerson valueForKey: @"title"] will return a collection of title objects and [aTitle valueForKey: @"person"] a collection of person objects. Any two foreign keys in one table will be interpreted as a many-to-many relationship. Objects from the helper table may be retrieved as with one-to-many relationships: [aPerson valueForKey: @"person_title_rel"].

Modifying relationships

In case of to-many relationships, collections returned by -valueForKey: are NSCountedSet proxies. They can be modified directly, and changes will be propagated immediately to the database.

Relationships can also be modified using -setValue:forKey:. The value should be either a single object or a collection depending on the type of the relationship. Nil values can be used to remove all the objects from the relationship. However, modifying the collection is preferrable to always giving whole collections to -setValue:forKey: if every object will not change.