Lets say I have the following table
*book_prices*
book_id
book_price
then another table
*book_titles*
book_id
book_title
Is there a way to automatically join them by book_id if someone were to query the book_prices table for the book_title field?
I know the example is rediculous - but I'm looking for the basic functionality to incorporate into a complicated normalization scheme.
Edit: I mean without a "join clause..." If i have a table with 6 relations to other tables, i don't want to have 1000 character sql queries, i want it to lookup the fields for me
Automatic Normalization Variable Call
Moderator: General Moderators
mysql> explain select * from dt_property where id = 874;
+----+--------------------+------------+--------+---------------------------------+---------+---------+--------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+---------------------------------+---------+---------+--------------------------------+-------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15145 | Using where |
| 2 | DERIVED | property | ALL | building_id,building_id_2 | NULL | NULL | NULL | 15430 | Using where |
| 2 | DERIVED | building | eq_ref | PRIMARY,address_id,address_id_2 | PRIMARY | 4 | tornado_2.property.building_id | 1 | |
| 2 | DERIVED | address | eq_ref | PRIMARY | PRIMARY | 4 | tornado_2.building.address_id | 1 | |
| 3 | DEPENDENT SUBQUERY | gallery | ALL | NULL | NULL | NULL | NULL | 27 | Using where |
+----+--------------------+------------+--------+---------------------------------+---------+---------+--------------------------------+-------+-------------+
5 rows in set (0.43 sec)
I added a key to property for building_id, and on building for address_id
Each select takes ~0.40 seconds
Anything I can do?
--------------------------
Edit: I think I'm going to make a pseudo view by using triggers to create a table with has the same information and gets updated whenever the other tables get updated. It's not a heavily updated database, just large, frequent reads. Should work out okay
Thanks!
+----+--------------------+------------+--------+---------------------------------+---------+---------+--------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+---------------------------------+---------+---------+--------------------------------+-------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15145 | Using where |
| 2 | DERIVED | property | ALL | building_id,building_id_2 | NULL | NULL | NULL | 15430 | Using where |
| 2 | DERIVED | building | eq_ref | PRIMARY,address_id,address_id_2 | PRIMARY | 4 | tornado_2.property.building_id | 1 | |
| 2 | DERIVED | address | eq_ref | PRIMARY | PRIMARY | 4 | tornado_2.building.address_id | 1 | |
| 3 | DEPENDENT SUBQUERY | gallery | ALL | NULL | NULL | NULL | NULL | 27 | Using where |
+----+--------------------+------------+--------+---------------------------------+---------+---------+--------------------------------+-------+-------------+
5 rows in set (0.43 sec)
I added a key to property for building_id, and on building for address_id
Each select takes ~0.40 seconds
Anything I can do?
--------------------------
Edit: I think I'm going to make a pseudo view by using triggers to create a table with has the same information and gets updated whenever the other tables get updated. It's not a heavily updated database, just large, frequent reads. Should work out okay
Thanks!
Re: Automatic Normalization Variable Call
Perhaps this is an oversimplified example of what you're getting at, but the basic fallacy in the above schema is that you shouldn't be using two tables to begin with. A table should represent an entity and ALL properties of the entity should be in the table. That is, the title of a book is a property of the book, and the price of a book is a property of the book. so both properties belong in one table, which represents the entity "book". Now, if the same book has different prices for different customers, that's another matter, but then the price wouldn't be an exclusive property of the book, and you would have to have another foreign key in the "book" table, identifying the customer for whom this price applies to this book.Todd_Z wrote:Lets say I have the following table
*book_prices*
book_id
book_price
then another table
*book_titles*
book_id
book_title