Help! Database design

Here's my little issue:

I'm trying to design a database for a school club website using mysql and php, both of which I'm new at.

I want to record in the database:

-Name
-Year(s) participated
-Competitions entered (districts, state, nationals)
-Events entered

-Each person can participate in many years.
-Each year the person can participate in many competitions
-Each competitions the person can participate in many events.
-And, vice versa for each of the above.

So, I have a table with Member information, an Event table, and a junction table with Name, Year, Competitions, Events as fields, all as primary keys.

But isn't that bad design since the junction table would essentially be a "flat file" table and have _lots_ of redundancy?

I can't think of any other designs, though.

Can anyone say "your junction table's okay" or "here's a better way to do it?"

Thanks in advance,

Gerry

How large of a database is this going to be? if it's pretty small, then there's no need to divide everything up. just store it all in one database.

now, if you want to have seperate databases,and normalize everything, here's how I would do it. Have a MEMBERS table, like you have with name, address, email, etc, and a unique id for each member. Next a YEARS table will hold the unique id of the member and the year they participated. For the competitions, have a sort of COMPETITION_NAME table where you list the possible competitions and assign each a unique id. Then have a COMPETITION table where you store the member id, the competition id, and possibly the year id or year if they are related, also. Do something similar for events, an EVENT_NAME table and an EVENTS table.

You can use various joins to pull out any data you want. By unique id I basically mean assigning an auto_incrementing column to the table and using that value as the unique id.

If you're not familiar with normalization, this is probably a little confusing. I'll try to answer any questions to have if I haven't been very clear. Please do a search on here for normalization, though, as this method has been described by many people before...

---John Holmes...

Your original design seems reasonable but I would put an extra "junction" table (tblCompetionEvents) in, that lookups up competion name and event.
The main table tblCompetionsEntered looks up IDmemberID IDYear IDCompDetails. IDCompDetails looksup tblCompetitions & tblEvents.
Which should display the following if you got you SQL correct.
Member Year Competition Event
Heidi Potter 1998 districts Running
Heidi Potter 1999 state Running
Robert Oldhamstead 1998 districts Javelin
Heidi Potter 2000 nationals Running

The underlying data:
MemberLookup YearLookup CompetitionEventLookup
2 1 1
2 2 5
2 3 8
4 1 3

A MySql design:

# Converted from MS Access to mysql
# by Andy Jackson, (c) SolidTech Solutions Ltd, 05/12/00

DROP DATABASE IF EXISTS dbCompetitions;
CREATE DATABASE dbCompetitions;
USE dbCompetitions;


DROP TABLE IF EXISTS tblCompetitionEvents;

CREATE TABLE tblCompetitionEvents (
IDCompDetails INT AUTO_INCREMENT NOT NULL,
Competition INT NOT NULL NOT NULL DEFAULT 0,
Event INT NOT NULL NOT NULL DEFAULT 0,
KEY (Competition),
KEY (Event),
PRIMARY KEY (IDCompDetails),
UNIQUE KEY (Competition,Event)
);

INSERT INTO tblCompetitionEvents VALUES (1,1,1);
INSERT INTO tblCompetitionEvents VALUES (2,1,2);
INSERT INTO tblCompetitionEvents VALUES (3,1,3);
INSERT INTO tblCompetitionEvents VALUES (4,1,4);
INSERT INTO tblCompetitionEvents VALUES (5,2,1);
INSERT INTO tblCompetitionEvents VALUES (6,2,3);
INSERT INTO tblCompetitionEvents VALUES (7,2,4);
INSERT INTO tblCompetitionEvents VALUES (8,3,1);
INSERT INTO tblCompetitionEvents VALUES (9,3,2);
INSERT INTO tblCompetitionEvents VALUES (10,3,3);
INSERT INTO tblCompetitionEvents VALUES (11,3,4);


DROP TABLE IF EXISTS tblCompetitionsEntered;

CREATE TABLE tblCompetitionsEntered (
MemberLookup INT NOT NULL,
YearLookup INT NOT NULL,
CompetitionEventLookup INT NOT NULL,
PRIMARY KEY (MemberLookup,YearLookup,CompetitionEventLookup)
);

INSERT INTO tblCompetitionsEntered VALUES (2,1,1);
INSERT INTO tblCompetitionsEntered VALUES (2,2,5);
INSERT INTO tblCompetitionsEntered VALUES (4,1,3);
INSERT INTO tblCompetitionsEntered VALUES (2,3,8);


DROP TABLE IF EXISTS tblCompetitions;

CREATE TABLE tblCompetitions (
IDCompetition INT AUTO_INCREMENT NOT NULL,
CompetitionName CHAR (25) NOT NULL,
KEY (CompetitionName),
PRIMARY KEY (IDCompetition)
);

INSERT INTO tblCompetitions VALUES (1,'districts');
INSERT INTO tblCompetitions VALUES (2,'state');
INSERT INTO tblCompetitions VALUES (3,'nationals');


DROP TABLE IF EXISTS tblEvents;

CREATE TABLE tblEvents (
IDEvent INT AUTO_INCREMENT NOT NULL,
Event CHAR (50) NOT NULL,
KEY (Event),
PRIMARY KEY (IDEvent)
);

INSERT INTO tblEvents VALUES (1,'Running');
INSERT INTO tblEvents VALUES (2,'Swimming');
INSERT INTO tblEvents VALUES (3,'Javelin');
INSERT INTO tblEvents VALUES (4,'Discus');


DROP TABLE IF EXISTS tblMember;

CREATE TABLE tblMember (
IDMember INT AUTO_INCREMENT NOT NULL,
FirstName CHAR (15),
Surname CHAR (18),
Address1 CHAR (35),
Address2 CHAR (35),
City CHAR (20),
PhoneNo CHAR (14),
PRIMARY KEY (IDMember)
);

INSERT INTO tblMember VALUES (2,'Heidi','Potter','Forrester Park Golf Club','Beckingham Rd,','Frinton On Sea','01621 333337');
INSERT INTO tblMember VALUES (3,'Andy','Smith','2 Rose Cottage,Weeley Rd,','Gt Totham','Maldon','01206 333337');
INSERT INTO tblMember VALUES (4,'Robert','Oldhamstead','6 Raeburn Close','','Colchester','01255 222268');


DROP TABLE IF EXISTS tblYear;

CREATE TABLE tblYear (
IDYear INT AUTO_INCREMENT NOT NULL,
Year CHAR (4),
PRIMARY KEY (IDYear)
);

INSERT INTO tblYear VALUES (1,'1998');
INSERT INTO tblYear VALUES (2,'1999');
INSERT INTO tblYear VALUES (3,'2000');


I hope this hasn't confused you to much!

Andy



[This message has been edited by eaamj01 (edited December 11, 2000).]

The sql to retrieve the data from the above design:

SELECT tblMember.FirstName
, tblMember.Surname
, tblYear.Year
, tblCompetitions.CompetitionName
, tblEvents.Event
FROM tblCompetitionsEntered
LEFT JOIN tblMember ON tblCompetitionsEntered.MemberLookup = tblMember.IDMember
LEFT JOIN tblYear ON tblCompetitionsEntered.YearLookup = tblYear.IDYear
LEFT JOIN tblCompetitionEvents ON tblCompetitionsEntered.CompetitionEventLookup = tblCompetitionEvents.IDCompDetails
LEFT JOIN tblCompetitions ON tblCompetitionEvents.Competition = tblCompetitions.IDCompetition
LEFT JOIN tblEvents ON tblCompetitionEvents.Event = tblEvents.IDEvent;


Andy J.

BPA eh? Gunna have to do better than that.

Kalispell, MT.