Hey Monks!

Sorry for this not really beeing direcly Perl again, but I know you are so bright here (been here a while now), and this "problem" I have now, I really need feedback on.

Now, Im almost done with my rather complex but nice application I've made in Perl soon to be released for everybody! Basicly, I have this "core" script, that calls others as "plugins" as wanted by user. Now, this has to do with files, and I use a database. In the "core" I save the full path to a file as a key (which can change later, but anyway). Now, the "problem" I have is the communication between the core and the scripts (when it comes to the database talk that is). As it is now, every plugin also saves the full path to the file (just so it was easier and faster developing and testing), but I dont really like the redundancy this is creating, and I really wanna optimize this! Since the "core" also has an Autoincremet column, I was thinking making this something like a foreign key, so there is only one "full path" in the core, and the plugins gets the uniqe increment value, and uses that to lookup in their own table. This really feels like it should be faster than doing a core.fullpath = plugin.fullpath check, and more the way of how it should be done.

However, this means that then I have to do more queries, but I suppose its waaay better doing more queries than having a bad table structure.

Ok, to illustrate:

{Way #1} (as I have it atm)
core table:
id, fullpath, modtime, size, attributes....

plugin table:
fullpath, title, album... (if its a mp3 plugin).

If I now wanna find files with title "Perlmonks dancing", its pretty easy and fast.
SELECT fullpath FROM plugin WHERE title LIKE ("%Perkmonks dancing%");

If I wanna find files with the title "Perlmonks dancing" AND size < 5000 bytes, its abit more (and more to the point, not really great to speed!):
SELECT fullpath FROM core,plugin WHERE title LIKE ("%Perkmonks dancing%") AND size < 5000 AND core.fullpath = plugin.fullpath;

This core.fullpath = plugin.fullpath can't be good whatsoever!

So, this is why I wanted to restructure (+ I dont like the redundancy, really) But, the question is, how to do this the best way?

{Way #2}
core table:
id, fullpath, modtime, size, attributes....

plugin table:
id_ref, title, album... , where id_ref points to core.id.

Now, here its worse with the first example, since I dont have the fullpath stored in the plugin table, but should be faster anyway?
SELECT fullpath FROM core,plugin WHERE title LIKE ("%Perkmonks dancing%") AND plugin.id_ref = core.id;

Second example gives:
SELECT fullpath FROM core,plugin WHERE title LIKE ("%Perkmonks dancing%") AND plugin.id_ref = core.id AND size < 5000; (so not much of a change)

Would really like comments on this, since this is quite essential. I mean, I wanna do things best as soon as possible! :) Specially, since the idea is that one should only have to care about doing plugins, and not the boring work the core does! Problem also arrises when one run my core script, but uses a plugin. If I for some reason need to know the fullpath to a file, I have to ask the plugin about it (so I get the full path to the file in question). Using Way #1, plugin can just send me the info (since it has the column fullpath itself). Using Way#2, plugin has to do little more.

Thanks,
Ace

Edited by planetscape - added readmore tags

In reply to Relation between tables by Ace128

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.