[ale] MySQL mentor

[Michael E. Barker]

"David S. Jackson" wrote:
> 
> Hi,
> 
> I volunteered to help some folks at my daughter's school (they're
> really desperate!) who are doing an auction and need someone to
> handle a database for all the donated items.
> 
> I've got Mysql on one of my network boxes here at home, and I've
> been teaching myself the ropes from the mysql.com info and the
> debian-supplied docs.
> 
> I was hoping there was a local MySQL or SQL guru who could review
> my progress and tell me how to get past some problems.
> 
> For example, in my effort to create a normalized structure, I've
> got six tables:
> 
>         My attempted normalized structure
>         ---------------------------------
> 
> [donated item]:
> --------------
> item_id
> description_for_bid_handout
> contributor_id
> description_short
> retail_value
> volunteer_id
> delivery_status_id
> group_id
> 
> [contributors]:
> --------------
> contributor_ID
> name
> address
> city
> state
> zip
> company_contact_id
> volunteer_id
> 
> [contact]:
> ---------
> contact_id
> contact_name
> telephone
> email
> 
> [volunteer]:
> -----------
> volunteer_id
> name
> phone
> email
> address
> 
> [delivery_status]:
> -----------------
> del_status_id
> status_type
> 
> [bid_grouping]:
> --------------
> group_id
> item_id
> 
>                 What they had been working with
>                 -------------------------------
> 
> The basic structure for each record that they've been using in an
> excel spreadsheet is:
> 
> item#  bid#  bid_description  contributor  address  city  state \
>         zip  contact  telephone  email item_descrip  retail_val \
>         vms_volunteer  vms_vol_telephone  delivery_status
> 
>                 Questions and Challenges
>                 ------------------------
> 1. I guess I'm supposed to combine these tables into a single
> table, thus avoiding duplication of data.  Ie, if I misspell a
> contributor's name, I update it in the contributors table, and it
> ripples through any other tables necessary.  Where can I see how
> you create a table from links to the ID keys of the tables above?
> 
> (IOW, can I make a single table like the original excel
> spreadsheet, but have it reflect the info in the respective
> tables instead of retyped information?)
> 
> 2. Oh, yeah.  Do you see any problems in my normalization of the
> data?
> 
> 3. Am I supposed to make a script or macro or something to let
> people update each record?  That is, first they fill in item,
> then bid_description, then contributor, etc.  But since they're
> all in different tables, is there some spiffy way they can update
> all of them in one swell foop?  Have it look to them like they're
> updating a single table than several tables?  Or if they update a
> table that joins the above tables, will the data ripple back to
> the contributor table, the donated_item table, etc.?
> 
> 4. I was going to let people update the catalog over the
> Internet.  I was just going to direct port 3306 to my mysql
> server.  Does that make sense?  Just password basic user account
> in /etc/my.cnf so no passwordless connections can be made?
> Any other precautions?
> 
> 5. My debian potato installation of server and client (ver
> 3.22.32-6) doesn't seem to include isamchk.  apt-cache search
> isamchk doesn't show anything.  How could debian have left this
> out?  Or am I missing something?
> 
> I should probably break out any further questions to separate
> emails.  :-)
> 
> TIA!
> 
> --
> David S. Jackson                        dsj at dsj.net
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> I don't kill flies, but I like to mess with their
> minds.  I hold them above globes.  They freak out
> and yell "Whooa, I'm *way* too high." -- Bruce Baum
> 
> ---
> This message has been sent through the ALE general discussion list.
> See http://www.ale.org/mailing-lists.shtml for more info. Problems should be
> sent to listmaster at ale dot org.

What you want to do can be done with MySQL with a little fancy SQLing
and would be more user friendly if you could hide this with a web
application.

If you have the facility to do the web app. let me know and I'll give
you the help you need.

-Michael

---
This message has been sent through the ALE general discussion list.
See http://www.ale.org/mailing-lists.shtml for more info. Problems should be 
sent to listmaster at ale dot org.