CODEWITHSUNDEEP
mysqldatabasedjangodatabase-designdatabase-schema
ChrisDevWard
ChrisDevWard

Reputation: 965

Updating MySQL Database with foreign keys and Django

I'm in the process of structuring the layout for a database project I'm starting. It's been over a year since I've last worked with structuring foreign keys, etc and to call me rusty is an understatement.

So, for my database, we're going to have a table with vehicles, and a table of locations. Each vehicle has an id (VehID), a location (location), and a name (title). The table of locations has the following fields a LocID, a vehicle ID that the location is for (VehID) that is the foreign key from the vehicles database, it also has the vehicle's latitude, and longitude.

The tables look as such:

-----------
| vehicle | - //Holds all vehicles 
-----------
|  VehID  | - PK, auto_incrementing
|Locations| - //should link to most current location, with location.VehID == vehicle.VehID
|  Title  | - varchar(40)
-----------

-----------
|location |   - //Holds all locations where a vehicle has been
-----------
|LocID    |   - PK, auto_incrementing
|VehID    |   - Foreign Key from vehicle
|Latitude |   - decimal(10,6)
|Longitude|   - decimal(10,6)
-----------

If it helps I'm using the django framework for ORM. Every time I try to add a new entry to the location table, it errors out when trying to set to VehID... How would I go about setting this? If I can't, how would I be able to link the vehicle and location fields? Like I said.. It's been a while.

Upvotes: 0

Views: 124

Answers (1)

musical_coder
musical_coder

Reputation: 3896

Here's how I'd solve this:

  • Remove vehicle.Locations (otherwise you've got a circular relationship between the two tables)
  • Add a timestamp field to location, which has the time at which the vehicle arrives at a particular location
  • Make sure you always insert an entry into vehicle before referencing it in location

To find a vehicle's current location, do a GROUP BY search on vehicle.VehId, and pick the one which has the greatest timestamp less than the current time.

Upvotes: 1

Related Questions