almightyegg Posted April 4, 2007 Share Posted April 4, 2007 Firstoff, I didn't know whether to post it here or in the MySQL board...sorry if this is the wrong board. I have, or will have over 100 groups on my site, they can have a diplomatic stance with one another BUT I don't want each group to have to have a table with 100 extra columns saying what stance they are asit would take up way too much space...is there any way I could have three columns named: allied neutral enemy and then a list of the group IDs like 10, 59, 450 etc... so I would be able to split it up and count how many IDs are in there AND show individual IDs when viewing other groups pages?? Quote Link to comment Share on other sites More sharing options...
trq Posted April 4, 2007 Share Posted April 4, 2007 Look up some tutorials on database normalization techniques. This sort of thing is a prime candidate. Quote Link to comment Share on other sites More sharing options...
almightyegg Posted April 4, 2007 Author Share Posted April 4, 2007 I had a look around and all I could find didn't seem to explain how I could do it ??? Do you know of any tutorials that will help me? Quote Link to comment Share on other sites More sharing options...
trq Posted April 4, 2007 Share Posted April 4, 2007 In 8 minutes? Nothing? Database normalization isn't always a simple process, I would think you'd need to spend at least a day reading about it and experimenting. Quote Link to comment Share on other sites More sharing options...
almightyegg Posted April 4, 2007 Author Share Posted April 4, 2007 But what I could find wasn't to do with downsizing, it was making more tables to fit more stuff in Quote Link to comment Share on other sites More sharing options...
trq Posted April 4, 2007 Share Posted April 4, 2007 A simple example might be something like. CREATE TABLE stances ( id INT AUTO INCREMENT PRIMARY KEY, group_id INT, alliance_id INT, stance VARCHAR ) Then, if group 4 was allied with 5,33 and 54, neutral with 14 and 6, and enemies with 77 and 99 you would run.... INSERT INTO stances (group_id,alliance_id,stance) VALUES (4,5,'allied'); INSERT INTO stances (group_id,alliance_id,stance) VALUES (4,33,'allied'); INSERT INTO stances (group_id,alliance_id,stance) VALUES (4,54,'allied'); INSERT INTO stances (group_id,alliance_id,stance) VALUES (4,14,'neutral'); INSERT INTO stances (group_id,alliance_id,stance) VALUES (4,6,'neutral'); INSERT INTO stances (group_id,alliance_id,stance) VALUES (4,77,'enemy'); INSERT INTO stances (group_id,alliance_id,stance) VALUES (4,99,'enemy'); To find all allies of group 4.... SELECT alliance_id FROM stance WHERE group_id = 4 && stance = 'allied'; Get the idea? Quote Link to comment Share on other sites More sharing options...
almightyegg Posted April 4, 2007 Author Share Posted April 4, 2007 But surely that doesn't cut down the size of my database...???? Or am I missing something? Quote Link to comment Share on other sites More sharing options...
trq Posted April 4, 2007 Share Posted April 4, 2007 Is there a reason your so concerned with space? Storing comma separated values in a string is asking for trouble. IMO Quote Link to comment Share on other sites More sharing options...
almightyegg Posted April 5, 2007 Author Share Posted April 5, 2007 Because I don't really want to have to sort out lots of different rows... if there were only 5 groups there would be 10 rows if there were only 7 groups there would be 21 rows if there were only 9 groups there would be 36 rows if there were 99 groups there would be 4,800 + rows...If I have worked my nth term right It would take too long to do... Quote Link to comment Share on other sites More sharing options...
trq Posted April 5, 2007 Share Posted April 5, 2007 I'm not sure what you mean by 'sort out'. Anyways, thats how I would do it. If you want to do it your way, fine. CREATE TABLE stances ( id INT AUTO INCREMENT PRIMARY KEY, group_id INT, allied TEXT, neutral TEXT, enemy TEXT ); INSERT INTO stances (group_id,allied,neutral,enemy) VALUES (4,"5,33,54","14,6","77,99"); To get the allies of group 4. <?php $sql = "SELECT allied FROM stances WHERE group_id = 4;"; if ($result = mysql_query($sql)) { if (mysql_num_rows($result)) { $row = mysql_fetch_assoc($result); $allies = explode(',',$row['allied']); foreach($allies as $group) { echo $group."\n"; } } } ?> At least using my method you could also incorporate a JOIN so as to get the names straight up. You'll need to use another query (or a loop of them) using your method. Quote Link to comment Share on other sites More sharing options...
almightyegg Posted April 5, 2007 Author Share Posted April 5, 2007 I'm not sure what you mean by 'sort out'. Anyways, thats how I would do it. by sort out I mean write 100000 rows out: id id2 1 2 1 3 1 4 1 5 etc... But, looking at my way, it is still very time consuming and not very 'bug proof' If there is a way to write out the rows quickly then it would be good Quote Link to comment Share on other sites More sharing options...
trq Posted April 5, 2007 Share Posted April 5, 2007 Surely your not planning on adding it all by hand? Aren't your users going to submit a form or something? I'm sorry, but your questions are becoming more and more cloudy. Quote Link to comment Share on other sites More sharing options...
almightyegg Posted April 5, 2007 Author Share Posted April 5, 2007 These groups don't start by users, I will add more and more as more users come and award certain users leadership of the groups... So how could I add these rows automatically? 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.