Entry

Database: Data structure: Relation: Type: Main: Which are main database relation types? [one/many]

Jul 6th, 2008 13:34
Knud van Eeden, dman,

----------------------------------------------------------------------
--- Knud van Eeden --- 11 September 2020 - 11:41 pm ------------------
Database: Data structure: Relation: Type: Main: Which are main 
database relation types? [one/many]
---
   +------+-------------+---------------+
   |      |             |               |
   |      | ONE         |  MANY         |
   |      |             |               |
   +------+-------------+---------------+
   |      |             |               |
   | ONE  | one to one  |  one to many  |
   |      |             |               |
   +------+-------------+---------------+
   |      |             |               |
   | MANY | many to one |  many to many |
   |      |             |               |
   +------+-------------+---------------+
===
Here totally 2 x 2 = 4 possibilities:
===
1. One to one
---
one-to-one relationship:
---
-in a one to one relation for each primary key there is only 1 foreign
 key in the other table.
---
-there is exactly one record in table 1 that corresponds to exactly one
 record in table 2 (and vice-versa)
  table 1         table 2
    [A] ----->     [v]
    [B] ----->     [w]
    [C] ----->     [x]
    [D] ----->     [y]
    ...            ...
===
2. One to many
---
one-to-many relationship:
---
-in a one to many relation for each primary key there is more than 1
 foreign key in the other table.
---
-each record in table 1 may have many linked records in table 2 (but
 each record in table 2 may have only one corresponding record in
 table 1)
  table 1         table 2
    [A] --+-->     [v]
    [B]   |        [w]
    [C]   +-->     [x]
    [D]   +-->     [y]
    ...            ...
===
3. Many to one
many-to-one relationship:
---
-in a many to one relation for each foreign key there is more than 1
 primary key in the other table.
---
-each record in table 2 may have many linked records in table 1 (but
 each record in table 1 may have only one corresponding record in
 table 2)
  table 1         table 2
    [A] -+--->     [v]
    [B]  |         [w]
    [C] -+         [x]
    [D] -+         [y]
    ...            ...
===
4. Many to many
---
-in a many to many relation for each primary key there is more than 1
foreign key, and for each foreign key there is more than 1 primary key
in the tables.
---
Many-to-many relationships: each record in table 1 may have
many linked records in table 2 and vice-versa.
  table 1         table 2
    [A] <-+-->     [v]
    [B]   +        [w]
    [C]   +-->     [x]
    [D] <-+        [y]
    ...            ...
---
Many-to-many relationships are more complex than the other three
relationships, because these relationships require an
additional table in the database.
Rather than relying on a single foreign key column,
you will need a relationship table.
Each row of a relationship table expresses a relationship with foreign
keys, but has no other data.
E.g.
The additional table 1-2 connects keys
from table1 with keys from table 2.
  [table 1]            [table 1-2]          [table 2]
  -----------------    --------------       ------------------
  | id1 | column1 |    | id1  | id2 |       | id2  | column2 |
  -----------------    --------------       ------------------
    ... <--------------- ...    ... --------> ...
    ... <--------------- ...    ... --------> ...
    ...                  ...    ...           ...
    ...                  ...    ...           ...
  -----------------    --------------       ------------------
===
Book: see also:
---
[book: author: Hibbs, Curt / Tate, Bruce A. - title: Ruby on Rails: Up 
and Running - publisher: O'Reilly - year: 2006 - ISBN 978-0-59-610132-
9 - 'Many-to-many relationships']
===
Internet: see also:
---
Database: Link: Overview: Can you give me an overview of links?
http://www.faqts.com/knowledge_base/view.phtml/aid/33838/fid/132
----------------------------------------------------------------------