Build-your-own-club database design

EggShen · #1 (**permalink**) 01-20-05, 21:51

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.

r937 · #2 (**permalink**) 01-20-05, 22:27

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' );

EggShen · #3 (**permalink**) 01-20-05, 23:01

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.

EggShen · #4 (**permalink**) 01-21-05, 00:16

How exactly is a primary key different from a foreign key?

r937 · #5 (**permalink**) 01-21-05, 07:16

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

healdem · #6 (**permalink**) 01-21-05, 08:30

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.

r937 · #7 (**permalink**) 01-21-05, 08:55

not true!!

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

Pat Phelan · #8 (**permalink**) 01-21-05, 10:42

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

EggShen · #9 (**permalink**) 01-22-05, 19:53

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?

r937 · #10 (**permalink**) 01-22-05, 20:07

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

certus · #11 (**permalink**) 01-26-05, 02:49

Here's your data model showing the composite key

r937 · #12 (**permalink**) 01-26-05, 06:18

why does it show a separate index Club_Has_Member_FKIndex1(Club_id)?

isn't that redundant with the PK (Club_id.Member_id)?

certus · #13 (**permalink**) 01-26-05, 08:15

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.