0

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.

Yehia Awad
  • 2,898
  • 1
  • 20
  • 31
ChrisDevWard
  • 885
  • 2
  • 9
  • 20
  • 1
    You can't have bidirectional foreign keys like this. A foreign key requires that the reference already exist, so you've got a chicken-and-egg problem. – Barmar Dec 26 '13 at 01:04
  • Okay, so suppose I remove the vehicle.location field, what MySQL query would I use to add a new location to a the vehicle table if the vehicle/location relationship was one-to-many while preserving the VehID foreign key? – ChrisDevWard Dec 26 '13 at 08:46

1 Answers1

1

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.

musical_coder
  • 3,886
  • 3
  • 15
  • 18
  • Awesome, I really appreciate it. I figured I would have to remove the location field from the vehicle table. I wasn't sure if there would be any better ways to do it. Question is, when updating the vehicle table, what would the correct query be? I currently have `INSERT INTO location (VehID, Latitude, Longitude) VALUES (1,38.9, -85.2, 4.8);` but I can't include the VehID since it's a foreign key... So how can I set the VehID? – ChrisDevWard Dec 26 '13 at 04:33
  • When trying to add a new location entry, I'm getting: `Cannot add or update a child row: a foreign key constraint fails` – ChrisDevWard Dec 26 '13 at 09:13
  • As he said, you have to insert into the `vehicle` table before adding into `location`. The rule with foreign keys is that the referenced row has to be added before the row with the foreign key. – Barmar Dec 26 '13 at 14:58
  • "I can't include the VehID since it's a foreign key": actually, you *must* include it. My hunch is that you're not sure how to insert a record into `vehicle`, get the new `VehID` for that record, then use that `VehID` to insert into `location`. You need to do something like this: http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc (if you're using another language than Java, Google it). If my answer works out for you, please mark it as accepted. – musical_coder Dec 26 '13 at 15:29