Relating Multiple Tables and Data Models

The heart of the database design is the data model. Models are the corner stone of design. System designers develop models to explore ideas and improve the understanding of the database design.

Relating Multiple Tables Each row of a table in a relational database schema contains data that describes exactly one entity. The column structure of the table separates in pieces of information the logical data unit of the entity or object. Thus, data about different entities is stored in different tables. Very often in order to answer to a more complicated query you have to combine two or more tables. The solutions to this are foreign keys. By using foreign keys we can relate the data in one table to the data in another. We remind you that a foreign key is a column or a set of columns that refer to a primary key in the same table or another table. Using Logical Data Models Like other models, data models can be used for a variety of purposes, from high-level conceptual models to physical data models. From the point of view of an object-oriented developer data modeling is conceptually similar to class modeling. With data modeling we can identify entity types just as with class modeling we identify classes. Attributes are assigned to entity types just as we would assign attributes to classes. There are relationships between entities, similar to the relationships between classes. Additionally inheritance, composition, and aggregation are all applicable concepts in data modeling. Data models focus solely on data and lead us to explore data issues. Thus, from the point of view of data model developers data modeling must get the data "right" as opposed to object oriented modeling (ORM) developers who explore both the behavior and data aspects. Thus, we will choose a logical data model (LDM) that should create a database of high performance as efficiently as possible and easy to maintain. This model must be based on particular amount of data that it is not repetitive. To eliminate duplication and enhance our tables data maintenance, we would create a table of possible values and will use a key to refer to these values. Thus, if the value changes names, the change will take place in the master table once. The reference will remain the same to this table throughout the foreign keys of the other tables. Let us suppose for example that we are responsible for maintaining a database of employees and the departments in which they work. If 120 of these employees are in the same department, called "Development" this department name would appear 120 times in the table of employees. If the person in charge of this department decides to change its name to "R&D" we must change 120 records to reflect the new name of department.  If the database was designed under the model we mentioned earlier so that department names appeared in one separate table and just the department ID number was stored with the employee record in the employees table, we would only have to change one record in order to change the name and not 120.