Jump to content


Photo

Creating a table within a table


  • Please log in to reply
10 replies to this topic

#1 DrAwesomePhD

DrAwesomePhD
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 February 2006 - 11:01 PM

Hi, i'm sure theres a better way to word it... but i'm basically trying to create a table within a table and have run into a little snag. Heres what i'm trying to create: a way of organizing my guild (in world of warcraft) raids via a website. So, i have created a database called "raids" to hold all this information... now i wanted to create a new table for each Raid, and WITHIN each of those tables store the info about people's names, classes, etc. etc. because they are dependent of the raid... AKA different people will sign up for different raids.

Could anyone tell me how to get around this dillemma ? The help would be greatly apprecaited! ^_^

#2 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 21 February 2006 - 11:17 PM

I would actually suggest making a table of all the members in your guild and give them ID numbers, then make a table for raids with unique ID numbers then make a thrid table that combines the two... for example...

Members
(member_id, rank, class, name, email, etc.)

Raids
(raid_id, raid_name, raid_description, raid_date, etc)

Combined_raids
(ID, member_id, raid_id, any info about the member for that raid)

Then you use either member_id to get all the raids for a single person or raid_id to get everyone in a raid.

Believe me, you do not want seperate tables for all of your raids. That was my thought process when I first started and its totally wrong. Relational databases are much easier to code for and deal with.

#3 DrAwesomePhD

DrAwesomePhD
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 February 2006 - 11:23 PM

Aw :-/ so basically i should make a login script and use the login names as unique ID's for signing up for the raids. That makes sense.... so much for being lazy :P thanks for the help!!

#4 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 21 February 2006 - 11:25 PM

personally, I would use integers as the unique ID's because it will be that much faster to find matches.

[!--quoteo(post=348074:date=Feb 21 2006, 05:23 PM:name=DrAwesomePhD)--][div class=\'quotetop\']QUOTE(DrAwesomePhD @ Feb 21 2006, 05:23 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Aw :-/ so basically i should make a login script and use the login names as unique ID's for signing up for the raids. That makes sense.... so much for being lazy :P thanks for the help!!
[/quote]


#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 February 2006 - 12:07 AM

I know that sounds logical enough, but it is "really" true -- does it really matter once there's an index?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 DrAwesomePhD

DrAwesomePhD
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 22 February 2006 - 05:33 AM

Sorry, i was just playing around with this, i'm still confused as to how this combined raid works... So ive got raid and members tables with info in them fine... but how do i say "OK, who is signed up to raid_id=1? " how do i get a list of their member_ids? Will it ultimately look like this:

Raid id:       Member ID:        
1                     11
1                     14
1                     9
1                     27
1                     31
1                     18
2                     11
2                     21
2                     17
2                     24
2                     22

Just as some rnadom numbers, but the thats the basic pattern?

The problem i run into seems to be in the actual coding... so i want to print this out in some sort of reliable fashion... I'm really quite stumped on how you are supposed to do this using these multiple tables and have a clean set of code. The only thing i could think of doing is querrying the combined_raid table, making multiple arrays (one for each class) and storing all the member_id's inside of them for people who have signed up for the same raid_id, and then doing another query with lots of for loops to print the arrays out with even more querries inside of it for the specific member info.... but tahts really really really messy code :-/ surely theres a better way to use MySQL to simply get ALL the info you need and just sorting through it? Please someone help :(

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 February 2006 - 08:08 AM

The benefit of these lookup tables -- other than the many-to-many relationship it allows -- is that you can find all of the members in a given raid, or vice versa, with ease. What specific queries are you having trouble with?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 DrAwesomePhD

DrAwesomePhD
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 22 February 2006 - 05:53 PM

The query where i grab everyone who is in a specific raid (which would be in the combined_raids table) AND the information about each specific character (class, guild, etc etc --- which would be in the members table) so i can print out in an organized fashion who attended the raid and quickly see how many warriors, mages, etc.

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 February 2006 - 07:50 PM

Ok -- so you'd do something like (UNTESTED):

SELECT m.* FROM Combined_raids AS cr, members AS m WHERE cr.member_id = m.member_id AND cr.raid_id = '<raid_id_here>'

Make sense?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 DrAwesomePhD

DrAwesomePhD
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 22 February 2006 - 09:31 PM

sorry for my noobness but it doesnt make sense >.<

What does the "AS m" and "AS cr" mean? and how does that relate to cr.member_id...

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 February 2006 - 10:11 PM

These are simply table aliases, so I don't have to type the entire table name again (and it means less work for the parser).
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users