[Catalyst] Re: CRUD, but with multiple tables involved

[Karl.Moens at marsh.com]

Sean Davis wrote:

> Date: Fri, 06 Jan 2006 07:21:28 -0500
> From: Sean Davis <sdavis2 at mail.nih.gov>
> Subject: [Catalyst] CRUD, but with multiple tables involved
> To: The elegant MVC web framework <catalyst at lists.rawmode.org>
> Message-ID: <BFE3CAF8.323E%sdavis2 at mail.nih.gov>
> Content-Type: text/plain;   charset="US-ASCII"

> This is not really a catalyst-specific question, but there is a lot of
> brain-power and experience lurking on this list, so I ask here.  (Oh, did
I
> mention, I am using Catalyst for developing this?)

> I have a table in the database "Dogs" that stores information like the
name,
> date-of-birth, kennel club ID, registry name, etc.  With each dog, I also
> want to store the owner (stored in a contacts table), a breed (in the
breeds
> table), a sire and dam (these are references back into the "Dogs" table).
> In short, there are foreign keys that I need to account for.  If all of
> these external objects (owner, breed, etc) already exist, they can be
simply
> included in a dropdown list.  However, if one doesn't exist, the story is
> different and I need to allow users to create them.  I can see multiple
ways
> of doing this:

> 1)  Make a set of "input" screens that force the user to choose all the
> "foreign objects" first and have the option on each page (breed, owner,
> parents) to create new entries as needed or to choose existing ones.
> Advantages: simple to create and can utilize the /list, /add, /edit
> functions for each of breed, owner, etc.

> 2)  Make a single page that does everything in one shot.  This would
> probably require either refreshes or AJAX calls to update dropdown lists
and
> would make "searching" breed, owner, etc. hard to do.

> 3)  Make a single page that has links to "create new owner", "created new
> breed", etc.  This is probably what I envision being able to accomplish.

> Are the general hints or specific examples of this kind of application in
> action?

> Thanks,
> Sean

I had a similar decision to make for my "Biblio" database. Users have to
enter the name of the book, author, publisher, owner, ... with separate
tables for all these attributes.

Rather than have the users go the counter-intuitive way of first having to
make the new entries before filling in the main form or the technically
more difficult way of using AJAX and its like, I opted for a single page in
which the user fills in all basic data (e.g. name of the publisher), but
not (yet) the additional (and redundant) data, such as address of
publisher, ... . The application parses the form and checks if there exists
already entries in the various tables and if not returns to the user with
another form and request to fill in the full set of data required for these
tables. The main problems with this system is that typing errors or
alternative spellings of key-fields will add "duplicate" records in your
tables and that one puts great trust in the users to indeed fill in the
additional form in order to keep all the tables nicely up-to-date. If the
link is broken or the user does not provide the info on the additional
form, the data in the table gets "corrupted". This can probably be avoided
by using transactions, rolling back the whole operation if not all data is
provided. The first problem is less easily solved (I haven't found a good
solution yet), but some automated (off-line?) checking of the records
(perhaps using a Soundex algo. or such) could flag duplicated or almost
duplicated entries for manual fine-tuning later.

Karl
aka CountZero at perlmonks.org




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This message and any attachments are confidential. If you have received
this message in error please delete it from your system. If you require any
assistance please notify the sender. Thank You.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~