SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Canada, Ontario
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database linking table structure question (newb!)

    Hey guys,

    Im new to SQL and I'm trying to use it just to get the hang of it, was doing good until I got to this problem, it seems simple but I just can't solve it.

    I want to have a listing of tutorials, and each tutorial would have pages.
    So one table would be like page / content.

    Page|content
    1 Content
    2 Content
    3 Content

    then I need a table with id|tutorial name|date added

    But i dont know how I can link the two tables. I think I need a 3rd table.
    And another thing, i need a primary key for the page|content. If I add an ID field it loses normalization. IE:

    ID|Page|Content
    1 1 Content...
    1 2 Content...
    1 3 Content...
    2 1 Content...
    2 2 Content...

    The only way i see a way to fix this is to make a new table for EACH tutorial. Isn't there a way to do this without making a table for each tutorial. It looks so simple but I just cant wrap my mind around it.

    I tried my best to describe my problem. Any pointers would be greatly appreciated!

  2. #2
    SitePoint Zealot romance's Avatar
    Join Date
    Apr 2004
    Location
    UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to use something called joins.

    I'd do something like the following:

    create a table called tutorials liek the following

    Tutorial_name: varchar
    Tutorial_id: Int (auto_increment): primary key

    then create another table called pages

    page_name: varchar
    page_id:Int (auto_increment): primary key
    tutorial_id:Int

    then for each page you add it references the tutorial via the tutorial_id so would have

    tutorial_id|tutorial_name
    1|tut one
    2|tut two

    page_name|page_id|tutorial_id
    page one|1|1
    page two|2|1
    Page one|3|2

    when it comes to getting this data out of the DB, you would use a SQL statement like this

    select * from pages INNER JOIN tutorials ON tutorials.tutorial_id = pages_tutorial_id

    this is the same as saying

    select * from pages,tutorials where tutorials.tutorial_id = pages_tutorial_id

    just a better way of expressing it

    HTH if you need more info, do some searches for INNER JOIN examples

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You first need to make sure what tables you need. I think you have 2 tables already: Tutorial and the other table. But both tables have 'content' , which I assume is going to be BLOB or some text type. the two 'content' column must contain different content I assume, but it's not so clear in your description.

    Either case, you need a join for sure.

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Canada, Ontario
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey romance,

    in yours you have:

    tutorial_id|tutorial_name
    1|tut one
    2|tut two

    page_name|page_id|tutorial_id
    page one|1|1
    page two|2|1
    Page one|3|2

    but I want:

    page one|1|1
    page two|2|1
    page one|1|2
    page two|2|2

    but im not sure if that is right, any idea? how would i establish a primary key?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    establish your primary keys like this:
    Code:
    create table tutorials
    ( tutorial_id integer not null primary key auto_increment
    , tutorial_name varchar(99) not null
    );
    create table tutorial_pages
    ( page_id integer not null primary key auto_increment
    , page_name varchar(99) not null
    , tutorial_id integer not null 
    );
    then when you have loaded your data, run this query:
    Code:
    select T.tutorial_id
         , T.tutorial_name
         , P.page_id
         , P.page_name
      from tutorials as T
    inner
      join tutorial_pages as P
        on T.tutorial_id
         = P.tutorial_id
    order
        by T.tutorial_name
         , P.page_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    how can you say that? i not only touched on it, i nailed it!!

    "I want to have a listing of tutorials, and each tutorial would have pages" -- check

    "But i dont know how I can link the two tables" -- check

    "how would i establish a primary key?" -- check

    slam dunk

    i'm sorry you didn't understand it
    That is a bummer!

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Canada, Ontario
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, sorry I misread, hence my deletion of the post 10 seconds after i posted it. Thanks for the help, im gonna try it out when i get more time.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, i deleted mine now too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •