Jump to content

Archived

This topic is now archived and is closed to further replies.

DrAwesomePhD

Creating a table within a table

Recommended Posts

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! ^_^

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites
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) [snapback]348074[/snapback][/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]

Share this post


Link to post
Share on other sites
I know that sounds logical enough, but it is "really" true -- does it really matter once there's an index?

Share this post


Link to post
Share on other sites
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:

[code]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[/code]

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 :(

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Ok -- so you'd do something like (UNTESTED):

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

Make sense?

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.