Database design question ...

TylerNZ

Hi, I'm trying to develop my first relational database ... but am having a bit of trouble trying to understand how I can normalise.

This is the end result that I want to achieve:

Report Form

I created a table called "Members"
Then another called "Attendance" (Date, Arrival, Donation)
And another called "Report" (Basically a table that represents the form page)

However, I'm still having trouble trying to understand how I can design this so that it will record multiple Reports.

Can anyone help with this?!
Thanks!

drawmack

Members:
  ID - bigint, auto_increment
  f_name - varchar(100),
  m_name - varchar(100),
  l_name - varchar(100),
  <any other fields you want>
  primary key('ID'),
  index 'l_name' ('l_name'),
  index 'f_name' ('f_name'),
  index 'm_name' ('m_name')

Meetings
  ID
  Date
  Time
  <etc>

Memters_To_Meetings
  ID
  MemID
  MeetID
  <etc>

TylerNZ

I'm not sure if I'm being SUPER blonde or what ... but I only 1/2 understand this drawmack.

Does that mean my Member_to_Meetings table becomes HUGE coz it has all the members and their details for EACH meeting?

Hmmmm

TheIceman5

yeah something like that, im tired here, something has to become huge when u put data in it, if u got ya hand on a certain something, that will become huge as well.😃

bubblenut

Yes it will become huge but because it only contains three columns and each of these can be well indexed (because they're all unique_id's probably int(10) or something) it will be very quick to traverse.

sid

Originally posted by TylerNZ
I'm not sure if I'm being SUPER blonde or what ... but I only 1/2 understand this drawmack.

Does that mean my Member_to_Meetings table becomes HUGE coz it has all the members and their details for EACH meeting?

Hmmmm

nope - its not that complicated:

members:
  ID - bigint, auto_increment
  f_name - varchar(100),
  m_name - varchar(100),
  l_name - varchar(100),
  <any other fields you want>
  primary key('ID'),
  index 'l_name' ('l_name'),
  index 'f_name' ('f_name'),
  index 'm_name' ('m_name')

meetings
  ID
  Date
  Time

members_to_meetings
  ID
  MemID
  MeetID

see - members_to_meetings will probably have a lot of rows, but only a tiny amount of data in each of these: for example if member nr. 345, 6 and 728 go to meeting 44 itll look like this:

 
insert into members_to_meetings (MemID, MeetID) VALUES (345, 44);
insert into members_to_meetings (MemID, MeetID) VALUES (6, 44);
insert into members_to_meetings (MemID, MeetID) VALUES (728, 44);

i.e. the content looks like this (members_to_meetings ID: primary key, auto_increment):

 
members_to_meetings
ID    MemID    MeetId
 1     345      44
 2       6      44
 3     728      44

to find out what data is connected with user 728, all you have to do is

SELECT * FROM members WHERE ID = '728';

(itll be a lot nicer with a join of course:

SELECT *
FROM members mb, meetings mt, members_to_meetings m2m
WHERE m2m.MemID = mb.ID
AND m2m.MeetID = mt.ID
AND m2m.ID
AND mt.ID = '44';

this will return all info associated with meeting #44 and the users that attended it)

hope that helps a bit!

sid

drawmack

don't forget he also wants to store their status in meetings_to_users but with an enum this will just add another numeric field. Still really smalle and quick to traverse especially if it's indexed properly.

TylerNZ

Thanks for your reply sid (and others) ... it's been really helpful!

I've managed to make my database with the following tables:

Members
TeamLeaders
Teams
TeamsAndMembers
Meetings
Attendance

All linked appropriately. Now I'm trying to create a report page. And I'm calling the data from the Meetings table which contains:
MeetingsID
TeamID (from the Teams table)
Date
TeamLeadersID (from the TeamLeaders table)
Comments
Need Assistance (yes/no)

I'm using dreamweaver, mysql and php ... but how do I get the leaders NAME to appear rather than the TeamLeadersID number?
Same with the TeamID and on top of that, how do I get the "yes" or "no" to display?

THANK you so much.
Joel

bubblenut

You need to do a join.

SELECT Meetings.MeetingsID as MeetingsID,
             Meetings.TeamID as TeamID,
             Meetings.Date as Date,
             Meetings.TeamLeadersID as TeamLeadersID,
             Meetings.Comments as Comments,
             Teams.Name as TeamName,
             TeamLeaders.Name as TeamLeadersName,
FROM    Meetings, Teams, TeamLeaders
WHERE  Meetings.TeamID = Teams.TeamID
&&         Meetings.TeamLeadersID = TeamLeaders.TeamLeadersID
[CODE]
This will not work perfectly for certain cases though. For example if you have a meeting which does not yet have any teams in it then nothing will be output, ie no meeting data. This would be accomplished with a left join.
HTH

TylerNZ

hmmm, ok, I need to do a bit of reading on how to do this. I'm totally lost. Does anyone know of a decent tutorial that will teach me how to join tables with PHP & MySql?!?!?!

THANKS

drawmack

RTFM