Need to select from multiple tables based upon records in one table

Question

Hi guys, heres the situation. I have a table called as maps coordinates the schema is like below:

map_coordinates:
item | item_id | latitude | longitude

The idea is that this table contains a list of coordinates of different items, they could be a note, a classifieds post, an event etc. I want to be able to set it up so I have a result set as below:

item | name of item | latitude | longitude

Consider that all events, classifieds and notes are in separate tables. How could I be able to set this up as I need to show all the coordinates on a google maps along with the name of the element i.e if its a classifieds - the title of the ad, an event the title of it etc without having to make alot of sql queries.

I have the code to show it on the map - I just need to grab the details as such. Or do I have to redo my database design here?

========================

EDIT

Actually I have a number of tables in my database such as notes, classifieds and events etc. The point is that an item in the maps_coordinates table refers to the type of the element i.e if its a note or event and teh item_id is the actual id of that event. I don't want to get stuck with using joins here because that would involve alot of tables. Besides I just need to grab basic information as in just the title to help in populating the map so when I hover over the individual markers I don't have to make an ajax call just to show basic information of the element at the marker.

The other tables have their own definitions but all of them have a similar title field and it is this title field I wish to show in the same tuple as the coordinates. Or should I denormalize my tables here and have a duplicate entry for title sin the map_coordinates?

Answer 1

Yes, basically your DB design is not good. Try it like this:

map_coordinates:
item | item_id | item_type | item_name | latitude | longitude

and use a lookup table for item_types.

Answer 2

You say "The idea is that this table contains a list of coordinates of different items, they could be a note, a classifieds post, an event etc.". Does this mean that there are different tables for different items? If so, a denormalized table with all the lookup information is likely your best bet. If not, it's just a matter of JOINing the two tables.