1

I realize that this type of question has been asked several times before but as there are a plethora of different answers I wanted to find out which applied best to my situation.

I am designing an online journal for psychologists. I plan to have one table in the main DB to store all user (psychologists) information and then I'm unsure as to how to deal with the patient data (notes) the users want to enter. Should I:

  1. Have 1 table within the same DB that stores all users patients and their notes.
  2. Have a separate table for each user with all their patients together on that table.
  3. Have a separate DB for each user with a table for each patient in that DB.

In terms of data size I'm assuming about 1000 total users each with 300-400 patients and an average of 100 notes (this is several paragraphs of data, each note is the content of an entire counseling session) per patient.

I saw this question on SO DB design: one large DB for all customers or many small DBs and I just wasn't sure if it also applied to my situation. I also saw various references to using InnoDB with partitions to perhaps have all the patients and notes on one table. If this is recommended could you also include some sources or tutorials as the information I found on this was limited and a bit too complex for me.

Community
  • 1
  • 1
trendicoff
  • 13
  • 2

1 Answers1

0

You won't need anything like InnoDB partitions for such a relatively small data set. I would recommend using four InnoDB tables: 

1) psychologists: id (auto-increment, primary key), name, address, ...
2) patients: id (auto-increment, primary key), name, address ...
3) psych-patients: id (auto-increment, primary key), psychologist-id (foreign key reference to `psyschologists`.`id`, patient-id (foreign key reference to `patients`.`id), with a unique constraint spanning the columns `psychologist-i` and `patient-id`
4) notes: patient-id, psychologist-id, date, text

This allows you to separate your data cleanly. The third table is required so patients can switch doctors; if you included psychologist-id in the table patients, you would have to create a new record for that. (You wouldn't be able to simply update the ID because the links to the previous notes would be lost.)

Of course, you are dealing with medical data, so you need to do all you can to keep these private notes safe. That's something you should handle in the application layer rather than in the database layer, however. Using a different database for every psychologist may seem like a safer solution, but it probably isn't, plus it makes it difficult for your software to let patients switch psychologists while taking their notes with them.

Daan
  • 3,403
  • 23
  • 19
  • Thanks so much for the great explanation. If I could just ask 1 quick followup: Do you actually recommend InnoDB over MyISam for this or were you just repeating back what I had mentioned? – trendicoff May 02 '12 at 15:54
  • I'd definitely recommed InnoDB. It supports transactions, row-level locking and foreign-key relationships, which MyISAM doesn't support. [This page in the MySQL docs](http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html) explains the benefits of using InnoDB, and why it's now the default engine for MySQL. So yes, go with InnoDB if you can :) By the way, I'd appreciate it if you could upvote or accept my answer if it was useful to you :) – Daan May 02 '12 at 18:34
  • Thanks again for the help, sorry that I can only accept (don't have the rep to vote up yet). – trendicoff May 02 '12 at 20:34