Newbie - table structure question

I am extremely new to SQL. I have been reading the info all weekend, and feel I
have sort of grasped the concept.
I am trying to set up a database, it will
be pretty mundane stuff, name, address, city
state. I am catagorizing artists, and need
to list their mediums. Now do I have to create several columns for this? for example,
medium_1 medium_2 medium_3
or is their a way to have all the different mediums in one column together?

You have just entered the realm of relational databases.
Things like this are difficult to grasp at first, but i'll try to give you a good explanation.

Probably the best way would be to setup three tables. One of your user, like you have, another for the mediums the user creates art in, and another for the available mediums the artist can select.
In the user table, you don't even need the anything for medium. In the second table, you need 2 fields. User id (or username, if you keep them unique), and medium id (or name). For the medium table, you need two (or three fields). One for the Medium id, the medium name, and the medium description (if wanted). Here's some sample tables:
<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
create table users (
uid INT DEFAULT '0' NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
#other misc columns
PRIMARY KEY(uid)
);

create table users_mediums (
uid INT DEFAULT '0' NOT NULL,
medium_id INT DEFAULT '0' NOT NULL
);

create table mediums (
medium_id INT DEFAULT '0' NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
desc TINYBLOB,
PRIMARY KEY(medium_id)
);
[/code]
Now, insert all available medium types into the mediums table, using something like <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>INSERT INTO mediums VALUES (null, 'mymedium', 'mydesc');[/code].
Then, you insert the users id in to the users_medium table along with the id of the medium he belongs to. This allows you to store as many mediums per user as you want, while being able to expand easliy.

I hope this helps. This is a fairly long post. I've found that the articles here are very useful in PHP/MySQL teaching. For more help, you might want to try www.phpbuilder.com , or just post here.

-silfreed

Ok.. feel free to give long posts because I need detail here.

I think I need to go back and read. I am
assuming the
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
are so they can access their records and
update the info? See I told you I am very very new. I have not even crossed into how
I will set this up on the site so they can
access the data. Have not even thougtht of
that yet. I need to go back and
read some more. I am probably putting the cart before the horse.

I guess I am one of those who just jumps in the pool and hopes I learn to swim before I drown.

What it appears to me is each column is a table on its own? Let me give you a better explanation of what I "HOPE" to do.
I will be listing Artists, their state, city,
Galleries where they exhibit, their mediums
and what type of art they do, ie, landscapes, still lifes and so on.

I also am going to have a listing for Galleries, their addresses, city state
urls, and artists they represent. Hopefully
this is all going to be cross referernced.
I want these searchable, so that if someone
wants to find a landscape artist in Louisiana, they can do a keyword search.
Am I dreaming here? Is this going to be doable, because if this wont' do it then I
will throw up my hands. This is my personal site and I certainly can't afford to hire a programmer. So I decided to learn on my own.
What do you think? Have I taken on something that is beyond a beginner?

Yeah, username and password would be for the user to make their own changes, but it doesn't sound like you'll need that.
Each column isn't a table. A table can have multiple rows, and data has to be in one of the colums. something like this:

| col1 | col2 | col3 |
----------------------------
row1 |'data'|'data'|'data'|
row2 |'data'|'data'|'data'|

Everything you want is doable, although, I could spend my entire workday explaining everything. I would recommend reading some articles on this site (devshed), and refering to the mysql manual when needed. It isn't very good reading if you want general help or tutorials, but is decent if you want help on a specific topic. Feel free to keep asking, though, and don't get discouraged. It took me about 6 months to really grasp everything when I started, but you can learn to swim by jumping in. I think that's the best way.

-silfreed

Thank You so much.

I went by your example,
and was playing around with creating
tables.

On the third table I get this error:
MySQL said: You have an error in your SQL syntax near 'desc TINYBLOB , PRIMARY KEY (medium_id))'
at line 1

What does this mean I have examined my books,
can't find what it means.

DESC is a reserved word (used with order by to indicated a descending sort) so you can't use it as a field name. Change it to descript or something.

Thanks that worked. What I would like to know now, is once the tables are established
and the data is in, what do I use to access this from my website? Is this done with simple html forms, perl or what?

Just for arguments sake, why wouldn't you use an enumerated type for the medium?

Robman

oh you better explain that for me.
I am open to all suggestions, I have only created the tables, I am not entering data as of yet.

I am looking for the best structure, and since I really don't know what I am going any and all suggestions (with examples) would
be appreciated.

I have ordered a book on mySQL so it is on its way, but until then I am reading what I have found online. I am someone who knows practacally nothing about this stuff and wants it to work tomorrow :-). Of course that
isn't the way it is, but when I put my mind to it I learn very fast.

What I want is for artists to submit their info:

Jane Doe
123 Maple St
Anytown, ST 12345
Phone: 555-555-5555
Email: Jane@whatever.com
url: http://www.janedoessiteaddy.com
medium: oils, watercolor, pastels
Type: impressionist, landscapes, still lifes
Galleries: This Gallery, Anytown, St
That Gallery, ThatTwon, St.

Now, I will have galleries list:

So-and-So Gallery
1234 Oak St
Anytown, ST 12345
Phone: 555-555-5555
Fax: 555-555-5555
Email: gallery@galleriessite.com
URL: http://www.galleriessite.com
Artist: Jane Doe
Joe Bob Clampet
John Smith

ok so I want a visitor to the site to be able
to search for a landscape artist in Anytown, St. When it comes up it will list all artists that do landscapes in that city. And
then I want them to be able to search for galleries that have this work by so and so artist. So with that said, have I lost my mind?

I understand setting up tables, and I am familiarizing myself with the terms, but I need a connection. I need to list things in seperate tables but connect somehow.
So what do you think?