User's Guide Part III. Using SQL Anywhere Chapter 16. Designing Your DatabaseIn designing a database you plan what tables you require and what data they will contain. You also determine how the tables are related.
You must determine what things you want to store information about (each one is an entity) and how these things are related (by a relationship). A useful technique in designing your database is to draw a picture of your tables. This graphical display of a database is called an Entity-Relationship (E-R) diagram. Usually, each box in an E-R diagram corresponds to a table in a relational database, and each line from the diagram corresponds to a foreign key.
Entity-Relationship design (E-R design) is an example of top-down design of databases. There are now sophisticated methods and tools available to pursue E-R design of databases in great detail. This chapter is an introductory chapter only, but it does contain enough information for the design of fairly straightforward databases.
Each table in the database describes an entity; it is the database equivalent of a noun. Employees, order items, departments and products are all examples of entities represented by a table in a database. The entities that you build into your database arise from the activities for which you will be using the database, whether that be tracking sales calls, maintaining employee information, or some other activity.
A relationship between entities is the database equivalent of a verb. An employee is associated with a department, or an office is located in a city. Relationships in a database may appear as foreign key relationships between tables, or may appear as separate tables themselves. We will see examples of each in this chapter.
The relationships in the database are an encoding of rules or practices governing the data in the table. If each department has one department head, then a single column can be built into the department table to hold the name of the department head. When these rules are built into the structure of the database, there is no provision for exceptions: there is nowhere to put a second department head, and duplicating the department entry would involve duplicating the department ID, which is the primary key. This is not allowed.
There are three kinds of relationships between tables:
