[Perfparse-devel] More Database design decisions.

[Ben C. <bcl...@pe...>]

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