[nK0de]

I'm desinging a database to store information about multiple taxi services. What I'm trying to do is a bit

difficult to describe but I'll try my best.

The taxi_services table looks like this.



this is the locations table.



Each taxi service has a locations table.(Shown below) Notice only the values in the Active field have changed. It shows in which locations the taxi service is available.



Think of it like the whole table as another field in the taxi_services table.(I dunno if that makes sense)

Is it possible to connect these 2 tables in that way in MySQL? If not are there any other database management system which can do it?
Or if you can think of a completely different way to go about this, please let me know.

Thank you very much

This post has been edited by nK0de: 11 September 2012 - 01:40 AM

[baavgai]

No. Don't even go there. Stop thinking that way right now.

Why does Location have an active column? It seems that such data belongs to it's service relationship. LocationID is bad, because it's not an ID at all; it is not unique. Compare it to serviceID, which is unique.

Location should looks something like:

LocationID Code Name
1          CO   Fort
2          CO   Pettah
3          GQ   Gampaha

Now, you want a third table, to join your two tables together. Call it ServiceLocation. It will have the ServiceID from one table and LocationID from the other. e.g.

Service  LocationID  Active
S001     1           True
S001     2           True
S001     3           False
S002     1           False
S002     2           True
S002     3           True
S003     1           True
S003     2           False
S003     3           True

Now, depending on what you're looking for from active, this can be simplified. Only have records for active, and the rest are inactive. e.g.

Service  LocationID
S001     1
S001     2
S002     2
S002     3
S003     1
S003     3

Every table should have a primary key. All data in a database is associated through the relationship of those keys and joins are used to show that relationship. In the case of location, a natural primary key would have been code and name. However, complex keys make for complex joins. Using an identity style key makes things easier. You should still put a unique index on those fields for data integrity. Also, ServiceLocation should have it's foreign key relationships stated.

Hope this helps.

[nK0de]

Thanks, baavgai.

This post has been edited by nK0de: 12 September 2012 - 07:51 PM