Identifying Database Table RelationshipsOne of the huge advantages of a relational database is that, once you have your data held in clearly defined, compact tables, you can connect or relate the data held in different tables. There are three types of relationships between the data you are likely to encounter at this stage in the design: one-to-one, one-to-many, and many-to-many. To be able to identify these relationships, you need to examine the data and have an understanding of what business rules apply to the data and tables. If you're not sure, it can be helpful to meet with someone who does have a thorough knowledge of the data. Show
When analyzing table relationships, you need to look at the relationship from both sides (sounds a little like marriage counseling, doesn't it?). When creating table relationships you always work with two tables at a time. One table is called the primary or parent table and the other is the related or child table. One-To-One RelationshipA one-to-one (1:1) relationship means that each record in Table A relates to one, and only one, record in Table B, and each record in Table B relates to one, and only one, record in Table A. Look at the following example of tables from a company's Employees database:
Above, tables with a one-to-one relationship from a database of information about employees Each record in the Personal table is about one employee. That record relates to one, and only one, record in the Payroll table. Each record in the Payroll table relates to one, and only one, record in the Personal table. (This is what looking at it from both directions means.) In a one-to-one relationship, either table can be considered to be the primary or parent table. One-To-Many RelationshipA one-to-many (1:N) relationship means a record in Table A can relate to zero, one, or many records in Table B. Many records in Table B can relate to one record in Table A. The potential relationship is what's important; for a single record in Table A, there might be no related records in Table B, or there might be only one related record, but there could be many. Look at the following tables about a company's Customers and Orders. CUSTOMERS
Above, tables with data about customers and orders that have a one-to-many relationship The Customers table holds a unique record for each customer. Each customer can (and, we hope, does) place many orders. Many records in the Orders table can relate to only one record in the Customers table. This is a one-to-many relationship (1:N) between the Customers table and the Orders table. In a one-to-many relationship, the table on the one side of the relationship is the primary table and the table on the many side is the related table. A one-to-many relationship is the most common relationship found between tables in a relational database. The following is an example of a form used in a database to display the data from two tables with a one-to-many relationship. A form displaying data from tables with a one-to-many relationship Many-To-Many RelationshipExamine the sample data below. These tables hold data about employees and the projects to which they are assigned. Each project can involve more than one employee and each employee can be working on more than one project (the "do more with less" thing). This constitutes a many-to-many (N:N) relationship. EMPLOYEES
Above, tables with a many-to-many relationship Most RDBMSs do not support many-to-many relationships. You'll see how to manage this type of database relationship in the Many-To-Many Relationships article. Which field is always on the one side of one to many relationships between two tables?In a one-to-many relationship between two tables, the foreign key field is the field in the "many" table that links the table to the primary key field in the" one" table.
What shows the records on the many side of a oneThe linking field on the "many" side of a one-to-many relationship is called the foreign key field. A subdatasheet shows the records on the "many" side of a one-to-many relationship.
What do two tables use to link related records from each table?A table relationship works by matching data in key fields — often a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table.
In which situation would using a one to one relationship be helpful?One-to-one relationships are frequently used to indicate critical relationships so you can get the data you need to run your business. A one-to-one relationship is a link between the information in two tables, where each record in each table only appears once.
|