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

[Sean Davis]

On 1/8/06 6:39 PM, "Peter Edwards" <peterdragon at users.sourceforge.net>
wrote:

> There are several ways of solving this. Your choice depends on whether you
> need a new breed record fully filled out before it is used, and whether you
> want to allow multiple windows.
> 
> 1) Show a dropdown with all allowed breeds in it and an add button after it.
> The add button uses javascript prompt() to ask for new breed name and adds
> it as a new entry to the dropdown with that as the text and NEW as the code.
> In the handler when a code of NEW is submitted create a new breed record in
> skeleton form, to be filled out later, and link the dog breed field to its
> key.
> 
> 2) Show a DHTML combobox that uses AJAX to retrieve substring matches on the
> typed breed.
> Either
> - let them type in a new breed name and on submission create a new breed
> record in skeleton form (like 2 above)
> Or
> - show an "add" button after the combo which will open a new window to fully
> enter a new breed record, then either copy it back to the first window using
> a Javascript callback or let the user type in the newly added breed name in
> the first window's combobox.
> 
> You can pick up a free DHTML combobox or write one or consider a commercial
> one, e.g. http://developer.ebusiness-apps.com/
> 
> 
> 3) Wizard screens. Work out a flowchart of screens that will collect the
> requisite foreign key data and ask for them one screen at a time. So for
> example
> screen 1 - pick dog breed from list (go to 3) or click add breed (go to 2)
> screen 2 - add new breed record (go to 3)
> screen 3 - enter dog name and basic details
> You can extend this with other steps for additional data.
> 
> Regards, Peter

Thanks, Peter.  Your insight helps.  I may start with #3 and work up to #1/2
as time permits; I have never done much Javascript programming, so they will
take me more time to develop at this point.  However, sounds like it is
worth going there....

Sean



> -----Original Message-----
> From: catalyst-bounces at lists.rawmode.org
> [mailto:catalyst-bounces at lists.rawmode.org] On Behalf Of Thomas Hartman
> Sent: 07 January 2006 02:43
> To: The elegant MVC web framework
> Subject: Re: [Catalyst] Re: CRUD, but with multiple tables involved
> 
> Let's say the goal is to enter a dog with a breed.
> 
> Seems to me what's really needed is a list-box. IE, a combination of
> drop-down (for breeds that have already been created) with the option
> of a textbox, for entering a new breed. Does something like this exist
> for html forms?
> 
> If yes, and the user enters a new breed, you could also have a
> "verification" page where you say:
> 
> 'entering this information will create a new breed "Shepherd." Are you
> sure you want to add this breed to the list of already existing
> breeds? Please double check that it doesn't already exist!'  -
> followed by a list of existing breeds.
> 
> I am having similar issues myself and so have been mulling this over.
> I am not sure if this combo listbox thing exists though. Would
> appreciate input on if this is a good/workable idea. And if it's
> workable if it can done with/without ajax.
> 
> Don't really understand what the ajax functionality referred to
> earlier on was needed for. Explanation?
> 
> Thomas.
> 
> 2006/1/6, Karl.Moens at marsh.com <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
> 
> 
> 
>