Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2005
    Posts
    8

    Build-your-own-club database design

    I've done some searching here for soething similar, but have had no luck yet. I would like to figure out the best way to design a database or rather databases in MySQL for a site that allows people to create their own organization on the site and invite other members to join.

    The basic breakdown is like this:
    One database contains a list of users. This list has some personal info about themselves, etc.

    The other database is the list of all of the organizations.

    The trouble is, I'm not sure how to have these two databases interact.

    In which database do I store information about what clubs a member is in?

    If I put the info in the organizations database, wouldn't I have one field called something like 'members' and it would just be a long blob of names? I certainly can't create a new field for each new member.

    On the other hand, if I have a list of organizations a member is in, wouldn't it again have to be a long list in one field of all of the organizations.

    I hope that makes some sense, and help would be appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you are talking about is tables, not databases

    a single database can contain multiple tables

    your design is a classic many-to-many relationship

    a club can have one or more (many) members

    a member can belong to one or more (many) clubs

    Code:
    create table members
    ( id smallint not null primary key auto_increment
    , name varchar(9)
    );
    insert into members ( name ) values ( 'curly' );
    insert into members ( name ) values ( 'larry' );
    insert into members ( name ) values ( 'moe' );
    
    create table clubs
    ( id smallint not null primary key auto_increment
    , name varchar(9)
    );
    insert into clubs ( id, name ) values ( 101, 'football' );
    insert into clubs ( id, name ) values ( 102, 'macrame' );
    insert into clubs ( name ) values ( 'cooking' );
    insert into clubs ( name ) values ( 'babies' ); 
    
    create table memberclubs
    ( memberid smallint not null 
    , clubid smallint not null 
    , foreign key ( memberid ) references members ( id )
    , foreign key ( clubid ) references clubs ( id )
    , primary key ( memberid, clubid )
    , joindate datetime not null
    );
    insert into memberclubs values ( 1, 101, '2004-09-09' );
    insert into memberclubs values ( 1, 102, '2004-09-09' );
    insert into memberclubs values ( 1, 104, '2004-11-11' );
    insert into memberclubs values ( 3, 102, '2003-04-22' );
    insert into memberclubs values ( 3, 104, '2004-02-29' );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    8
    I love you.

    I'll check out the foreign keys, they are unfamiliar to me.

    But thank you very much for your quick and EXTREMELY helpful post.

  4. #4
    Join Date
    Jan 2005
    Posts
    8
    How exactly is a primary key different from a foreign key?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a primary key is unique and not null, and stands by itself

    a foreign key can be null, is usually not unique, and always refers to a primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    A primary key is unique within the table that defines that key. A foreign key refers to an external entity (almost certainly table), and there may be duplicates.

    eg
    Table Continent
    contID primary key in Continent
    ContDesc describes the name of the continent

    'ie there can only be one row with a specific ContID - it must be unique

    Table Country
    CountryID primary Key in Country
    CountryName describes the country
    ContID foreign key to ContID in table Continent
    ie there can be only one row with a specific countryId - it must be unique, however there can be many rows with the same contID.

    In english there a 5 unique continents, each continent may be comprised of more than one country, however a country cannot belong to more than one continent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not true!!

    the part of turkey west of the Strait of Bosporus is in europe, the part east of it is in asia

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by r937
    not true!!

    the part of turkey west of the Strait of Bosporus is in europe, the part east of it is in asia

    The same was true of the USSR. You could debate Panama too.

    -PatP

  9. #9
    Join Date
    Jan 2005
    Posts
    8
    So wouldn't I want to make both fields in

    Code:
    create table memberclubs
    ( memberid smallint not null 
    , clubid smallint not null 
    , foreign key ( memberid ) references members ( id )
    , foreign key ( clubid ) references clubs ( id )
    , primary key ( memberid, clubid )
    , joindate datetime not null
    );
    NOT be primary keys because one member can be in many clubs. If a member were in 2 clubs, there would be 2 rows with the same member id, and it wouldn't be unique.

    And I tried the foreign key references thing and it looks like the table it made is identical to one without those foreign key lines.

    Maybe I'm not understanding what you guys mean correctly. Help?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the primary key is a composite key, consisting of the values of two columns

    yes, a member can belong to more than one club, but she can belong to a specific single club only once!!!

    so the values you'd see in memberclubs might be --

    insert into memberclubs values ( 1, 101, '2004-09-09' );
    insert into memberclubs values ( 1, 102, '2004-09-09' );
    insert into memberclubs values ( 1, 104, '2004-11-11' );
    insert into memberclubs values ( 3, 102, '2003-04-22' );
    insert into memberclubs values ( 3, 104, '2004-02-29' );

    see? each pair of keys is unique

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Here's your data model showing the composite key
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why does it show a separate index Club_Has_Member_FKIndex1(Club_id)?

    isn't that redundant with the PK (Club_id.Member_id)?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    DBDesigner explicitly states all of it's indexes as part of the display of the entity. The bottom section of the table provides the explicit names of the indexes to be used in the table and the columns involved.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so DBDesigner builds redundant indexes, is that what you're saying?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    No, the two foreign key indexes are used together to create the composite key index.

Posting Permissions

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