Composite key as a foreign key

I’m developing a backend for a google maps administration facility which allows an admin to add markers to the map, which then displays a popup and allows them to enter information into the database; each marker can own 1+ tabs within its popup.

I had developed it using a fixed number of tabs so had no need for the following but decided it would be better suited allowing any amount of tabs. Studying the markers table led me to removing a previously used identity column which was solely used as an id, however google maps latitude and longitude values uniquely identify each marker so I have now stored these as a composite key as below.

  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  `type` int(5) NOT NULL,
  PRIMARY KEY  (`lat`,`lng`)

I also have a tabs table that stores the title and content of a tab, each marker can have more than one tab but each tab can belong to only one marker. How do I create a foreign key within my tabs table that references the composite key of lat and lng in the markers table?

Is this the best way to design the tables? Would I be better suited reverting to an auto incremented id column within markers?

Thanks for any help.

The syntax I was previously using (ALTER TABLE … ADD FOREIGN KEY) was failing but I’ve now created it using ADD CONSTRAINT.

As far as normalisation goes I know this is preferred but which (an id key or a composite key) is more efficient/better practice?

can you post the whole CREATE TABLE statement now that you have a constraint on it?

and float is probably the wrong data type to use. i would probably use decimal instead.

Cheers for the reply, heres the table sql.

table markers

  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  `destination` varchar(50) NOT NULL,
  PRIMARY KEY  (`lat`,`lng`),
  UNIQUE KEY `destination` (`destination`)

table tabs

  `tid` int(5) NOT NULL,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  `title` varchar(25) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY  (`tid`),
  KEY `fk_coords` (`lat`,`lng`)

  ADD CONSTRAINT `fk_coords` FOREIGN KEY (`lat`, `lng`) REFERENCES `markers` (`lat`, `lng`);

I used the float as it suggested it within the google maps api, although I have seen some coordinates longer than the suggested api float type which then loses marker precision.

a natural key is almost always preferred, even if it’s composite.

Thanks for the reply, I opted for both the composite key and decimal over float. Each tuple in my table is identified completely uniquely by a combination of the latitude and longitude so an id column seems unnecessary, and each time I make a call for a tuple I need both lat and lng to place a marker anyway so querying would not be improved by a single column.

I do however have a few questions on normalization; hopefully people will still look at this post so I didn’t bother creating another. I understand the concepts of normalization and any examples I come across but in practice I often hit a few problems. My current problem is to do with data that is not functionally dependant on any key or other table column, as in the example below. The table actually contains a foreign key to an icon name (pk) and its description but it shouldn’t affect the example below so I’ve left it out.

    lat       lng       name      country     continent     lengthofstay
 49.349    -2.349     Jersey         UK         Europe           4
 12.712     24.23      Tampa       USA     North America        7
  7.23     7.901    Eiffel Tower   France       Europe           14

Perhaps I’m misunderstanding normalisation but what do I do with the lengthofstay column? The only thing it depends on is the composite key of lat and lng, however this composite key doesnt determine the length of stay and neither do any of the other columns. Is the table still in 3NF with the length of stay column there?

My second question is how far do I normalise the other three columns? The name is determined by the lng & lat pk (it doesn’t have to be a city). Normally I’d split country off into it’s own table and use an id column as a foreign id, however the country name itself is a natural key and the lat and lng pk determines the country (and continent). It seems pointless storing a foreign key reference to table containing an id column and a country/continent name for each id. Would it not just create extra workload by having to join tables and collect the country/continent name on a query? However by storing the country and continent name within the table above am I not breaking the rules of a 3NF table?

Apoligies for the long winded post, trying to get my head fully round normalization.


for your first question, the answer is yes, lengthofstay is functionally dependent on the lat+long composite key – at least, technically

the way to look at it is like this –

if you have two values for lat and long, can you, unequivocably, from the table data, determine the lengthofstay value? yes

if you have a value for lengthofstay, can you, unequivocably, from the table data, determine the lat and long values? perhaps by coincidence, but in general, no

here’s another one –

if you have a value for country, can you, unequivocably, from the table data, determine a unique pair of lat and long values? no

if you have two values for lat and long, can you, unequivocably, from the table data, determine the country value? of course

see how it works?