Database Tables

DataMapper is implemented with Database normalization Fifth normal form in mind. In short, that means every table is aware only of itself, with fields relevant only to itself. If a table has a relationship with another table, it is represented by a special joining table. These joining tables hold all the relationships between records of other tables.

Lets take a look at the below example.

countries

id	code	name
12	AM	Armenia
13	AW	Aruba
14	AU	Australia
15	AT	Austria

countries_users

id	country_id	user_id
1	14	7
1	12	8

users

id	username	password	email
7	Foo	ec773c1da6f96b0265d76fa0a53db697e66a8eea	foo@bar.com
8	Baz	383f27f548397ea123ec444505ef4c7cd993dbf6	baz@qux.com

Here we have 3 tables. Tables countries and users are normal tables. Table countries_users is the joining table that stores the relations between the records of countries and users.

The joining table shows that country ID 14 (Australia) has a relationship with user ID 7 (Foo). Country ID 12 (Armenia) has a relationship with user ID 8 (Baz).

Rules

Every table must have a primary key named id.
Normal tables must be named the lowercase, pluralised version of the object name. So for a user object of User, the table would be named users. For Country, it would be countries.
A joining table must exist between each related normal tables, regardless of the type of relationship.
Joining tables must be named with both of the table names it is joining, in alphabetical order, separated by an underscore (_). For example, the joining table for users and countries is countries_users.
Joining tables must have a specially name id field for each of the tables it is joining, named as the singular of the table name, followed by an underscore (_) and the word id. For example, the joining id field name for table users would be user_id. The joining id field name for table countries would be country_id.

Special rules for Self Referencing Relationships

If you have a self referencing relationship (read Relationship Types for more information), the joining tables have a slightly different set of rules.

Joining tables must be named with both of the model names it is joining, in lowercase alphabetical order, separated by an underscore (_). For example, if you have an employees table with models such as Manager and Supervisor both using employees as their table and having a relationship between each other, the joining table is managers_supervisors (the normal way would have been employees_employees which is too restrictive for self referenching relationships, as it would only allow one, and why the model names are used for this instead).
Joining tables must have a specially name id field for each of the models it is joining, named as the lowercase singular of the model name, followed by an underscore (_) and the word id. For example, the joining id field name for model Manager would be manager_id. The joining id field name for model Supervisor would be supervisor_id.

Here's a example of self referencing tables:

employees

id	first_name	last_name	position
1	Fred	Smith	Manager
2	Jayne	Doe	Supervisor
3	Joe	Public	Underling
4	Sam	Rogers	Underling

managers_supervisors

id	manager_id	supervisor_id
1	1	2

supervisors_underlings

id	supervisor_id	underling_id
1	2	3
1	2	4

From the above, we can see that employees is a normal table and that managers_supervisors and supervisors_underlings are our self referencing joining tables. Looking at the data, we find:

Fred Smith is a Manager who manages Jayne Doe who is a Supervisor that supervises Joe Public and Sam Rogers (both Underlings).

That's pretty much it as far as your tables go. The setting to signify if tables are joined with a One to One, One to Many, or Many to Many relationship is setup in the DataMapper models.