DrAwesomePhD Posted February 21, 2006 Share Posted February 21, 2006 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! ^_^ Quote Link to comment Share on other sites More sharing options...
jworisek Posted February 21, 2006 Share Posted February 21, 2006 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. Quote Link to comment Share on other sites More sharing options...
DrAwesomePhD Posted February 21, 2006 Author Share Posted February 21, 2006 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 Link to comment Share on other sites More sharing options...
jworisek Posted February 21, 2006 Share Posted February 21, 2006 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] Quote Link to comment Share on other sites More sharing options...
fenway Posted February 22, 2006 Share Posted February 22, 2006 I know that sounds logical enough, but it is "really" true -- does it really matter once there's an index? Quote Link to comment Share on other sites More sharing options...
DrAwesomePhD Posted February 22, 2006 Author Share Posted February 22, 2006 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 111 141 91 271 311 182 112 212 172 242 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 :( Quote Link to comment Share on other sites More sharing options...
fenway Posted February 22, 2006 Share Posted February 22, 2006 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? Quote Link to comment Share on other sites More sharing options...
DrAwesomePhD Posted February 22, 2006 Author Share Posted February 22, 2006 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 22, 2006 Share Posted February 22, 2006 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? Quote Link to comment Share on other sites More sharing options...
DrAwesomePhD Posted February 22, 2006 Author Share Posted February 22, 2006 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... Quote Link to comment Share on other sites More sharing options...
fenway Posted February 22, 2006 Share Posted February 22, 2006 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). Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.