[ale] MySQL mentor

[Tommie M. Jones]

Usually what you are talking about (keeping data unique) is done in the
business logic when using MySQL.

I always hate it when other people suggest other tools on these lists but
I am about to do it

You need foreign keys to do what you are talking about.  I suggest
switching to postgres.  Also get a copy of pgaccess.  It provides you with
a graphical way to manage your database and provides a method for easy
data entry.

Your tables look good for a system that is fairly temporary.

Also I have seen some auction systems on freshmeat, you might want to
check those out.
---------------------------
Get inside Atlanta's Tech Scene - http://www.atlantageek.com
'Business Intelligence' is not an Oxymoron - http://www.intelliforge.com


On Wed, 16 Jan 2002, 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.