SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database structure (normalization) question

    I am creating a database backend for a site. The Top of each page has a title, some text, and a main photo. The bottom of the page has 4 sub categories, each with a photo, a catagory title, and a few links for each category (some sub categories, however, don't have additional links...

    So, here is where I'm stuck..

    table page
    ----------------
    pid mediumint(10)
    maintitle varchar(60)
    maindescription text
    mainphoto varchar(100)

    Now do I need to add one or two more tables to this structure??? and if so, what is the way to creat this.. ie primary keys, foriegn keys, ...

    sub_cateory
    --------------
    sid mediumint(10)
    pid mediumint(10)
    subphoto varchar(60)
    subtitle varchar(60)

    sub_cateory_links
    --------------
    slid mediumint(10)
    sid mediumint(10)
    link_title varchar(60)
    link_url varchar(255)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your table design is fine

    define the primary/foreign keys like this --
    Code:
    create table page
    ( pid integer not null primary key 
    , maintitle varchar(60) not null
    , maindescription text
    , mainphoto varchar(100)
    )
    
    create table sub_category
    ( sid integer not null primary key 
    , pid integer not null
    , foreign key (pid) references page (id)
    , subphoto varchar(60)
    , subtitle varchar(60)
    )
    
    create table sub_category_links
    ( sid integer not null
    , link_url varchar(255) not null
    , primary key (sid, link_url)
    , foreign key (sid) references sub_category (sid)
    , link_title varchar(60)
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey r937, I remember your posts from a year ago

    anyways, I thank you a bunch for the data design... however, this leads me to other questions. Mostly because I don't understand the use of foreign keys..
    foreign key (pid) references page (id)

    So when I design my php to add, delete, and modify... Do I do anything different? Ie.. can foreign keys help with php design? If I am correct (and I could most likey not be) I thought mysql wasn't like larger databases.. that is, tables can't automatically update themselves from other tables?? Am I making sense? I have created alot of database websites, however, I never seem to tie in foreign keys or understand the importance....

  4. #4
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HEre's a question... would I want to make another id field on the sub_category_links table for the primary key to keep it sepperate from the others? ie.. How is a sid a primary key for this table if most of them will have the same primary?

    Quote Originally Posted by r937
    your table design is fine

    define the primary/foreign keys like this --
    Code:
    create table page
    ( pid integer not null primary key 
    , maintitle varchar(60) not null
    , maindescription text
    , mainphoto varchar(100)
    )
    
    create table sub_category
    ( sid integer not null primary key 
    , pid integer not null
    , foreign key (pid) references page (id)
    , subphoto varchar(60)
    , subtitle varchar(60)
    )
    
    create table sub_category_links
    ( sid integer not null
    , link_url varchar(255) not null
    , primary key (sid, link_url)
    , foreign key (sid) references sub_category (sid)
    , link_title varchar(60)
    )

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webgodjj
    ...I don't understand the use of foreign keys..
    foreign key (pid) references page (id)
    that's okay, they aren't often used by mysql programmers

    if you want relational integrity you would have to use innodb tables

    if you do not enable relational integrity by declaring foreign keys, your application has to take care of those details

    you know, simple stuff like an order from a non-existent customer, or a post in a non-existent thread, et cetera

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webgodjj
    HEre's a question... would I want to make another id field on the sub_category_links table for the primary key to keep it sepperate from the others? ie.. How is a sid a primary key for this table if most of them will have the same primary?
    sid is not "a" primary key

    sid is part of the primary key of sub_category_links

    a table can have only one primary key, but it may be a composite key

    no, sub_category_links does not need an additional column

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... I get is a little.. and a little not. I have never used innodb databases. Can you show me an example (php?) how this is done by the database compared to writing it in php done by the application? I am REALLY green in this area.

    I usually use one table to handle most of my designs and can get away with it.. but want to learn more. Every site I design now uses php/mysql.


    Quote Originally Posted by r937
    that's okay, they aren't often used by mysql programmers

    if you want relational integrity you would have to use innodb tables

    if you do not enable relational integrity by declaring foreign keys, your application has to take care of those details

    you know, simple stuff like an order from a non-existent customer, or a post in a non-existent thread, et cetera


  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, my friend, you'll have to get someone else to teach you

    i do coldfusion, not php
    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
  •