mySQL, database redesign / optimise?

[GeX]

Hi all.

I have written a tracking application for my phone, and am now working on the web application side of it.

I currently have two tables, User and Location.

User:
UserID, phone#, email, password, name

Location:
UserID, Lat, Long, Alt, Speed, Bearing, Time, TrackID

A TrackID is generated when the user sends a tracking request to the phone. It is made of the UserID plus 4 random digits, and is sent to the device in the activation SMS.

Once it is confirmed that the activation SMS has been delivered, the web app moves to the map view where it selects all the records from the Location table - and displays the points as a route on a map.

I want the user to be able to view past 'tracks', but am not sure the best way of doing this.

I was thinking a drop down box, that would display the time stamps of each track from the Location table (where UserID = logged on User). However, this would have course return every single Location result that had that TrackID.

How would I select just the first time stamp and TrackID of each unique occurance? If I order by timestamp and take the first value, I'll only get data related to the first(or last) track.

I could pull all the data for that UserID out of the table and then programatically select what I need. But that doesn't feel a neat and tidy way of doing it.

I could create another table, Track. Which contains TrackID, UserID, initial time stamp.

Then to display past tracks, I could just query that table (which would be much smaller) and pull out time stamp and TrackID where UserID = logged on user.

But that does mean creating another table, which I (potentially) do not need.

What would you do?

 

[GeX]

Shouldn't your TrackID be unique anway. Each record should have a unique identifier. I always design my tables to have an unique reference and a timestamp. I may never need to use it, but its always there should I later rely on it.

TrackID is unique to each track, but each track is made up of many rows in the Location table.

SELECT DISTINCT trackID,time, from location order by time

I must be missing something

No, that sounds like what I'm looking for. I've only ever scratched the surface of SQL in the past - and the library is closed till Tuesday. I did not know about DISTINCT .

Thanks

 

[GeX]

thanks, it looks like using view is going to come in handy.

SELECT DISTINCT trackID,time, from location order by time

is returning (as expected when looking at it) any unique occurance of both TrackID and Time. What i'm looking for is to return the first occurance of a TrackID and also return the time field from that row.

 

[GeX]

it's not liking group By;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY TrackID
LIMIT 0, 30' at line 1

*stabs server in the face*

 

[GeX]

that seems to be returning what i'm after BUT i'm not too clear on what it is doing, and when i added some different data in the table it didnt act as expected.

If I have three rows, all with the same TrackID but different time stamps.
And then three more rows with the same TrackID (but different to the first 3 rows), again with different time stamps.

I want to return the two distinct values for TrackID and their assoicated first time stamp.

What the above statment is doing is returning the distinct TrackIDs, with the first time stamp that appears in the table.. which for the most would be correct - but isn't exactly the data I want.

Am i making sense, or gibbering on now - it's way past my dinner time!

 

[GeX]

SELECT trackID,time from location group by trackID order by trackID desc,time

Basically that should give you output like:

TrackID Time
1 01/01/2010
1 01/02/2010
2 12/12/2009
2 01/01/2010

Using that as a example return dataset, all i actually want is:

TrackID Time
1 01/01/2010
2 12/12/2009

Basically the timestamp of when the track was requested/created

 

[GeX]

This is how I would design the tables;

Users:
UserID, phone#, email, password, name

Tracks:
TrackID, UserID, Timestamp

Locations:
LocationID, TrackID, Lat, Long, Alt, Speed, Bearing, Time,

Relationship Rules:
A User can have many Tracks.
A Track can have many Locations.

This is what I was thinking re adding another table. But wasn't sure if it was strictly needed.