 |
 |
 |
Modeling: User's Guide |  |
 |
|
|
|
|
Modeling: User's Guide | |
|
|
Modeling many-to-many relationships in a relational database schema usually
involves designing a correlation table, a table which holds the
informations about the objects in the two tables, Person and
Address. Each line in the correlation table is basically a tuple of
two elements, one from Address and one from Person, so that we
can tell that if person_1 and address_2 appear in a single row
of the correlation table, we know that those two objects are related to each
other.
Now let's integrate the correlation table into our model:
+--------+ 0,1 0,* +----------+ 0,* 0,1 +---------+
| Person |<-person---pAs->>| PersAddr |<<-pAs------address-->| Address |
|--------| |----------| |---------|
| name | | FKperson | | street |
+--------+ | FKaddress| | town |
+----------+ +---------+
where:
personAddresses are abbreviated with
pAs
To fully understand what's going on here, let's look at the tables themselves. Suppose tables Address and Person have 3 rows each:
SELECT * FROM Person; id | name --- +------ 1 | p1 2 | p2 3 | p3 SELECT * FROM Address; id | street | town ----+-----------+------ 1 | street a1 | 2 | street a2 | 3 | street a3 |
Then, for the following situation:
p1's addresses are [a1, a2]
p2's addresses are [a1]
p3's addresses are [a1, a3]
The correlation table looks like:
SELECT * FROM Person_Address; id | fk_person | fk_address ----+-----------+------------ 1 | 1 | 1 # p1 <--> a1 2 | 1 | 2 # p1 <--> a2 3 | 2 | 1 # p2 <--> a1 4 | 3 | 3 # p3 <--> a3 5 | 3 | 1 # p3 <--> a1
|
|
|
Modeling: User's Guide | |
|
|