SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Advice

  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advice

    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

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •