Advice

[peterjunsworth]

Hi,
I am just looking for some advice with storing data in phpMyAdmin.
I have a table with is setup like a matrix:
It has place names as column titles and also has the same place names as unique fields in each row - for example:

Column Names:
place_name (unique field)
Auckland
Christchurch
Dunedin

Rows
place_name = Auckland
place_name = Christchurch
place_name = Dunedin

Now, what I want to do is store distances in the remaining fields. So where Auckland is the place_name and Auckland is the column title it will read 0 as the distance.

This is all fine, however I want to store km, miles and driving time in each field that returns a value other than 0 so for instance:

The field where place_name = Christchurch, column title is Auckland will return 1700 (for km), 1400 (for miles) and 17 (for drive time).

My question is - should I store the km, miles and drive time in the database as comma separated values or is this not wise?

Hope this makes sense.

Cheers

[oddz]

Storing the values as comma separated values is not wise. Seems like the only columns needed are miles and drive time since km can be calculated given miles. I guess that all depends on how the data will be entered. However, more importantly is your design breaks first normal form. You *should* really have a single table that has all the locations in a normailzed, non-repeating format. Than have a second table start place,end place,drive time and miles.

something like:

places
-------------
name

place_to_place
---------------------
start_place
end_place
miles
drive_time

* You can use surrogate key rather than synthetic if you prefer, that was just an example.

It may also be worth considering name overlaps, based on country (?), city, state, etc. Not sure about that since a context was not provided for a "place" but it seems likely to happen just using a name, rather than full location by say: country,sate,city perhaps, w/ state being optional.

[peterjunsworth]

Yeah - thanks for that.
I have been mocking up a plan to redesign the application with normailzed data but have created the CSV table in the meantime while the new model gets developed. It all seems to work with CSV data but I agree - it is not the smartest way to do it.

Thanks for getting back to me.

Peter