User Tools

Site Tools


db:relations

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

db:relations [2013/03/16 17:40] (current)
Line 1: Line 1:
 +===== Relation types exemplified in Doctrine YAML Files =====
 +12.01.2009
 +
 +=== 1. ONE TO ONE ===
 +
 +
 +Case: two tables, **sn_user** and **sn_cnp**. **One user** has **one cnp** (social security code). Deleting an user will remove also the related cnp record.
 +
 +<​code>​
 +SnUser:
 +    columns:
 +        name: { type: string(100) }
 +        age: { type: integer(1) }
 +    options:
 +        collate: utf8_unicode_ci
 +        charset: utf8
 +    relations:
 +        SnCnp: { local: id, foreign: user_id }
 +
 +SnCnp:
 +    columns:
 +        cnp: string(13)
 +        user_id: integer
 +    relations:
 +        SnUser:
 +            local: user_id
 +            foreign: id
 +            onDelete: CASCADE
 +            foreignType:​ one
 +</​code>​
 +
 +=== 2. ONE TO MANY ===
 +
 +
 +Case: **sn_user** and **sn_phone_numbers**. **One user** may have **multiple phonenumbers**.\\ ​
 +Deleting an user will remove also all related phonenumbers records.
 +
 +<​code>​
 +
 +SnUser:
 +    columns:
 +        name: { type: string(100) }
 +        age: { type: integer(1) }
 +    options:
 +        collate: utf8_unicode_ci
 +        charset: utf8
 +    relations:
 + ...
 +        RelPhonenumbers:​
 +            type: many
 +            class: SnPhoneNumbers
 +            local: id
 +            foreign: user_id
 +
 +SnPhoneNumbers:​
 +    columns:
 +        user_id: integer
 +        phone_number:​ string(20)
 +    relations:
 +        SnUser:
 +            local: user_id
 +            foreign: id
 +            onDelete: CASCADE
 +</​code> ​
 +
 +
 +=== 3. MANY TO ONE ===
 +
 +
 +
 +Case: **sn_user** and **sn_level** tables. **One user** can have only **one level**, but **one level** can be related with **many users**.\\ ​
 +A level record cannot be deleted if it's related to at least one user record.
 +
 +<​code>​
 +SnUser:
 +    columns:
 + ...        ​
 + level_id: integer
 +    relations:
 + ...
 +        RelLevel:
 +            class: SnLevel
 +            local: level_id
 +            foreign: id
 +            onDelete: RESTRICT
 +
 +SnLevel:
 +    columns:
 +        level: integer
 +    relations:
 +        SnUser:
 +            local: id
 +            foreign: level_id
 +</​code>​
 +
 +
 +=== 4. MANY TO MANY ===
 +
 +
 +Case: **sn_user** and **sn_city** tables (for relation a supplementary table will be created **sn_user_city**). **One user** can have **one or more cities**, **one city** can be related to **one or more users** (or none).\\ ​
 +A city cannot be deleted if it's already linked to an user, deleting an user will delete also the relation with the city.
 +
 +<​code>​
 +SnUser:
 +   ​relations:​
 +        RelCityU:
 +            class: ​             SnCity
 +            foreignAlias: ​      ​RelUserC
 +            refClass: ​          ​SnUserCity
 +            local: ​             user_id
 +            foreign: ​           city_id
 +
 +SnCity:
 +    columns:
 +        name: string(10)
 +    relations:
 +        RelUserC:
 +            class: ​             SnUser
 +            foreignAlias: ​      ​RelCityU
 +            refClass: ​          ​SnUserCity
 +            local: ​             city_id
 +            foreign: ​           user_id
 +
 +SnUserCity:
 +    columns:
 +        user_id: integer
 +        city_id: integer
 +    relations:
 +        SnUser: { onDelete: CASCADE, local: user_id, foreign: id }
 +        SnCity: { onDelete: RESTRICT, local: city_id, foreign: id }
 +</​code>​
  
db/relations.txt ยท Last modified: 2013/03/16 17:40 (external edit)