Best way to organise my stored data?

[razta]

Hello!

 

I am building a web application and I'm not sure what the most efficient way of storing my data is. I have some experience with SQL however it seems obviously not enough.

 

My table looks as follows:

SELECT * FROM data;

+----------+---------------------------+----------+------------------+------------------+
|      id        |             name                    |    tree     |    old_versions     |  latest_version    |
+----------+---------------------------+----------+------------------+------------------+
|      1        |             MYSQL                  |     CS      |   1,1.1,1.2,1.3    |          1.5             |
|      2        |               PHP                     |     5.3     |    5.3.1,5.3.2      |         5.3.3           |
|      3        |               PHP                     |     5.2     |    5.2.1, 5.2.2     |         5.2.3           |
|      4        |               ASP                    |                |                            |                             |
+----------+---------------------------+----------+------------------+------------------+

 

Now what I want to do is add the release date to the 'old versions' and 'latest_version'. What's the best way to structure my database to do this?

 

The data being output would be something like:

 

LATEST: PHP 5.3 (released: 01/01/2010)

              PHP 5.3.1 (released: 01/01/2009) 

              PHP 5.3.0 (released: 01/01/2008)

 

Any help is much appreciated.

 

Thank you.

[Daz84]

I would create a second table to store the release dates

 

Columns would be

Autonumber, ID, version, date

 

So your tables might look something like this

 

SELECT * FROM data;

 

+---+-----------+------+

|ID |    name  | tree |

+---+-----------+------+

| 1 |  MYSQL  |  CS  |

| 2 |    PHP    |  5.3 |

| 3 |    PHP    |  5.2 |

| 4 |    ASP    |      |

+---+-----------+------+

 

SELECT * FROM versions;

 

+----------+----+---------+----------+

|autonumber| ID | Version |  Date  |

+----------+----+---------+----------+

|    1    |  1 |    1  |          |

|    2    |  1 |  1.1  |          |

|    3    |  1 |  1.2  |          |

|    4    |  1 |  1.3  |          |

|    5    |  2 |  5.3.1  |          |

|    6    |  2 |  5.3.2  |          |

|    7    |  3 |  5.2.1  |          |

|    8    |  3 |  5.2.2  |          |

|    9    |  4 |        |          |

+----------+----+---------+----------+

 

Then simply relate the ID fields of the two tables, this would allow for infinate new versions to be introduced and recorded without any messy parsing being required, as per your 'old_versions' field.

 

Hope this helps,

If I can be of any more assistance please ask

 

Daz

[razta]

Fantastic! Thank you very much. I had something like this in mind but wasn't sure quite how to organise it.

 

Thanks again.