You know in AI, how the 1st thing you get given as an example is a route finding program. You start at a point and want to get to an endpoint, and the program will look through the roads trying to find the best way there right?
Well, what I do a lot of at work is we have a few tables e.g. 'Persons', 'Addresses' and 'AddressTypes'. To get a person, we also want to join with Addresses to get his primary address and we want to join with AddressTypes to get the primary address type. So all that sort of goes into one select.
Now, lets say we're looking for contracts. When getting a contract we also want to get the name of the solicitor of the contract (he is a person) and we want to get his primary address and his primary address type. That'll go into one select too. Because that's what relational database are good for: joining tables. I don't really want to get the contract, find the id of the person in charge and then loading him in a second query.However, when writing code for this, I find myself having to write the fact that persons joins to Addresses and Addresses join to AddressTypes twice. Once for getting persons and once for getting contracts.
So, my idea is: wouldn't it be nice if I could define the database in terms of tables and attributes, and relations for getting from one table to another. The relations should get you from an entry in one table to a single entry in another (for example you'd have to say that you want the address for a person that has the Primary flag set to true). You could say the attributes are the endpoints and the relations are the roads. Then, starting from a table, you could ask for all the attributes you wanted from it or any other table. Then you could have an AI style search though the relations in search for a route of joins that will get you the attributes you needed. Then could dynamically build your query from the route, putting in the joins you need and selecting the values you wanted.
