Power By 专否社区

Best way to store directions data in MySQL

Go To StackoverFlow.com

5

I am working on a app which requires me to store some directions for a location, below is an example of the data i'm trying to store:

Direction 1
From the WEST: Take 528 East (Beechline), pass the Airport Exit, then take exit #13 to Narcoossee Rd and turn LEFT, PARK TO FLY is located 1/4 mile on your left.

Direction 2
From the EAST: Take 528 West (Beechline), before the Airport Exit, Take exit #13 to Narcoossee Rd and turn RIGHT, PARK TO FLY is located 1/4 mile on your left.

Direction 3
From Florida Turnpike: Take Exit #254 to 528 East (Beechline), pass the Airport Exit, then take exit #13 to Narcoossee Rd and turn LEFT, PARK TO FLY is located 1/4 mile on your left.

Direction 4
From 417 (Greenway): Take Exit #26 to 528 West (Beechline), then take exit #13 to Narcoossee Rd and turn RIGHT, PARK TO FLY is located 1/4 mile on your left.

Direction 5
From 408 (East/West Expressway): Take Exit #16 to South S.R. 551 (Goldenrod Rd). Drive aprox. 4 miles, and Turn Left on Hoffner Rd. Hoffner changes to Narcoossee Rd. PARK TO FLY is located 2 miles on your right.

I was wondering if someone could tell me the best and recommended way of storing this data in MySQL.

I am thinking of first serializing the array that holds this data and then storing it into a single column, i don't think i need to worry about searching for this in the database so it should be good? i understand it won't be normalized.

But do you think storing this in a separate table would be the best for the long run?

Update

I have a mysql table named something like Locations, it has information regarding a particular location, now i want to associate some Directions with the locations that are also inputted by the user.

  • User inputs a location
  • User can then also at that time input info about directions.

UPDATE 2
I am asking about the best way to store the data in the database, currently i have no relation at the moment since i haven't created the table for it yet, that's what i am here to ask about.. Should i go and create a relational table as i described above or should i just create another column in the Locations table for the directions and serialize the array holding the directions data to be inserted into a single mysql column.

Thanks in advance!

2012-04-04 16:49
by Zubair1
Do you need to store it in MySQL - liquorvicar 2012-04-04 16:57
@liquorvicar - Yes - Zubair1 2012-04-04 17:02
Why? MySQL is best at storing relational data. This doesn't seem to be relational data; it's more like page content - liquorvicar 2012-04-04 17:06
This is relational data, i just said above the directions are for a location, if that's not a relation i don't know what is - Zubair1 2012-04-04 17:09
I think we really need more information to give you any advice. On what keys do you need to use to retrieve the directions? Looks like maybe "place" and "from location", but that's just conjecture.. - Aerik 2012-04-04 17:09
@Zubair1 - dude, relational data means that your data are related to one another, right? Like in your app you have some "place" (presumably multiple places) - the directions are related to the place they try to get you to. There also seems to be an implied "from" which you may or may not care about in your app - the directions are related to the "from" but the "from" and "place" are not related... see - Aerik 2012-04-04 17:12
Like i said the relation of the Directions is with the Locations. One to many relation. One location can have many ways to reach it. Thus that is why above there are multiple examples of directions - Zubair1 2012-04-04 17:15

3

If, when a user accesses each Location, you will always fetch all of the directions, then you can just stick them in the Locations table. If you ever might figure out where the user is coming FROM, and only select the right direction for that FROM, then you should stick the directions in another table.

2012-04-04 17:38
by Aerik
So far, This seems to be the way i'm thinking of going.. but i'll wait abit longer before accepting this as the answer - Zubair1 2012-04-04 17:42

-2

First, this is a modeling question, not a PHP neither Mysql. You can model your data this way:

place(
  place_id;
  place_name;
)
direction(
  direction_id;
  direction_name;
)
direction_place(
  direction_place_id;
  order;
  direction_id;
  place_id;
)

So, a direction is an ordered sequence of place entities.

2012-04-04 16:58
by ilyes kooli
As I understand, the data already has a model, but no OR-mapping. That's what he's asking for - devsnd 2012-04-04 17:03
when he asked his question, he didn't mentioned he have a model.. - ilyes kooli 2012-04-05 09:06
Ads