> convert our FM
> system to a normalized data structure will take at least 9 months.
and what is the point about "normalized structure", that can justify 9
or 4 monthes of specialized work ?
Micah - 21 Aug 2008 00:31 GMT
>> convert our FM
>> system to a normalized data structure will take at least 9 months.
>
> and what is the point about "normalized structure", that can justify 9
> or 4 monthes of specialized work ?
I don't have the desire to explain to you why normalization is
important. I suggest reading up on it.
Micah
> Because I want to work with normalized data in PHP. To convert our FM
> system to a normalized data structure will take at least 9 months.
> I
> can normalize the data and put a new front end on it in 4 months using
> MySQL while still presenting a denormalized view to FM for integration.
Frankly, I can't really envision a scenario where taking an FM
database, converting it to MySQL, and then exposing the tables back to
FM from MySQL as denormalized views would be preferable or indeed,
faster than just cleaning cleaning up the structure in filemaker.
> Can anyone tell me the steps necessary on the FM side to accommodate this?
You really can't effectively -convert- a solution of more than trivial
complexity that uses local fm tables, to one that uses external odbc
views (from say mysql).
The steps to do it are simple enough:
"All you have to do is"...
define the schema in mysql, export the data, set up your 'denormalized
views for filemaker' in mysql, setup an OBDC dsn, and then reference
it in filemaker (file -> manage -> external data sources), and then go
through your filemaker relationship graph, and change the data source
for each table occurrence from its local table reference to the
external counterpart.
The problem is that not everything works through an ODBC link that
works on a local table, and there are nuances to everything from
record numbering, record locking, auto-enter, calculation fields,
storage (global fields are really per user, for example), value list
issues, not to mention general performance issues for queries,
relationships, etc, etc.
The odds of just taking a reasonably complex file and converting it
like this and having it actually work well enough to be usable are
next to none.
It would take someone who is a filemaker expert to do it with any
chance of quick success, and even then I would expect it to take
considerable debugging and testing to ensure it worked correctly
afterwards. And no disrespect, but based on the fact that you are
asking basic questions on how it might be done suggests that you don't
really appreciate the task you are approaching here.
If you've converted a solution from local filemaker tables to external
filemaker tables, you should have some appreciation for the sort of
things that can go wrong. Convertering to ODBC would easily be an
order of magnitude harder.
I'm not saying you won't be successful, especially if you are willing
to hammer away at it, but it will probably take much longer than
you've anticipated.
I'd say you be better off rebuilding the solution from scratch using
the existing one as a functional prototype. That would probably be
true whether you wanted to have the data in filemaker or in an
external database.
-regards,
Dave
Micah - 21 Aug 2008 18:17 GMT
>> Because I want to work with normalized data in PHP. To convert our FM
>> system to a normalized data structure will take at least 9 months.
[quoted text clipped - 10 lines]
> -regards,
> Dave
I wanted to avoid an immediate overhaul of the FM system because a
rewrite would take years. I thought if I can present the same table
name, relationships, and data it would integrate with the current
system, but I realize that is not possible now. I am looking into other
options.
Thanks,
Micah
Chris Brown - 22 Aug 2008 00:37 GMT
>>> Because I want to work with normalized data in PHP. To convert our FM
>>> system to a normalized data structure will take at least 9 months.
[quoted text clipped - 18 lines]
> Thanks,
> Micah
I agree with Dave, rebuild the FM db from scratch. Think of it as a
template for the input/data display requirements.
The 'de-normalised' bit: the 'de-noramalized' (i.e. the non-normailzed)
FM fields present on the layouts, can be be (generally) redefined to
use hopped relationships equivalents in the normalized tables... THere
goes the old FM structure...
Just out of curiosity, how may tables and fields are you dealing with?
Micah - 22 Aug 2008 00:56 GMT
>>>> Because I want to work with normalized data in PHP. To convert our FM
>>>> system to a normalized data structure will take at least 9 months.
[quoted text clipped - 28 lines]
>
> Just out of curiosity, how may tables and fields are you dealing with?
Over 100 tables and 1000 fields.
David Simpson - 25 Aug 2008 23:52 GMT
If you have this many tables/fields, it seems like FmPro Migrator might be
helpful. It will also convert the repeating fields if you have them in your
FileMaker database.

Signature
David Simpson
www.fmpromigrator.com
>>>>
>>>>> Because I want to work with normalized data in PHP. To convert our FM
[quoted text clipped - 32 lines]
>
> Over 100 tables and 1000 fields.