How to write associated linking for a model?

Question

Let's say I have a "users" table with a field called "user_type", for simple explanation if user_type is "C" this user is a clinic's owner, if user_type is "D" this user is a dentist.

I would like dentists in this system belong to clinics which he/she associates to.

My questions are...

1. Do I need clinics and dentists database tables???

2. If I didn't need those 2 tables then how many models do I need, just a User model or Clinic and Dentist models as well???

3. If all I need is just a User model, please hint me how do I write the User model and associated linking ($belongsTo, $hasMany)???

P.S. Clinic's owners and dentists are users of the system.

Please advise me. Thanks.

---

More about the system

This system is intended for clinic's owners as the paid customers of the system. The system will allow them to advertise the job offers for dentists and assistants.

The dentists and assistants can register themselves to the system after they get hired by the clinic's owner, the clinic's owner will become their employer and keep records of how they work in the system. If they quit the clinic also update his/her employed status.

The records that each clinic keep can be seen by other clinic.

It's actually like employer and employee model, but because of online system so I thought of User model.

Should I decide the system like, any user just register first, so he/she become a user of the system, after that they need to fill in more information whether they are employer (clinic/s owner) or employee (dentists or assistants), these information are kept in seperate tables such as clinics, dentists, assistants, and there are finally the models in CakePHP???

Thanks

Answer 1

I'll try to answer this question.

Separate Model for 'Clinic'

If you want to store information about a clinic (e.g. address, name etc) in your system, then you should have a database table to store that information and a Model to use that information.

Separate Model for 'Dentists' and 'Clinic owners'

Regarding separate database-tables for 'Dentists' and 'Clinic owners' You mention that both 'Dentists' and 'Clinic owners' are users of the system (and should probably both be able to log in). Also, it is likely that a 'Clinic owner' is also a 'Dentist'.

Because of this, I think you are safe to use a 'user type' to indicate if a person is (also) a Clinic Owner.

Naming of database tables

Regarding the naming of the database-tables.

Although both Dentists and Clinic owners are both 'Users' of the system, you may ask yourself if 'users' is the best name to describe both.

If Clinic owners are also Dentists, I would suggest to name your table 'dentists' as it better describes what is 'in it'. It is not a problem to use a different name for your 'users' table. It's still possible to use the table to log in as long as you specify the right model in the Auth component via the userModel option. For example:

public $components = array(
    'Auth' => array(
        'authenticate' => array(
            'Form' => array(
                'userModel' => 'Dentist',
                // other settings for 'Form' authentication
            )
        )
    )
);

See this part of the documentation: Configuring Authentication handlers

Example for database-tables and Models

To summarise your situation, your database should look something like this;

CREATE TABLE dentists
(
  id            int(4)      NOT NULL AUTO_INCREMENT,
  username      varchar(50) NOT NULL,
  password      char(128)   NOT NULL, -- SHA512 hash (128 characters)
  user_type     char(1)     NOT NULL DEFAULT 'D',
  name          varchar(75),
  address       varchar(75),
  email         varchar(75) NOT NULL,

  -- etc..

  -- primary key for CakePHP
  PRIMARY KEY (id),

  -- make sure usernames are unique
  UNIQUE KEY unique_username (username)
);


CREATE TABLE clinics
(
  id            int(4)      NOT NULL AUTO_INCREMENT,

  -- Note: NOT named according to 'CakePHP' conventions
  --       as 'owner_id' is probably clearer than 'dentist_id'
  owner_id      int(4)      NOT NULL,
  name          varchar(75),
  address       varchar(75),

  -- etc..

  PRIMARY KEY (id)
);

The models;

class Dentist extends AppModel {
    public $hasOne = array(
        'Clinic' => array(
            'className'    => 'Clinic',

            /**
             * because we're not following CakePHP
             * conventions here.
             *
             * This should be 'dentist_id' according to CakePHP conventions
             * but 'owner_id' is probably clearer in this case
             */
            'foreignKey'   => 'owner_id',
            'dependent'    => true
        ),
    );
}


class Clinic extends AppModel {
    public $belongsTo = array(
        'Dentist' => array(
            'className'    => 'Dentist',
            'foreignKey'   => 'owner_id',

            /**
             * Optionally
             */
            'conditions'   => array('Dentist.user_type' => 'C'),
        ),
    );
}

note In my design, a clinic can only have a single owner, but a Dentist can own multiple clinics. If a clinic can have multiple owners, a HasAndBelongsToMany relation should be used

More separation of 'Dentists' and 'Clinic Owners'

If desired, you can always set up another Model for 'Clinic Owners' (ClinicOwners), connected to the same database table as 'Dentists' and with a default condition of (user_type = 'C'). If you need more information on this, I'll add an example

update Some additional hints, based on additional information provided.

Deciding if Dentists, Assistants and ClinicOwners should have their own database-table should be based on their usage. If all are 'basically' equal, apart from their permissions, then it's possible to store them in the same database-table.

Even if they are in the same database-table, it is possible (for convenience) to create different models that use the same database-table, for example:

class Assistants extends AppModel {

    public $useTable = 'dentists'; // or 'users' if you decide on that

    /**
     * This model automatically filters results
     * to show only records with user_type 'A' (assistant)
     *
     * {@inheritdoc}
     *
     * @param array $queryData
     *
     * @return mixed
     */
    public function beforeFind(array $queryData)
    {
        if (!empty($queryData['conditions'])) {
            $queryData['conditions'] = array(
                'AND' => array(
                    $this->alias . '.user_type' => 'A',
                    $queryData['conditions'],
                )
            );
        } else {
            $queryData['conditions'] = array(
                $this->alias . '.user_type' => 'A'
            );
        }

        return $queryData;
    }
}

Permissions

Because permissions will be based on the user_type, it's worth considering to rename 'user_type' to 'role' (or similar). You'll probably be needing ACL (Access Control Lists) to define exact permissions per 'type of user', i.e. the users 'role'. You might want to create a 'roles' database-table for that as well (and a Role model)

Fully explaining ACL is quite difficult in a few lines, so I suggest to read the documentation on this subject;

Access Control Lists

And the examples:

Simple Acl controlled Application

Simple Acl controlled Application - part 2