2 Replies - 938 Views - Last Post: 12 September 2012 - 07:51 PM Rate Topic: -----

#1 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

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

A database table as a field

Posted 11 September 2012 - 01:38 AM

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.

Posted Image

this is the locations table.

Posted Image

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.

Posted Image

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


Is This A Good Question/Topic? 0
  • +

Replies To: A database table as a field

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5777
  • View blog
  • Posts: 12,592
  • Joined: 16-October 07

Re: A database table as a field

Posted 11 September 2012 - 04:17 AM

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

#3 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

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

Re: A database table as a field

Posted 12 September 2012 - 07:51 PM

Thanks, baavgai. :)

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1