Diego B wrote:
> Hi all,
>
> This is my first post here, and the questions I am going to ask are
> pretty basic for a MySQL user, so please excuse me in advance, first I
> performed a search, to find some useful posts, but I could not find
> anything.
>
> Here it is the starting situation: I have a clinical research DB done
> in Filemaker 9 (win XP).
> The Database uses a client/server structure entirely in Filemaker, and
> it is working great so far.
>
> For many different reasons I would like to have a copy of the database
> in MySQL, or better, I would like
> that the two versions of the database (filemaker and mysql) would
> interact each other, so that mySQL will be able to update itself
> through an ODBC connection with FM, let us say every 4 hours, every
> day.
>
> In order to create the mySQL DB, I made the following:
>
> 1) Exported all the tables from FM in excel fromat. I have a small
> program that converts each table from excel to mySQL apparently in an
> acceptable way (the program is "Intelligent converters, MySQL
> migration toolkit, vers 2.3"). Differently from the others I tested on
> windows, this program let me create a MySQL table from scratch
> directly converting from Excel. Eventually, I ended up with 6 tables.
>
> 2) The DB has the following structure: a main table ("patients") where
> demographic data and the primary key (ID) are listed. All the other
> tables (essentially referring to clinical and diagnostic examinations)
> are linked to the main table because they all have the ID key (now the
> foreign key) in a 1:many relatiponship, so that
>
> Table A Table B Table C
> Patients ID ----> Patients ID ----> Patients ID
>
> etc...
> The problem here is that tables B, C etc... do not have a primary Key.

In your case, PatID IS the PK

> In other words, the only key of the database is the patient ID, and it
> works as the primary key in the "patients" table and as the primary
> and foreign key in all the other tables. I know this is formally wrong
> and that every table should have its own primary key, but at the time
> I designed the DB I did not know enough about database design and had
> no idea about primary and foreigh key. Anyway, the DB works fine, and
> if I need to create a new primary key on the other tables, it would
> not be a big deal.

see ALTER TABLE statement in:
http://dev.mysql.com/doc/refman/5.0/...nstraints.html
(does not appear to work on MyISAM tables just yet:

from:
http://dev.mysql.com/doc/refman/5.0/...eign-keys.html
1.8.5.4. Foreign Keys

"In MySQL Server 3.23.44 and up, the InnoDB storage engine supports
checking of foreign key constraints, including CASCADE, ON DELETE, and
ON UPDATE. See Section 13.2.6.4, “FOREIGN KEY Constraints”.

For storage engines other than InnoDB, MySQL Server parses the FOREIGN
KEY syntax in CREATE TABLE statements, but does not use or store it. In
the future, the implementation will be extended to store this
information in the table specification file so that it may be retrieved
by mysqldump and ODBC. At a later stage, foreign key constraints will be
implemented for MyISAM tables as well. "


>
> Finally I converted all the tables in mySQL and each table. Here are
> the questions:
>
> 1) How can I modify the tables so that MySQL will consider the patient
> ID as the primary key on the "patients" table and the foreign key in
> the others ? In other words, How can I relate the different tables to
> the "patients" table using patients ID ?
>
> 2) Guessing, I have completed the database in MySQL, I will have two
> versions of the same DB, one in filemaker, and the other (the
> "mirror") in mySQL. Now, I would like that every patient is added to
> the FM database will be available in the MySQL database after a while,
> that is I would like to "pair" the two databases using an ODBC
> connection. ODBC is already enabled in the FM server, and with version
> 9, filemaker is able to "see" standard SQL tables. So I do notr know
> what is the best approach here: ask mysql to pull the data from FM, or
> ask FM to push the data to MySQL. I asked about this last solution in
> FM forums, but I did not get any clear answer. Maybe if MySQL acts as
> the protagonist, the pairing is easier, but I really do not know how
> to do that, might you point me in the right direction ?

Antagonist/protagonist will be whatever application you choose on the
appropriate platform. If they both reside on Windows (yuck)... then it
really does not matter as it will be a Windows app that would either
push or pull the data -

If you change or add PK information are you going to use the database or
the application to assign the new PK column?

>
> Thank you in advance for all the help
>
> Diego

One of the big picture things you have worry about is HIPPA - depending
of course on where you are located in the world - Mayo Clinic, Italy
etc... Having previously worked at several companies that dealt with
clinical data, the REQUIREMENT for the security of that data is
extremely high. Please hire outside help to ensure the security of this
data before we read/hear about it in the mainstream media. Given just a
bit of time, that stuff could be hacked relatively easily.