I need some sage advice. I have built a generic web database system. It now can handle relationships, but it's a major cludge, in my opinion. I have a number of issues to deal with here - user interface - how to make relationships easy to create with table structure, add, and just see and - focus on SQL or focus on function? My cludge, instead of focusing on the SQL (creating foreign keys, creating the WHERE clauses on the fly) have actually created the functions of the relationships in perl, which works, but I have a nagging suspicion that it's not the best way to do it.

What I've done is the following:

1) User tells system which is primary key for that table, and which are foreign keys - system puts that info in a system table

2) For input - the user can specify lookups for menus and the like from the related tables

3) for output - an "output" lookup is done by the system based on the foreign key info placed in the system table.

4) The user interface for the relationships is not done, so right now, users cannot edit that information in a way that makes any sense to them (it makes sense to me, but that's not the point).

5) Relational integrity is not implemented in any way, which to my mind is a *bad thing*. I certainly could implement relational integrity again using perl instead of SQL.

One major problem I have is that if I focus on using SQL, it means that I have to radically beef up my table creation tool, and also somehow figure out how to have a user interface that makes sense to the non-SQL-speaking unwashed masses :-).

So, any advice, comments, etc. are welcome.

In reply to How to handle relationships by michellem

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.