How to understand a way to get around MyISAM FK issue?

Good day everyone.

(I'm a vet finishing up my 2 year associates degree in web/software development this semester and have chosen to help a local non-profit to help me learn and help them function.)

I'm producing for them a volunteer database but they are limited to shared hosting and MyISAM unfortunately is a requirement.

To be frank, my brain is on the fritz with this problem. I've been taught off and on the last two years that basically ACID = SQL (well, you know, the goal of ACID should be used in any DB implementation.)

The following is a sample DB table I'm trying to create and use:

Table: log
pk: pid
fk: task.pid
fk: volunteer.pid

Well, I just learned tonight, that MyISAM doesn't allow foreign keys. I'm really kind of freaking out now heh. I don't even know how to tackle this and go from here. Is there any way at all possible I can reference the same volunteer for both log table and task table?

The goal is:

Administrator would login to the admin console.
Then click on a list of volunteers that have been populated on the screen.
Display all latest tasks completed by said volunteer.

Is this possible on MyISAM?

Any help would be greatly, greatly appreciated.

Solution

A key feature of the relational model is that tuples (rows) are related to each other by common value(s) stored in columns.

In that sense, MyISAM supports relationships between rows.

So, to answer your question, yes, you can have multiple rows that have the same value in a column.

So, get the list of volunteers...

SELECT v.pid, v.name FROM volunteer v ORDER BY v.name

pid  name
---  ------
43   Barney
24   Fred

To display "all the latest tasks" for a given volunteer, assuming that's stored in the log table...

SELECT t.id
     , t.task_name
     , l.log_date
  FROM task t
  JOIN log l 
    ON l.id = t.log_id
 WHERE l.volunteer_id = 24 
 ORDER BY l.log_date DESC

MyISAM does not support declarative constraints to enforce integrity between the tables.

With InnoDB, you can ensure that no values get put into the volunteer_id column of the log table, when a matching row doesn't existin in the volunteer table. That is, a row with a matching value in the id column of the volunteer table. If you attempted to do that with InnoDB tables with a FOREIGN KEY constraint, InnoDB (MySQL) would return an error.

What MyISAM lacks is this kind of enforcement of FOREIGN KEY constraints. MyISAM will let that INSERT/UPDATE be performed. MyISAM doesn't care if there's a matching row or not.

If you need to enforce integrity in the database with MyISAM, that becomes an application responsibility. The application needs to check if a volunteer_id value to be stored in the log table is valid... whether that refers to a row in volunteer table, and decide whether it should continue the operation or stop.

But aside from that difference, the SQL would be the same, whether it's InnoDB or MyISAM.