Associating Entities

When creating new entities for a model, you must consider whether to associate entities with database objects and how you want to do so. For example, how do you decide whether to represent the database object by a table or by one or more fields? You may also find that there are entities that are not associated with any database object. The Northwind database and sample project, Northwind.eqp, present a good example of the considerations to make when designing a model.

Associating an Entity With a Database Object

Most entities are associated with a database object; however, there are considerations as to which database object to use for an association.

Consider the entity, product. Two database objects in the Northwind database might represent products: Product Name field in the Products table and the Products table as a whole. If you specify that products are represented in the database by the field only, almost all of the questions work correctly. For example, if you ask, "Show the suppliers and their products," you get the right list. You can even ask, "Who sells Chef Anton's Cajun Seasoning?" and get the right answer.

If you ask for a count of products, you may get the wrong answer, however. If you ask, "How many products were sold by New Orleans Cajun Delights?", English Query will build SQL statements that count the number of products sold by that supplier. If there is more than one product with the same name, the answer does not include the duplicates.

Therefore, it is not accurate to say that the Product Name field in the Products table represents products. In fact, the entire Products table represents products because each unique row in the table represents a single product. The field Product Name, on the other hand, represents the names of the products, not the products themselves.

Entities Not Associated with Database Objects

Sometimes, there is an entity in a model that is not represented explicitly in the database at all. Consider the question, "Do buyers like Chef Anton's Cajun Seasoning?". To make questions like this work, create a relationship containing the phrasing, buyers like products (for example, with some SQL condition like product_name.1997_sales>1000). To create this phrasing, first create the entity buyer. Because there is no list of buyers in the database, create an entity not represented by a database object.