[ale] MySQL mentor

[Jim Philips]

I am not a "guru" or "mentor" by any means. But if you are going to use
foreign keys (all of those *_id fields) the only way you can enforce
integrity with MySQL is with application logic. For example:

When someone wants to enter a new donated item, they must first select a
valid contributor_id and so on.

MySQL does not support foreign keys, so you don't have any other option.
Most people are handling the logic through PHP. What kind of data entry
interface did you have in mind? If your database needs the kind of
constraints provided by foreign key support, then you need to look
beyond MySQL to PostgreSQL. 

Like I say, I'm not a guru, but if you want a second head to bounce
ideas off of, I will be happy to talk.

 
On Wed, 2002-01-16 at 11:23, 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.
> 



---
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.