Page 1 of 1
Automatic Normalization Variable Call
Posted: Sun Apr 01, 2007 4:56 am
by Todd_Z
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
Posted: Sun Apr 01, 2007 6:40 am
by timvw
If simply not having to type the USING part of a join is already enough, you could look into NATURAL JOIN... Otherwise you could consider to create VIEWS and then select from these...
Posted: Sun Apr 01, 2007 3:16 pm
by Todd_Z
I think the view will do it - thanks much
Posted: Mon Apr 02, 2007 1:46 am
by Todd_Z
It was working so well... but its amazingly slow. Any fixes available?
Posted: Mon Apr 02, 2007 7:30 am
by feyd
Have you looked at what EXPLAIN tells you?
Posted: Mon Apr 02, 2007 5:57 pm
by Todd_Z
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!
Posted: Tue Apr 03, 2007 9:38 am
by Begby
Don't make a pseudo view, that is a lazy hack that will cause you headaches down the road.
What does the query look like that you made to construct the view?
Re: Automatic Normalization Variable Call
Posted: Tue Apr 03, 2007 12:43 pm
by califdon
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
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.