Converting an ERD to a Physical Database: Page 2

By: Dan D'Urso
Published: October 2002

Many-to-many relationships (Cont'd)

Place the primary keys of the two original tables into the intersection table as foreign keys. These two columns then often form a composite primary key for this table. They are not optional, so don't allow nulls. Create additional columns for the attributes of this relationship from your diagram. Using enrollments as an example the composite primary key might be studentid, courseid. Alternatively, we could create a separate surrogate ("auto-number") primary key. However, in this case you may need to add a unique constraint or index later to preserve the unique pairing implied by the original composite primary key. Additional columns might be semester and grade.

Supertype-Subtype Relationships

Convert the supertype entity to a table. For columns use the attributes that are common across all the subtypes. For example say in our field engineering example we had a document supertype. All documents have a date, author and hyperlink. Place these in the new table. Use the entity identifier as the primary key - say document number in this example. You may also want to add an indicator column that holds a code showing which subtype instance a given supertype instance is related to.

Now create a table for each of the subtype entities. Each table will have the same primary key as the supertype - in this case document number. The primary key also serves as a foreign key. Now create columns in each new table for those attributes that are unique to that subtype.

Step 2: Set Field Constraints

Once you have your table defined you will want to make sure you have the field properties set to enforce those business rules that can be enforced at the field level. Typically, these are things like requiring a customer name in a customers table, making sure a line item quantity sold is greater than 0, etc. Let's take the relevant Access field properties one-by-one. (Be aware you have to set these manually if you use an Access template or wizard.)

Default value: Would you like the field to default to a given value every time the user adds a new record? For example, automatically insert today's date for an order date field? Then place your default value here.

Validation Rule: Use this property to check an entry against a business rule such as making sure a quantity is > 0. This rule is applied by the database when the user leaves the field.

Validation Text: Place here the error message you wish the user to receive if the Validation Rule is violated.

Required: This is equivalent in SQL databases to allowing NULL's or not. It is a very important property. If the attribute is optional then Required should be set to No, if mandatory then set to Yes. Use this property to ensure that the user enters things like the customers last name, the date of an order, etc. Otherwise you run the risk of compromising the integrity of the database with incomplete records that cannot be used in searches, reports, etc.

This rule is applied by the database when the user causes the record to be saved.

Indexed: We will cover this in step 4.

Table constraints: The above constraints are all column constraints. They apply to only one column or field. To create a constraint that applies to more than one field in the table, say checking that the ship date is greater than or equal to the order date, use a table constraint. To do this right click on the design surface of the Table Designer and select Properties. There will be a Validation Rule and Validation Text property as above. The difference is that you can refer to multiple columns in the rule. These rules are applied when the user causes the record to be saved.

Steps 3 and 4:

The next two steps are to create the relationships and then set up the indexing.

more