Relational Databases: Difference between revisions
Line 18: | Line 18: | ||
1:N relationships are also typically set up by foreign key relationships in the database. In the example above, a one-to-many relationship is "a manager has many employees". This relationship is implemented in the database by having each employee row contain the manager's primary key, as a foreign key. Thus, the employees are pointing back to their manager. This may seem backwards if we want to get from the manager to the employees. It works, however, because the database doesn't care, it is a flat structure without a sense of direction. You can construct queries that get from the manager to employees. | 1:N relationships are also typically set up by foreign key relationships in the database. In the example above, a one-to-many relationship is "a manager has many employees". This relationship is implemented in the database by having each employee row contain the manager's primary key, as a foreign key. Thus, the employees are pointing back to their manager. This may seem backwards if we want to get from the manager to the employees. It works, however, because the database doesn't care, it is a flat structure without a sense of direction. You can construct queries that get from the manager to employees. | ||
==Many-to-Many (M:N) Relationships== | |||
[[File: Multiplicity_Many_to_Many.png]] | |||
M:N relationships are typically set up by an association table in the database. An association table contains foreign keys to two other tables. | |||
=TODO= | =TODO= |
Revision as of 03:54, 16 October 2018
Internal
Multiplicity (Cardinality)
The cardinality specifies how many instances of data can participate in a relationship. There are three different types of cardinality:
One-to-One (1:1) Relationships
In a one-to-one relationship, each constituent can have at most one relationship with the other constituent. 1:1 relationships are typically set up by a foreign key relationship in the database. The foreign key column can be set in either table. In practice, 1:1 relationships will be combined into a single table, rather than having a relationship between two separate data objects, so 1:1 relationships are not very common.
One-to-Many (1:N) Relationships
1:N relationships are also typically set up by foreign key relationships in the database. In the example above, a one-to-many relationship is "a manager has many employees". This relationship is implemented in the database by having each employee row contain the manager's primary key, as a foreign key. Thus, the employees are pointing back to their manager. This may seem backwards if we want to get from the manager to the employees. It works, however, because the database doesn't care, it is a flat structure without a sense of direction. You can construct queries that get from the manager to employees.
Many-to-Many (M:N) Relationships
M:N relationships are typically set up by an association table in the database. An association table contains foreign keys to two other tables.
TODO
Object IDs
When persisting objects in a relational database, it is generally a good idea to have one field in the object that uniquely identifies the object.