## 1.5.3 One-To-Many Mappings

One of the features that distinguishes this module from many others is its ability to deal with more complex datastructures than just simple tables. As an example of a one-to-many mapping we will consider an address book.

In our address book each person can have many addresses but each address is only associated with one person. The data structure looks like this:

                +--  Address 1
    Person 1 ---|
                +--  Address 2

To create a database to describe this struture we need two tables, a Person table and an Address table.

    import web.database, web.database.object
    connection = web.database.connect(type="sqlite", database="object-multiple.db")
    cursor = connection.cursor()

    person = web.database.object.Table("Person")
    person.addColumn(web.database.object.String(name="firstName"))
    person.addColumn(web.database.object.String(name="surname"))
    person.addMultiple(name="addresses", foreignTable="Address")

    address = web.database.object.Table("Address")
    address.addColumn(web.database.object.String(name="firstLine"))
    address.addColumn(web.database.object.String(name="postcode"))
    address.addSingle(name="person", foreignTable="Person")

    database = web.database.object.Database()
    database.addTable(person)
    database.addTable(address)
    database.init(cursor)

As in the introductory example we use the `addColumn()` method to add `Column` objects to the Address table. This time however we also use the `addSingle()` method to add a column named `person` to the table. We have also used `addMultiple()` method to add a multiple join called `addresses` from the Person foreign table to the Person table. The final change is that we have added the `Address` table to the database.

Note: We in the `addSingle()` and `addMultiple()` methods we refer to the `foreignTable` by the string representing its name and not the object itself.

When we access a person's `addresses` key, we will get back a list of all the Address objects associated with that person. Continuing the example above:

    >>> john = database['Person'].insert(firstName='John', surname='Smith')
    >>> print john['surname']
    Smith
    >>> print john['addresses']
    {}
    >>> database['Address'].insert(person=john, firstLine='12 Friendly Place', postcode='OX4 1AB')
    >>> database['Address'].insert(person=john, firstLine='3a Crazy Gardens', postcode='OX1 2ZX')
    >>> for address in john['addresses'].values:
    ...    print address['firstLine']
    ...
    12 Friendly Place
    3a Crazy Gardens

Note how we specify the person to add the address to using `person=john`. We could alternatively have specified the rowid of the person to add the address to. Just like the database, tables and rows, the value returned by john['addresses'] behaves like a dictionary. In this example we use the `values()` method to return a list of the `Row` objects.

It should be noted that you cannot set the values of multiple columns like the `'addresses'` column directly. Instead you should set the values of each object induvidually.

    >>> john['addresses'] = something # XXX Doesn't work!

Again you muct use `cursor.commit()` to commit the changes to the database.

Just for interest here is how the tables look in the live database. You can see that the person column in the Address table contains the rowid in the Person table of the person to associate the address with.

    # Tables in the 'test' database
    +----------------+
    | Tables_in_test |
    +----------------+
    | Address        |
    | Person         |
    +----------------+

    # The Person table
    +-------+-----------+---------+
    | rowid | firstName | surname |
    +-------+-----------+---------+
    | 1     | John      | Smith   |
    +-------+-----------+---------+

    # The Address table
    +-------+-----------------------+----------+--------+
    | rowid | firstLine             | postcode | person |
    +-------+-----------------------+----------+--------+
    | 1     | 12 Friendly Place     | OX4 1AB  | 1      |
    +-------+-----------------------+----------+--------+
    | 2     | 3a Crazy Gardens      | OX1 2ZX  | 1      |
    +-------+-----------------------+----------+--------+