Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Feb 2009
    Location
    Los Angeles
    Posts
    41
    Thanks
    2
    Thanked 1 Time in 1 Post

    Question Database design problem - stumped

    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.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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

  • Users who have thanked guelphdad for this post:

    mikemacx (02-15-2009)

  • #3
    New Coder
    Join Date
    Feb 2009
    Location
    Los Angeles
    Posts
    41
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.....
    Last edited by mikemacx; 02-15-2009 at 08:52 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    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/>

  • #5
    New Coder
    Join Date
    Feb 2009
    Location
    Los Angeles
    Posts
    41
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    mysql, properly indexed and properly tuned can handle millions of rows of data in a table.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Quote Originally Posted by mikemacx View Post
    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
    Code:
    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.)

  • #8
    New Coder
    Join Date
    Feb 2009
    Location
    Los Angeles
    Posts
    41
    Thanks
    2
    Thanked 1 Time in 1 Post
    I have been humbled by the big brains on the forum...





    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
    Last edited by mikemacx; 02-16-2009 at 01:43 AM.

  • #9
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by mikemacx View Post
    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #10
    New Coder
    Join Date
    Feb 2009
    Location
    Los Angeles
    Posts
    41
    Thanks
    2
    Thanked 1 Time in 1 Post
    Yeah...

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

    TY!


  • #11
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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
    Last edited by bazz; 02-16-2009 at 03:26 AM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Quote Originally Posted by bazz View Post
    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" />


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •