Relational Databases

[BlaineSch]

Relationship Databases
This term is used for achieving database efficiency. For a good explanation of what/why click here. This tutorial will show you some common types of this. I will use one example and keep improving it starting from the beginning. The first thing I will do is give you a table structure, then we will improve it using the 2 most common techniques: “1 to 1”, and “1 to many”.

Current table structure:
id, username, email, password, street_addy, city, state, zip, phone, browser, group_1, group_2, group_3, is_admin, verified

1 to 1 Relationship
The first thing you notice is that this table has a lot of columns, because we will not use them every time we search for a user, for instance validating login does not require ‘city’ or displaying a post may not necessarily require ‘phone’, etc. So we would use our 1 to 1 relationship to help simplify this process. The new table should contain it’s own unique id and the id of the user it corresponds with “uid” in this case.
Current table structure:

Users:
id, username, email, password, verified, is_admin

Extra:
id, uid, street_addy, city, state, zip, phone, browser, group_1, group_2, group_3

When needing all of these this can still appear as the first one using a query like this:

SELECT * FROM `Users` LEFT JOIN `Extra` On Users.id=Extra.uid

1 to Many Relationship
The next problem we might notice is that we have a fixed number of groups, which might be what we want, but let's say this application we wanted as many as the user wanted, we don't wanna append a new column everytime the user wants a new one, right? How do we fix this? We might make a new table called "groups" that would have the same unique id, the user's id that points to the user and the group id, so 3 fields.

Users:
id, username, email, password, verified, is_admin

Extra:
id, uid, street_addy, city, state, zip, phone, browser

Groups:
id, uid, group_id

If you don't know much about the user this might be a bit more of a complicated query, depending on what you already know. If you know the user id# then try this:

SELECT * FROM `Groups` WHERE `uid`=2

If you don't know the user id# if you just know the username or something you could try something like this:

SELECT * FROM `Groups` WHERE `uid`=(SELECT `id` FROM `Users` WHERE `username`='BlaineSch')

There are lots of other relationships but that is all for this tutorial!

[Jordan]

Well written, +rep!

[BlaineSch]

Thank you Jordan! Any constructive criticism?

[mnmtanish]

I'm really a newbie on mysql stuff but I have a question about this line
(It has nothing to do with Relational Databases. It's just a mySQL optimization question.)

SELECT * FROM `Groups` WHERE `uid`=(SELECT `id` FROM `Users` WHERE `username`='BlaineSch')

I have been advised to use 2 separate queries than using nested stuff.
First Get the uid and store it temporarily. Then Get all other data using the uid. Like for example

First:
SELECT `id` FROM `Users` WHERE `username`='BlaineSch'
And then:
SELECT * FROM `Groups` WHERE `uid`='$temporary_uid_variable'

I heard that it will run SELECT `id` FROM `Users` WHERE `username`='BlaineSch' for each record on the Groups table.

Is it real or is MySQL smart enough to figure it out OR is requesting 2 queries worse than using nested queries ?

[BlaineSch]

I really don't think mysql would make it to do the query for each row that seems a bit insane, if it did however I would suggest doing the two queries. But I personally have never tested the two for speed.

Why not run a few tests and post up your results?

[mnmtanish]

I'll need some what you call BIG database to check it.
I'll first write a php code to generate some dummy data. I'll post the result soon.

(I don't like to head this thread off-topic)

[mnmtanish]

I did the test with 200 rows of data I generated at generatedata.com And here's the result:

* No-index
Sub    :    0.0012
Double :    0.0002 + 0.0008 = 0.0010
* Indexed
Sub    :    0.0003 OR 0.0004
Double :    0.0002 + 0.0002 = 0.0004

I think I'll have to try this with more Dummy data.

[BlaineSch]

Yea try for like 10k or 100k rows.

[mnmtanish]

I did the test again with 10k dummy rows and here's the results

Indexed
Sub    :    0.1970
Double :    0.0151 + 0.0089 = 0.0240

I did the sub query version first. And I don't know if these results can be accepted as solid data as MySQL loves to cache stuff.

[BlaineSch]

If you think mysql is caching itself why not do the test multiple times in various orders and post the averages?