Automatic Normalization Variable Call

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Automatic Normalization Variable Call

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

I think the view will do it - thanks much
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

It was working so well... but its amazingly slow. Any fixes available?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you looked at what EXPLAIN tells you?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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!
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Automatic Normalization Variable Call

Post 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.
Post Reply