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 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.

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 ?

Thank you in advance for all the help

Diego

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.

> > The problem here is that tables B, C etc... do not have a primary Key.
>
> In your case, PatID IS the PK
>

Yes ! This is exactly what I meant, sorry for having not been clear
enough


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

Thank you for the advice, I will study the command
>

> 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 -

Yes, both the programs (FM and MySQL) should be on Windows,
initially,
but what if I bring mySQL on Linux ?

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

Filemaker will assign the new PatID to the new Patient inserted in the
DB,
MySQL should simply update the column and all the data consequently.
I understand that, having both the programs on the same platform, does
not matter who is pulling and who is pushing the data...but might you
show the basics of an ODBC connection starting from MySQL
perspective ?


> 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

Weirdly enough, I am an italian physician working temporarily
at the Mayo Clinic : )
I agree with you, security is critical here, and maybe I will be able
to hire someone
that will take care of that aspect, but first I would like to define
all the other things, focusing
on the structure and the relationship between the two versions of the
DB
by myself, to avoid problems in future.

Diego B wrote:
>>> The problem here is that tables B, C etc... do not have a primary Key.
<snip>
>
>> 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
<snip>


> Weirdly enough, I am an italian physician working temporarily
> at the Mayo Clinic : )

It is amazing what one can find when looking at posting headers and a
Google search or two...

On Mar 1, 10:20*pm, Michael Austin <maus...@firstdbasource.com> wrote:
> Diego B wrote:

>
> It is amazing what one can find when looking at posting headers and *a
> Google search or two...


Yes I thought something like that but random effect would have been
muuuch more exciting !

Keep well,

Diego