Database Relationships

I have to make a database from where a user can select fields from a table or tables. My biggest problem is asking about relationships between tables (foreign keys) and passing the names of the requested tables to a processing page.

City: Püspökladány
Country: Hungary

You can use MySQL with InnoDB or PostgreSQL.

The database limits your choice of relationship handling. To answer your question in detail, I need to know which database or databases you want to use and how you will access those databases.

The most popular database for use on Web sites is MySQL and for a long time MySQL did not have table relationships built in. Now MySQL includes the InnoDB database engine that has relationships. PostgreSQL, Oracle, and some other databases have always had relationships built in.

MySQL

MySQL has several database engines for different uses. If you use the popular default MySQL MyISAM engine, you have to build relationships in to your code. That means you could let your visitor select fields for relationships then built PHP code or complex SQL to handle the relationship. You could also design your own relationships, define the relationships in a PHP array, then write code to process the relationships based on the tables. Once the relationships are listed in an array, you can use the array to list the relationships on your field selection form.

Relationships in code is not safe because anyone can use different code and not enforce the relationships. Enter InnoDB. InnoDB has relationships built in, making your database safe not matter what type of code updates the database. InnoDB has one disadvantage. All the tables are in one file, limiting the size of the database. MyISAM and PostgreSQL do not have that limitation.

PostgreSQL

PostgreSQL and similar databases give you the choice of using database-controlled relationships or relationships built in to code in the form of stored procedures or in views.

Stored procedures are normally used to validate data for a field and may be used to build complex data types from a set of simple fields. There is nothing to stop you using stored procedures to assemble data from several tables and effectively implement relationships between the tables. You are more likely to use proper relationship and use the stored procedures only for the data assembly.

Views are a way of creating virtual tables from real tables. Views could create relationships between tables when you read the data and, the same as stored procedures, you would normally use real relationships to protect the data.

Validating in code

The built in relationships could be defined in a PHP array or you could write code to read the relationships from the database using SQL commands. you can then validate the relationships between data before writing to the database.

Which database will be used for your project?

For PostgreSQL, you could look at the code in phpPgAdmin to see how they read information from PostgreSQL. There are PHP based administration interfaces for other databases and one of them might give you an example of the code you need. phpMyAdmin is the best PHP based administration interface for MySQL.

ODBC provides a standard interface to databases. IBM made ODBC the standard interface for their DB2. ODBC provides a standard way of performing simple queries but does not define how a database should implement administration functions in SQL. You still have to write SQL to fit individual databases. Some databases do not provide all administration functions through SQL so you may not be able to list relationships through ODBC.

After you decide how to find the structure of your database, you would have array $t to list the tables:
$t[] = 'city';
$t[] = 'suburb';
$t[] = 'street';

Next you build array $f to list fields:
$f['city'][] = 'name';
$f['city'][] = 'population';
$f['suburb'][] = 'city';
$f['suburb'][] = 'name';
$f['suburb'][] = 'population';
$f['street'][] = 'city';
$f['street'][] = 'suburb';
$f['street'][] = 'name';
$f['street'][] = 'population';

Next you build array $r to list relationships. This is the hardest as the detail in the table decides how much control you can put in to your forms and SQL. The following example says table 'suburb' has a relationship to table 'city' and within that relationship field 'city' matches field 'name'.
$r['suburb']['city']['city'] = 'name';

This would generate SQL containing:
where suburb.city = city.name

The following example says table 'street' has a relationship to table 'suburb' and within that relationship field 'city' matches field 'city' and field 'suburb' matches field 'name'.
$r['street']['suburb']['city'] = 'city';
$r['street']['suburb']['suburb'] = 'name';

This would generate SQL containing:
where street.city = suburb.city and street.suburb = suburb.name

PHP tables can be sorted and can be used to build other tables in different structures. That means you can take the example tables and build lists containing information to suit your application. If you want to store more information about relationships, you can add extra fields to the tables. Probably the first items I would add are short descriptions of each field and relationship so the descriptions can be displayed next to the fields on your forms.

Forms are described in a chapter of the PHP Black Book. Common SQL and database usages are described in other chapters. Accessing information about relationships was not included in the book because the code is very database dependent and you have to know a lot of information about the way the database controls a relationship before you can write code to dynamically build SQL to work with relationships.

Imagine trying to delete suburbs in the example city, suburb, street tables. If you want to delete suburb X and there are streets listed in suburb X, the database might be set up to not let you delete suburb X until you delete all streets in suburb X. Your SQL has to first delete all streets in suburb X then delete suburb X.

The database relationship might be set up to let you delete suburb X and automatically delete all the streets in suburb X. That makes life easier if you want to delete all the streets. What if you want to leave suburbs in the database when they contain streets? You have to write SQL to count the streets in suburb X before deciding if you will delete suburb X.

Your database might let you write SQL, which performs everything in one step. Your database might not support complex SQL and may make you write stored procedures to perform the actions. I suggest avoiding stored procedures because they limit your options for your user interface. A mixture of SQL and PHP could perform the same work without limiting your options.

The next step depends on your database. You have to decide how much flexibility you want in your user interface and work out the simplest way to provide that flexibility with your database. Read the PHP Black Book for general form and database code, then look through open source PHP based projects for database specific code examples. A good book on the SQL used in your database will help build the optimum SQL.