Connecting the dots…

I am currently using sfGuardUser table from the sfDoctrineGuardPlugin to link up to a messages table which stores the user id of sender and receiver. To ensure that the data schema is properly normalised I need to make sure that both the user id are actually foreign keys to the sfGuardUser table. Here are the schemas to get a better under standing of what I am talking about:

sfGuardUser:
  actAs: [Timestampable]
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    username:
      type: string(128)
      notnull: true
      unique: true
    algorithm:
      type: string(128)
      default: sha1
      notnull: true
    salt: string(128)
    password: string(128)
    is_active:
      type: boolean
      default: 1
    is_super_admin:
      type: boolean
      default: false
    last_login:
      type: timestamp
  indexes:
    is_active_idx:
      fields: [is_active]
  relations:
    groups:
      class: sfGuardGroup
      local: user_id
      foreign: group_id
      refClass: sfGuardUserGroup
      foreignAlias: Users
    permissions:
      class: sfGuardPermission
      local: user_id
      foreign: permission_id
      refClass: sfGuardUserPermission
      foreignAlias: Users

Message:
  actAs:
    Timestampable: ~
  columns:
    user_from_id:                  { type: integer(4), notnull: true }
    user_to_id:                    { type: integer(4), notnull: true }
    date_sent:                     { type: timestamp }
    date_read:                     { type: timestamp }
    alertSent:                     { type: boolean, default: false, notnull: true }
    subject:                       { type: string(255), notnull: true }
    body:                          { type: string(255), notnull: true }
  relations:
    Sent:
      class: sfGuardUser
      local: user_from_id
      foreign: id
      type: one
    Recv:
      class: sfGuardUser
      local: user_to_id
      foreign: id
      type: one

A couple of things to remember on how I got it working

• The name of the relationship has to be unique, if the same one is used the last one will be used
• You need to use class property to tell it the name of the Model class to use
• Have to 100% ensure that the datatype and size are the same otherwise the ALTER... sqls generated won’t work. I had issues of using integer(4) and integer and it took me 10mins of scratching my head to wonder what was wrong

Hope this helps someone with a similar problem.