[nK0de]

I'm designing a database for a Cab/Taxi service. There's a table for taxi service details.

cab_services


And there's another table for locations.

locations


Note that they are categorized according to districts. (CO, GQ, KT) Each district has multiple towns/cities.

A taxi service may be providing their service in multiple districts. And one district may have multiple taxi services. Its sort of a many to many scenario.

I'm trying to connect the cab_services table with the locations table. But I can't figure out how to.
I would have done something like this if only one service was in on district.



But like I said before, a service can have many districts.



This would violate the 1NF.

I would want to be able to get results in a situation like say, if a user search using a Location name, he should get the cab services in that area.

What changes do I have to do in my database, table structure to accomplish this?

Please let me know if some part is confusing, I'll try my best to clarify it further. I'm pretty bad at explaining things. Thank you.

This post has been edited by nK0de: 24 June 2012 - 06:54 AM

[AdaHacker]

Unless I'm misunderstanding your problem, it would seem that the obvious solution is a join table. In other words, a table that associates entries from your cab_services table with your locations table, typically using the primary key of each table. This is a pretty standard solution for a many-to-many relationship. It allows you to relate an arbitrary number of entities without denormalizing your tables.

In your case, you might add a table called service_locations (or soemthing like that) with two columns: SID and LID. To indicate that a service operates in a given location, you would simply add a row to this table. So, in your example, "ABC Taxi" would have two rows:

+------+-----+
| SID  | LID |
+------+-----+
| S001 | CO  |
| S001 | KT  |
+------+-----+

When you want to search by service location, you would simply search this table. You would get the location or service information using joins/subqueries/etc. as appropriate for what you're trying to do.

[nK0de]

You're right! That solution is great. I created another as you have shown, entered some records and used the following query to get the results and so far my testing goes, it works perfectly well.

SELECT T.SID, Code, Name   
FROM locations L
INNER JOIN service_locations SL ON L.Code = SL.Loc_Code
LEFT JOIN cab_services T ON T.SID =  SL.SID
WHERE Location LIKE '%<search_string>%'

Thank you very much.