MySQL database design question - storing lists/arrays of indexes

I'm wondering how to store a list of ID indexes for each id in a table. These will effectively form "relations" between the IDs. Here's the main table so far:

table main:

• id, integer, primary key

• name, varchar

• text_info_1, varchar

• text_info_2, varchar

And now for each row, there will be some list of other rows' IDs which will show me how one row relates to the next. For example, the row with id #5 might be related to IDs 6,7,9,25,...etc.

Here are the options I've considered:

1. Create a new column as a text field and store a serialized list of these integer values. Then just unserialize when I want them.

2. Create a new table called "relations" with columns relation_id (int auto increment primary key), name1, name2, [and optional other fields specifying the relation type, which would be nice].

I feel like option 1 is a bit of a hack. I've done it before and it works, but perhaps option 2 is better design?

I'm worried about speed though. With option 1 I can just do SELECT relations FROM main WHERE id = $id, and then unserialize the result and have my array with integer indices. But with option 2 I'll have to browse through a table that will be many times (10x or more) larger, and do "SELECT name1, name2 FROM relations".

Speed is my main priority here. I'm not sure which one is better for space, though I would be curious to find out.

So which option sho开发者_如何学Gould I go with? Are there other good options I haven't considered? I'd also appreciate some good general pointers on database design!

Thanks a bunch.

Second option is better. If you want to get better in database design, you should read about database normalization. Sadly, all the materials I've used were not in English. This wikipedia link could be a start.

Table relations:

relation_id (int auto increment primary key)
id_one (int)
id_two (int)
...

Instead of storing names in the relations table, you store ids.