Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Designing and Creating Databases » Designing and creating your database » The design process


Step 4: Resolve the relationships

When you finish the normalization process, your design is almost complete. All you need to do is to generate the physical data model that corresponds to your conceptual data model. This process is also known as resolving the relationships, because a large portion of the task involves converting the relationships in the conceptual model into the corresponding tables and foreign-key relationships.

Whereas the conceptual model is largely independent of implementation details, the physical data model is tightly bound to the table structure and options available in a particular database application. In this case, that application is SQL Anywhere.

Resolving relationships that do not carry data

To implement relationships that do not carry data, you define foreign keys. A foreign key is a column or set of columns that contains primary key values from another table. The foreign key allows you to access data from more than one table at one time.

A database design tool such as PowerDesigner can generate the physical data model for you. However, if you are doing it yourself there are some basic rules that help you decide where to put the keys.

  • One to many   A one-to-many relationship always becomes an entity and a foreign key relationship.

    Employees are members of departments, and departments contain employees.

    Notice that entities become tables. Identifiers in entities become (at least part of) the primary key in a table. Attributes become columns. In a one-to-many relationship, the identifier in the one entity will appear as a new foreign key column in the many table.

    The foreign key relationship between the Employees table and Departments table is Department ID = Department ID.

    In this example, the Employee entity becomes an Employees table. Similarly, the Department entity becomes a Departments table. A foreign key called Department ID appears in the Employee table.

  • One to one   In a one-to-one relationship, the foreign key can go into either table. If the relationship is mandatory on one side, but optional on the other, it should go on the optional side. In this example, put the foreign key (Vehicle ID) in the Truck table because a vehicle does not have to be a truck.

    A vehicle may be a truck, but a truck must be a type of vehicle.

    The above entity-relationship model thus resolves to the database base structure below.

    The foreign key relationship between the Vehicle table and Truck table is Vehicle ID = Vehicle ID.

  • Many to many   In a many-to-many relationship, a new table is created with two foreign keys. This arrangement is necessary to make the database efficient.

    Parts are stored at warehouses and warehouses contain parts.

    The new Storage Location table relates the Part and Warehouse tables.

    The foreign key relationship between the Part table and Storage Location table is Part Number = Part Number. The foreign key relationship between the Storage Location table and Warehouse table is Warehouse ID = Warehouse ID.

Resolving relationships that carry data

Some of your relationships may carry data. This situation often occurs in many-to-many relationships.

Between the Part entity and Warehouse entity is the Inventory entity, which contains the Quantity column.

If this is the case, each entity resolves to a table. Each role becomes a foreign key that points to another table.

The foreign key relationship between the Part table and Inventory table is Part Number = Part Number. The foreign key relationship between the Inventory table and Warehouse table is Warehouse ID = Warehouse ID.

The Inventory entity borrows its identifiers from the Part and Warehouse tables, because it depends on both of them. Once resolved, these borrowed identifiers form the primary key of the Inventory table.


A conceptual data model simplifies the design process because it hides a lot of details. For example, a many-to-many relationship always generates an extra table and two foreign key references. In a conceptual data model, you can usually denote all of this structure with a single connection.