Page 1 of 1

Database tutorials

Posted: Tue Aug 22, 2006 12:57 pm
by MrPotatoes
ok. well either i don't know what i want or i don't know where to find it.

i'm pretty good at picking things up and using them as long as the tutorial isnt long winded and thier code works. theory i can search for later so that's no big deal but what i'm looking to do is learn about database design and programming. i want to be really good at it as my job now deals with it more than i've ever had to with databases.

so, i wanted to learn about many to many and a one to one. basically relations with databases.

any good tutorials in PHP with code and explanations?

thanx alot :D

Posted: Tue Aug 22, 2006 1:12 pm
by feyd
I can't recommend any with PHP, but the O'Reilly book Java Database Best Practices has Chapter 2 available as a PDF on their site. It takes about the relations. When you get into the relational things, there's not much PHP involved really. The database does all the real work, you just have PHP to vaccum up the output.

Posted: Tue Aug 22, 2006 1:24 pm
by MrPotatoes
so i've noticed in thngs that i've read up on.

thing is that i'm not sure how or why i would use any of it. i've actually looked it up before and it just seems silly to me for MOST reasons. i guess it's one of those things that when i'm using it i'll wonder why i was so fussy to learn lol

Posted: Tue Aug 22, 2006 1:27 pm
by feyd
Another place for conceptual information on the same line of thought is Wikipedia's articles on Normalization.

Posted: Tue Aug 22, 2006 1:34 pm
by MrPotatoes
i've seen this:

Code: Select all

SELECT
	sum (f_sales.units_sold)
FROM
	f_sales, d_customer, d_time, d_store, d_product
WHERE
	f_sales.customer_id   = d_customer.customer_id AND
	f_sales.date_id       = d_time.date_id AND
	f_sales.store_id      = d_store.store_id AND
	f_sales.product_id    = d_product.product_id AND
	d_time.year_id        = 1997 AND
	d_product.category_id = "tv"
GROUP BY
	d_product.brand, d_store.country_iso_id
with joins instead

Code: Select all

SELECT
	sum (f_sales.units_sold)
FROM
	f_sales
	INNER JOIN d_customer ON d_customer.customer_id = f_sales.customer_id
	INNER JOIN d_time     ON d_time.date_id         = f_sales.date_id
	INNER JOIN d_store    ON d_store.store_id       = f_sales.store_id
	INNER JOIN d_product  ON d_product.product_id   = f_sales.product_id
WHERE
	d_time.year_id        = 1997
	AND d_product.category_id = "tv"
GROUP BY
	d_product.brand, 
	d_store.country_iso_id
from here

now. how is the dot operator the same as the join? because if it is i'd rather use the dot oeprator than the join... i don't see it tho. i think it's the full quieries that are the same

Posted: Tue Aug 22, 2006 2:10 pm
by feyd
The dot operator has nothing to do with the join but with separating table reference from field reference to remove ambiguities that the database would otherwise complain about when multiple tables are referenced during the query. Both of those queries perform the roughly same search. The former is using an implicit inner join, while the latter uses explicit inner joins.

I can't recall offhand if the former is standard SQL, but I know the latter is.

Posted: Wed Aug 23, 2006 10:49 am
by GM
The way I was told - and I'm not saying this is the truth, because I've never seen it in any official literature - is that the syntax

Code: Select all

SELECT a.field1, b.field2
FROM table_1 a, table_2 b
WHERE
blah blah
allows the database management system to optimise the sequence of accessing the tables, whereas the other way (with the explicit INNER JOINS) forces the database to behave as you've told it to.

Like I say, I'm not sure about this, but I use the former method 99% of the time. Sometimes, for a complicated join it can be a bit clearer to write it the other way, but not often, i find.