Joining Tables Based on Foreign Keys?

You talk about writing "nice generic functions", but I think you are thinking a little TOO generic here Personally I would just write a query with a bunch of joins in it. If you want to abstract all that join logic away and not have to worry about it, then you should probably look at using an ORM instead of writing the SQL directly.

You talk about writing "nice generic functions", but I think you are thinking a little TOO generic here. Personally I would just write a query with a bunch of joins in it. If you want to abstract all that join logic away and not have to worry about it, then you should probably look at using an ORM instead of writing the SQL directly.

As a general rule, you should join the lookup tables to the main table. If you have an excessive amount of joins and there aren't many UDFs involved here, there's a pretty good chance the table should be normalized a bit more. If the normalization is fine and the main data table is really wide, you could still split the table to multiple tables with 1:1 relationships so as to separate the frequently accessed columns from the infrequently accessed columns.

MySQL includes support for the ANSI catalog INFORMATION_SCHEMA. REFERENTIAL_CONSTRAINTS. You could use that to gather information on the FK relationships that exist.

Also, if there are combinations of joins you use frequently, create a views or stored procedures based on those common operations.

At some level, the system should run queries using joins, whether those queries are written explicitly by the application programmer or generated automatically by the data access layer. Option 1 is definititely better than the naive option. As for some other query creation options (by no means an exhaustive list): You could abstract out all database operations, much as PDO abstracts out connecting and query operations (i.e.

Preparing & executing queries). Use this to get table metadata, including foreign keys, which could then be used to construct queries automatically. You could write object specifications in some other format (e.g. XML) and a class that would use that to both generate PHP classes and database tables.

You find this more in Enterprise applications than smaller projects. This option has more overhead than others, and thus isn't suitable if you only have a few classes to model. Occurrences of this option might also be a consequence of Conway's Law, which I first heard as Richard Fairly's variant: "The structure of a system reflects the structure of the organization that built it.

" You could take a LINQ-like approach. In PHP, this would mean writing numerous functions or methods that the application programmer can chain together which would create a query. The application programmers are ultimately responsible for joining tables, though they never write a JOIN themselves.

Rather than thinking about how to create the queries, a better problem approach is to think about how to interface the DB and the application. This leads to patterns such as Data Mapper and Active Record that fall into the category of Object-Relational mapping (ORM). Note that some patterns (such as AR), other ORM techniques and even ORM itself have issues of their own.

Any of the above query creation options can be used in the implementation of a data access pattern. The problem with using SHOW CREATE TABLE is it doesn't work with most (all?) other RDBMSs. If you want to marry your app to MySQL, go ahead, but the decision could haunt you.

You talk about writing "nice generic functions", but I think you are thinking a little TOO generic here.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.