Re: [Perfparse-devel] More Database design decisions.

[Tim W. <tim...@gm...>]

This might help:
(from the MySQL documentation)
To make it easier to reload dump files for tables that have foreign
key relationships, mysqldump automatically includes a statement in the
dump output to set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1.This
avoids problems with tables having to be reloaded in a particular
order when the dump is reloaded. For earlier versions, you can disable
the variable manually within mysql when loading the dump file like
this:

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name
mysql> SET FOREIGN_KEY_CHECKS = 1;

This allows you to import the tables in any order if the dump file
contains tables that are not correctly ordered for foreign keys. It
also speeds up the import operation. FOREIGN_KEY_CHECKS is available
starting from MySQL 3.23.52 and 4.0.3.

So you can easily keep the reference....

Tim


On Fri, 10 Dec 2004 13:48:30 +0000, Ben Clewett <bcl...@pe...> wrote:
> More Database design decisions.
> 
> This is a problem which may effect many users, so I would really like to
> get it right with new schema.
> 
> We currently have a cyclic referential link in our tables.  Yves has
> reported that this makes PP data in MySQL impossible to dump and reload.
> 
> This reference is as follows on this ERD:
> 
>            <- 1. Last value recorded
>         +------------------------------+
>         |                              |
>         |                              |
> [Binary Data]                      [Metrics]
>        \|/                             |
>         |                              |
>         +------------------------------+
>              2. Metric details ->
> 
> The '1. Last Value Recorded' allows the binary report.  Showing for each
> metric, when and what the last reading was.  Useful information to have
> for current and future work.
> 
> The '2. Metric Details' link is used for Referential Integrity.
> Stopping the metric being deleted if there is data for the metric.
> Therefore avoiding hundreds of Mb of unreferenced data.  If this is not
> used, the user could end up with GB of orphaned data to which they
> cannot access or delete.
> 
> I can think of several possible solutions to this:
> 
> (i) Not worry about Referential Integrity, loose link 2.  Add the RI in
> programaticly and hope that our programs work.
> 
> (ii) Drop the Binary Report, loose link 1.
> 
> (iii) Hope that newer versions of MySQL have a dump / import program
> which works with cyclic referenced tables.
> 
> (iv) Program our own dump program which works.
> 
> I am tending towards option (iv)...
> 
> As I know there are people on this list who know many time more about
> databases than my self, I would really like to hear from you!
> 
> Regards,
> 
> Ben.
> 
> --
> Ben Clewett  bcl...@pe...
> PerfParse    http://www.perfparse.org
> PP FAQ       http://wiki.perfparse.org/tiki-list_faqs.php
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://productguide.itmanagersjournal.com/
> _______________________________________________
> Perfparse-devel mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perfparse-devel
>