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?

 

[djkav]

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.

 

[dangerstat]

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.

SELECT DISTINCT trackID,time, from location order by time

I must be missing something

 

[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

 

[dangerstat]

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

Ah cool glad it helped.

Quick tip, when you talk about creating a table, you can create a view these are essentially virtual tables that can be the results of a more complex query but operate and behave as tables do. So rather than taking up disk space creating a separate table and updating at load time, you can create a view and it will appear and act as a table to your application. More details here:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

It basically saves you having to give complex commands to MySQL all the time and saves on space.

 

[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.

 

[dangerstat]

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.

In that case this should work
SELECT trackID,time from location order by trackID desc group by trackID

(might be asc rather than desc - I always get those mixed up )

 

[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*

 

[dangerstat]

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*

Try changing the order. So do the group by first and the order by second

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

Also the number of columns in the group by MUST be 1 less than the number of fields in the select (IIRC)

 

[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!

 

[dangerstat]

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!

The problem is most likely you need to add the date time column to the order by statement

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

Clearly for a one user query you can just apply a limit 1 to the end of the statement and bobs your uncle.

 

[djkav]

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

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.

Its bad data design to use the Locations table to handle "many tracks per user and many locations per track".

 

[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.

 

[dangerstat]

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

I'm sure that it's really easy to achieve on top of what you have so far I just can't think of the statement...... I'll have a coffee and think it over

 

[djkav]

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

From reading your orginal post, I'd say its needed. The fact that you mention Tracks and querying data based on Tracks, but no table exists to discribe Tracks.

You even showed that you can normalise the data in Locations to create a seperate table for Tracks.

I would stick to the 3 table design. Its elegent, simple and clean. It also conforms to the relational data model thats suited to your quiries.

Sticking with just the 2 tables will result in complex inefficient sql quiries.