Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

Relational Databases


  • Please log in to reply
12 replies to this topic

#1 BlaineSch

BlaineSch

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1559 posts

Posted 26 November 2009 - 12:54 PM

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
Posted Image

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')
Posted Image

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

#2 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 30 November 2009 - 05:37 AM

Well written, +rep!
  • 0

#3 BlaineSch

BlaineSch

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1559 posts

Posted 30 November 2009 - 09:43 AM

Thank you Jordan! Any constructive criticism?
  • 0

#4 mnmtanish

mnmtanish

    CC Newcomer

  • Just Joined
  • PipPip
  • 22 posts

Posted 26 December 2009 - 09:23 AM

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 ? :confused:
  • 0

#5 BlaineSch

BlaineSch

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1559 posts

Posted 26 December 2009 - 09:28 AM

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?
  • 0

#6 mnmtanish

mnmtanish

    CC Newcomer

  • Just Joined
  • PipPip
  • 22 posts

Posted 27 December 2009 - 05:29 AM

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)
  • 0

#7 mnmtanish

mnmtanish

    CC Newcomer

  • Just Joined
  • PipPip
  • 22 posts

Posted 27 December 2009 - 06:53 AM

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.

Edited by mnmtanish, 27 December 2009 - 06:54 AM.
just tweakin

  • 0

#8 BlaineSch

BlaineSch

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1559 posts

Posted 27 December 2009 - 07:06 AM

Yea try for like 10k or 100k rows.
  • 0

#9 mnmtanish

mnmtanish

    CC Newcomer

  • Just Joined
  • PipPip
  • 22 posts

Posted 27 December 2009 - 08:27 AM

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.
  • 0

#10 BlaineSch

BlaineSch

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1559 posts

Posted 27 December 2009 - 08:30 AM

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

#11 mnmtanish

mnmtanish

    CC Newcomer

  • Just Joined
  • PipPip
  • 22 posts

Posted 27 December 2009 - 08:32 AM

My second, third, .. Queries got results in 0.0002s and its the same for both
That's why I didn't post the average.

Edited by mnmtanish, 27 December 2009 - 08:33 AM.
reason

  • 0

#12 technica

technica

    CC Regular

  • Just Joined
  • PipPipPip
  • 43 posts

Posted 28 December 2009 - 09:13 PM

Very nice tutorial and thanks for sharing it.
  • 0