SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot charles_i's Avatar
    Join Date
    Apr 2007
    Location
    Toronto, Canada
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lookup Table design question

    I am building a simple CMS based on the Kevin Yank Database Driven Website books (I have 3rd and 4th edition). I really wish there was more discussion about lookup tables than the brief one page overview with one simple example.

    Anyway, what I want to do is very close to his jokes db - I want to input notes on different creative projects - but I have one extra table to lookup. What do I do with my CHARACTER table as it is the same as the TYPE table - a many-to-many releationship with TEXT. Do I make a third column of IDs in PROJTYPE or a second lookup table that is PROJCHAR? If that's the case how does that link to TEXT?

    http://fanoss.ca/lookup.gif

    Thanks
    Charles

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your diagram has a line from projtype to text, and i believe it should be from projtype to project

    use the projtype table as a many-to-many relationship table ~only~ if you are sure that a project can be more than one type

    if a project can be only one type, then typeid column belongs in the project table, assuming, of course, that the type table is intended to classify projects and not texts (as the line on the diagram would seem to indicate)

    as for relating the character table, that all depends on whether you want to relate characters to projects or to texts

    by the way, you shouldn't really use reserved words like TEXT for table or column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot charles_i's Avatar
    Join Date
    Apr 2007
    Location
    Toronto, Canada
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK thanks I'll use CATEGORY - instead of TEXT.

    The connecting lines and arrows are out of the book - I scanned the tables layout page and changed the names in the tables for my purposes.

    A Project is the only thing that there can only be one of - which is why it is connected to the "text" table - because each entry is a text entry.

    I figured that a Project can have each of the types in the list so say I have a Project called My First Book and I have one note that says "scenario idea...chicken crosses the road" and then I have also under My First Book "quote" - "I want Mr. Smith to say... Why did he do it?" So I'm thinking that these are two distinct types of Project - I can sort by quotes or scenarios related to that book later. The quotes must involve a character, but the scenarios may involve several characters. How should I design this?

    Thanks
    Charles

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by charles_i View Post
    The connecting lines and arrows are out of the book - I scanned the tables layout page and changed the names in the tables for my purposes.
    now i'm curious what the tables actually were in the book
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by charles_i View Post
    ... because each entry is a text entry.
    you can't have an image entry? your books have text, nothing else?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot charles_i's Avatar
    Join Date
    Apr 2007
    Location
    Toronto, Canada
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, sorry I've confused you. When I say, "My Book" I'm just using that as an example -I want to consolidate my notes which are currently all text in a word.doc or hand written ideas on scraps of paper into a db. I have no images to input anywhere - these are writing ideas. Think of a series of books - each has ideas - characters - maybe the idea of a piece of text for a sign post somewhere.

    The book that I got the chart from is Kevin Yank's 3rd Edition Building Your Own Database Driven Website - I have the 4th edition as well which shows how to use controller files but since this is such a simple setup I'd prefer just to use the 6 linked tables without building a big CMS. I scanned his setup of lookup tables and overwrote it with my titles.

    Charles

  7. #7
    SitePoint Zealot charles_i's Avatar
    Join Date
    Apr 2007
    Location
    Toronto, Canada
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I've figure it out - this is what I want to do:

    mainText table can have
    1 Project Name
    - multiple Categories
    - multipe Characters

    So I have 2 text tables and 2 lookup tables for Category and Character.

    mainText
    ----------------------------
    id mainText projectid
    ----------------------------
    1 some text 1


    Project
    - links to projectid
    - only one
    ----------------
    projid projName
    ----------------
    1 my_first
    2 my_second


    Category
    - links to lookup table below
    -----------
    id catName
    -----------
    1 category_1
    2 category_2


    mainCategory
    - lookup table
    - mainid links to mainText id
    - categoryid links to category table id
    ------------------------
    mainid categoryid
    ------------------------
    1 1
    1 2


    Character
    - links to lookup table below
    ------------
    id charName
    ------------
    1 character_1
    2 character_2


    mainCharacter
    - lookup table
    - mainid links to mainText id
    - characterid links to character table id
    ---------------------
    mainid characterid
    ---------------------
    1 1
    1 2

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that works nicely, but i have a suggestion

    what you call a lookup table is usually called a relationship or many-to-many or association or linking or junction table (i prefer relationship table)

    a lookup table is typically only use to translate a code into a full name

    e.g. provinces table is a lookup table, has province code and province name, is referred to by various tables' foreign keys, and is used in queries to look up the province code and display the province name
    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
  •