2 Replies - 560 Views - Last Post: 25 June 2012 - 01:13 AM Rate Topic: -----

#1 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Database design problem

Posted 24 June 2012 - 06:49 AM

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

cab_services
Posted Image

And there's another table for locations.

locations
Posted Image

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.

Posted Image

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

Posted Image

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


Is This A Good Question/Topic? 1
  • +

Replies To: Database design problem

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Database design problem

Posted 24 June 2012 - 05:56 PM

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.
Was This Post Helpful? 2
  • +
  • -

#3 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Database design problem

Posted 25 June 2012 - 01:13 AM

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. :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1