6 Replies - 1343 Views - Last Post: 21 May 2012 - 05:58 AM Rate Topic: -----

#1 hamidkhl  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 03-November 08

2 Relationship Between 2 Tables

Posted 12 May 2012 - 01:10 PM

Hi

I'm designing a data base, I have a start city and a destination city in mission table, I have a city table which includes all cities information, now I want to set relationship between Mission table and City table, is it logical too having 2 relationship between 2 tables(I mean one with start city and another with destination city)

I mean some thing like this:
Attached Image

Is This A Good Question/Topic? 0
  • +

Replies To: 2 Relationship Between 2 Tables

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

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

Re: 2 Relationship Between 2 Tables

Posted 13 May 2012 - 03:20 PM

So... are you asking if both start_city and destination_city should be foreign keys referencing the City table? Yes, I'd say that seems like the obvious choice.
Was This Post Helpful? 1
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: 2 Relationship Between 2 Tables

Posted 17 May 2012 - 06:37 AM

Absolutely. You can have foreign keys to any table, and more than one to the same table if you like. An interesting example is an Employee table. Each employee has a boss, who is also an employee. So you can have a foreign key in a table that references the table it's in.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5874
  • View blog
  • Posts: 12,756
  • Joined: 16-October 07

Re: 2 Relationship Between 2 Tables

Posted 17 May 2012 - 07:43 AM

Yep, looks fine. Underscores and camel case however...

I would recommend avoiding all "Id" for all tables. It can make complex joins error prone and hard to read. If City's PK is CityId, then every join to it is clear. If it's just id, you could be joining to any other table in the query and not notice.
Was This Post Helpful? 1
  • +
  • -

#5 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: 2 Relationship Between 2 Tables

Posted 17 May 2012 - 01:01 PM

Baavgai's point is important.
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: 2 Relationship Between 2 Tables

Posted 20 May 2012 - 07:07 PM

View Postbaavgai, on 18 May 2012 - 01:43 AM, said:

Yep, looks fine. Underscores and camel case however...

I would recommend avoiding all "Id" for all tables. It can make complex joins error prone and hard to read. If City's PK is CityId, then every join to it is clear. If it's just id, you could be joining to any other table in the query and not notice.

For beginners, yes I would agree, though I go against both of these pieces of advice in my application.

In the first case, I use underscores in table names to pseudo-namespace tables, like so:
  • Lookup_ProjectType
  • Lookup_SubmissionStatus
  • Project_Owners
  • Project_Attachments

This is primarily for aesthetics within the DB browser, but also serves an important purpose when I want to retrieve info from the information_schema for all tables of a certain namespace, such as a list of all Lookup tables. I can do this by simply running a LIKE clause against, say, "Lookup_%". The only other way I can see around this is by defining the DB-namespaces in code, which I am not terribly keen on.

In the second case, I specifically use "ID" as the PK for all tables, partly due to habit, partly due to coding convenience. I have several methods in my codebase that rely on the PK being "ID", as is evident in the following method:
Spoiler

This query could be achieved by querying against the information_schema for the PK of the table, which would circumvent the need for PK's of tables to be "ID", but I wasn't keen on running two queries to retrieve one useful dataset.

There's pro's and con's of doing it either way. In the one case, you may introduce elevated numbers of queries against the DB. In the second case, you reduce DBA overhead, simplify your code, but also introduce tighter coupling.
Was This Post Helpful? 0
  • +
  • -

#7 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5874
  • View blog
  • Posts: 12,756
  • Joined: 16-October 07

Re: 2 Relationship Between 2 Tables

Posted 21 May 2012 - 05:58 AM

My advise is, of course, subjective. Everyone comes up with their own way of doing things. I will say that I don't believe database design should be influenced by the external tool selection of the programmer.

A well designed database is often a pain for the programmer to deal with. All those constraints and normalization add complexity that the programmer must deal with. From the programmer's perspective, these are impediments that cause extra work. From a DBA's perspective, the database schema should have consistent internal logic. "It just looks better in my favorite tool" is not a valid design criteria.

A DBA can make things easier on the developer. Using views and stored procedures a level of abstraction can be created that serves as an API for the application side. Indeed, in a large system, the app often doesn't even get to see the tables directly.

I wear many hats. I'm officially the DBA, so I work there first. As the developer, I can curse the DBA as most devs do, but I understand the issues involved.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1