Hello,
I'm trying to join two tables for
MySQL Syntax (Toggle Plain Text)
project 2 (users, history), but i
read that JOIN isn't a good practice and will eventually be bad if
your tables get larger.
so i created new tables as the following:
used a foreign key.
the question now am i on the right track?
is JOIN real problematic with larger tables?
is FK any better than JOIN?

another method my friend pointed me to but its also condusing:
he doesn't join or use a FK but he told me to use php to fill everything because table relations are slow...

I am really confused about MySQL i don't know how to design my tables. is there any reliable book or source to learn from other than the MySQL manual cause i am a beginner and the manual isn't friendly...

Thanks in advance.

Mostly difficult to really quantify in practice. Also are you comparing apples with oranges?

FK is a relational construct(DDL/Model) while Joins are a way to fetch the data (DML/View).

In good implementation, you would use FK with joins. Using FK would ensure that the data is consistent even if specific application programs don't validate it).

Thus, I wouldn't reccmmend the third suggestion (using php - or any other program - for this task).

OK?

1 members found this post helpful.

I think you're confused. You're going to need a join to actually get the data out of the database. You use the foreign key to keep the data consistent as it's being modified.

As a rule, the statement that "Join isn't a good practise" is wrong.

Breaking up tables to minimize the amount of duplicate data you're storing is called normalization and is normally a good practise, even though it increases the number of joins required.

It's true that denormalizating is the the right thing for some applications, but there are tradeoffs to this and it certainly isn't automatically the right choice.

Sorry to be honest here, but that's one of the most stupid things I've ever read. Do you think that the carefully performance-tuned database system just might be faster than something he can knock up in PHP? He's probably using an O(n^2) algorithm to join the tables (comparing each row in one table to each row in the other table) in PHP too.

As for books on database design, this one is not bad at all:

http://www.apress.com/9781590597699

And here's a good book on SQL:

http://www.amazon.com/dp/0321118030

1 members found this post helpful.