[ale] MySQL mentor

[David S. Jackson]

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.