...

View Full Version : Database design problem - stumped



mikemacx
02-15-2009, 08:14 PM
I'm just gaining my footing inMYSQL, so this may not be a difficult question, but the problem has me a bit confused. Can someone nudge me in the right direction?

I am designing a database containing art information. One table contains galleries all over the world, thousands of them, identified by a unique numeral gallery_id, and then city, state...etc.

Another table contains thousands of artists identified by a unique numerical artist_id. Now where I'm stumped is that I need a key to link both tables together, but an artist can be represented by more than one gallery. I thought the gallery_id was going to be the primary.

How will I be able to attach the numerous gallery_ids to one artist so that i can create an artist page that will list them?

Not sure if this is a separate table or something more complicated in multi-value input in a column.

I'm a newbie, so go easy on me.:thumbsup:

guelphdad
02-15-2009, 08:38 PM
three tables

gallery table
galleryid, physical_address, city, etc


artist table
artistid, name, yearspainted, etc

gallery_artist table

galleryid, artistid

one new row for each gallery an artist has his works in.

take the time to read up on DATABASE NORMALIZATION it is an important concept to understand.

NEVER store multiple values in a single column.

hope that gets you started

mikemacx
02-15-2009, 09:39 PM
Ahhhh...

Thanks for the info. I see what you mean. This should help getting the database off the ground.

In theory, is it better to separate the galleries in individual tables based on continent (Europe, North America, Asia...) or list about 10,000 in one table?

I'm not sure how MYSQL will handle so much information.....

Old Pedant
02-15-2009, 09:48 PM
Better all in one table.

If you find that you are OFTEN making a query that only needs to look at a single country or group of countries, just add an index on (say) the countryID column. (You *do* have a COUNTRIES table with fields COUNTRYID and COUNTRYNAME and maybe CONTINENTID, don't you? And maybe a CONTINENTS table with fields CONTINENTID and CONTINENTNAME?)

Indexing fields that are important in your frequent queries is the best way to handle this. If you have separate tables for each country or continent, then you have to use an ugly UNION when you need to make a cross-country or cross-continent query. MySQL will have no problem with only a few thousand reecords in a single table. Almost noise level for MySQL, in fact.

p.s.: Don't forget to add http://www.ArtsOfSnohomish.org [I'm their volunteer webmaster]. <grin/>

mikemacx
02-15-2009, 10:14 PM
Interesting analysis.....

I'll consider putting all galleries in one table. I had figured joining maybe 8 continents wasn't so bad. Mostly, artists will show in no more than 3 continents.

guelphdad
02-15-2009, 10:20 PM
mysql, properly indexed and properly tuned can handle millions of rows of data in a table.

Old Pedant
02-15-2009, 10:33 PM
Interesting analysis.....

I'll consider putting all galleries in one table. I had figured joining maybe 8 continents wasn't so bad. Mostly, artists will show in no more than 3 continents.

But how will you *KNOW* which 3 continents????

If a person is searching for a single artist, irrespective of what continent he/she is being shown in, you would *STILL* have do do something COMPLETELY ugly such as


SELECT * FROM NorthAmerica WHERE LastName = 'Zamboni'
UNION
SELECT * FROM SouthAmerica WHERE LastName = 'Zamboni'
UNION
SELECT * FROM Europe WHERE LastName = 'Zamboni'
UNION
SELECT * FROM Asia WHERE LastName = 'Zamboni'
UNION
SELECT * FROM Africa WHERE LastName = 'Zamboni'
UNION
SELECT * FROM Australia WHERE LastName = 'Zamboni'

BLECH! How ugly can you get??? PLEASE don't even *THINK* about that!

(And notice that there's no "JOIN" involved...you WOULD be using a UNION.)

mikemacx
02-16-2009, 02:38 AM
I have been humbled by the big brains on the forum...


http://www.v-rodforums.com/forums/images/smilies/notworthy.gif


OK.So I'll keep all galleries on one table, all artists on one table, and a connection table with artist_id and galllery_id

Like this:

Gallery id Artist_id
1 2
1 3
2 2

bazz
02-16-2009, 03:19 AM
I had figured joining maybe 8 continents wasn't so bad.

except there aren't 8 or, at least, there weren't when I was at school ;p

bazz

mikemacx
02-16-2009, 04:14 AM
Yeah...

Shot from the hip on that one...Realized the error..

TY!

http://www.auburnfootball.com/GIFS/yes_sir.gif

bazz
02-16-2009, 04:23 AM
If the continent is related to the artist, you could just add their address to their table. otherwise, if the continent is relative to the gallery, add it to their address details and make you query search that table for the continents.

to ensure consistent spelling you could have a table just for continents, which could be used when fillling in a form for new artists/galleries details.

bazz

Old Pedant
02-16-2009, 07:10 AM
to ensure consistent spelling you could have a table just for continents, which could be used when fillling in a form for new artists/galleries details.

Besides, it's better normalization. <grin style="lurking" />



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum