ASA SQL User's Guide
Designing Your Database
The design process
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 Adaptive Server Anywhere.
In order 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 the DataArchitect component of Sybase PowerDesigner can generate the physical data model for you. However, if you're doing it yourself there are some basic rules that help you decide where to put the keys.
One to many An one-to-many relationship always becomes an entity and a foreign key relationship.
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.
In this example, the Employee entity becomes an Employee table . Similarly, the Department entity becomes a Department 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.
The above entity-relationship model thus resolves the database base structure, below.
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.
The new Storage Location table relates the Parts and Warehouse tables.
Some of your relationships may carry data. This situation often occurs in many-to-many relationships.
If this is the case, each entity resolves to a table. Each role becomes a foreign key that points to another table.
The Inventory entity borrows its identifiers from the Parts and Warehouse tables, because it depends on both of them. Once resolved, these borrowed identifiers form the primary key of the Inventory table.
TipA 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. |