downsizing tables

[almightyegg]

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??

[trq]

Look up some tutorials on database normalization techniques. This sort of thing is a prime candidate.

[almightyegg]

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?

[trq]

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.

[almightyegg]

But what I could find wasn't to do with downsizing, it was making more tables to fit more stuff in

[trq]

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?

[almightyegg]

But surely that doesn't cut down the size of my database...???? Or am I missing something?

[trq]

Is there a reason your so concerned with space? Storing comma separated values in a string is asking for trouble. IMO

[almightyegg]

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...

[trq]

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. 

[almightyegg]

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

[trq]

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.

[almightyegg]

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?