User Roles and Foreign Keys - How best to handle this?

I have three roles in my database - careplanner, coordinator and administrator. They will have access to different areas of the application, but also have different roles in the organization itself. I wanted to keep them all in a single ‘users’ table with a ‘role’ field. The rest of the table fields are first_name, last_name, email, password. But this creates an issue with my foreign keys because in some of the other tables I have to keep track of a careplanner (careplanner_id instead of user_id), and a coordinator (coordinator_id instead of user_id), both of which would be a foreign key to the ‘users’ table. This doesn’t work because the field names don’t match up.

The only way I can think of to solve this problem would be to have a separate ‘careplanners’ table, ‘coordinators’ table and ‘administrators’ table, all of which would contain the login credentials to access different areas of the application. Is there another way to get around this? The ‘administrators’ table would only have one or two records in it.

but… but… the names don’t have to match

CREATE TABLE other ( foo INTEGER , bar VARCHAR(9) , careplanner_id INTEGER FOREIGN KEY REFERENCES users ( user_id ) , coordinator_id INTEGER FOREIGN KEY REFERENCES users ( user_id ) );

Well that’s what I thought, but I’m trying to add the foreign constraint through phpmyadmin, and it’s not allowing it. Shall I post the schemas?

Here is the ‘assessments’ table schema (I had already added one foreign key constraint for ‘client_id’ -

--
-- Table structure for table `assessments`
--

CREATE TABLE IF NOT EXISTS `assessments` (
  `id` int(10) unsigned NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `careplanner_id` smallint(5) unsigned NOT NULL,
  `assessment_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `caseworker` varchar(3) DEFAULT NULL,
  `caseworker_name` varchar(50) DEFAULT NULL,
  `other_caseworker_name` varchar(50) DEFAULT NULL,
  `caseworker_contact` varchar(3) DEFAULT NULL,
  `caseworker_contact_name` varchar(50) DEFAULT NULL,
  `caseworker_phone` varchar(15) DEFAULT NULL,
  `caseworker_email` varchar(100) DEFAULT NULL,
  `other_help` varchar(3) DEFAULT NULL,
  `help_notes` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `assessments`
--
ALTER TABLE `assessments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `client_id` (`client_id`),
  ADD KEY `careplanner_id` (`careplanner_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `assessments`
--
ALTER TABLE `assessments`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `assessments`
--
ALTER TABLE `assessments`
  ADD CONSTRAINT `assessments_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

and here is my ‘users’ table schema -

-
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` smallint(5) unsigned NOT NULL,
  `church_id` smallint(4) unsigned DEFAULT NULL,
  `role` varchar(20) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `church_id` (`church_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT;

do you have a separate clients table? shouldn’t that be your users table?

change this –

FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)

to this –

FOREIGN KEY (`client_id`) REFERENCES `users` (`id`)

The users are the careplanners, coordinators and administrators that need to be able to log in to various parts of the application. The clients are the people these users are helping. The clients table contains all the basic personal and contact information that a coordinator collects during the initial intake meeting with the client.

the fact that there are two tables (clients and users) was not well explained initially

there is, nevertheless, no good rerason that i can see for splitting up the users table into three

My apologies for not being totally clear.

I will use the single ‘users’ table - I like that way better anyhow.

I am still unable to set the foreign key constraint, though.

This is what I am getting from phpmyadmin -

SQL query:
    
ALTER TABLE `assessments` ADD  FOREIGN KEY (`careplanner_id`) REFERENCES `rc_crm`.`users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

    MySQL said: 

#1215 - Cannot add foreign key constraint

this means either there are rows in assessments that reference a non-existing user id value, or the datatypes of the two columns are not identical

The tables are still empty. I am just in the process of building the database. I checked the datatypes before, but I will check them again. Thank you.

Problem Solved! Thanks again for your help.

A couple of issues were caused by mismatched datatypes - where I had gone in and changed my mind about the field datatype and forgot to carry it through to other tables.

But the issue on most of them was not setting the fields to NULL when I wanted the action for “ON DELETE” to be “SET NULL” instead of “CASCADE” or “RESTRICT”.

This is my first project with a database this large (25 tables and 400 fields), and I definitely learned a lot from it.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.