[hamidkhl]

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:

[AdaHacker]

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.

[BobRodes]

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.

[baavgai]

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.

[BobRodes]

Baavgai's point is important.

[e_i_pi]

baavgai, 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

final public function LookupAllById(Array $params = array())
{
	// Set default values
	if(!isset($params['TableName'])) 	return false;
	if(!isset($params['ID'])) 			$params['ID'] = '';

	$query = '
		SELECT *
		FROM "{DB_SCHEMA_AOW}"."{pTableName}"
		WHERE "ID" = :ID
	';
	$placeholders = array(
		'pTableName'			=>	$params['TableName'],
	);
	$index = 'LookupAllById' . $params['TableName'];
	PSArchive::Prepare($index, SqlFactory::SqlQuery($index, $query, $placeholders));

	// Fetch and return the row by ID
	$boundValues = array(
		':ID'			=>	(int)$params['ID'],
	);
	return PSFetch::AssocUnique($index, $query, $boundValues);
}

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.

[baavgai]

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.