SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    liv
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a basic mysql relationship question

    beginner in need of help
    I have created two tables

    CREATE TABLE Topic (
    Topic_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Topic_name TEXT,
    Topic_info TEXT
    );

    CREATE TABLE Img (
    Img_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Img_name TEXT,
    Img_author TEXT
    );

    What i want to do is include the Img_ID in the Topic table(as a foreign key??) so i can refer to it through that table.

    do i do it like this:

    CREATE TABLE Topic (
    Topic_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Topic_name TEXT,
    Topic_info TEXT,
    Img_Id INT NOT NULL AUTO_INCREMENT FOREIGN KEY,
    );

    any help would be great thanks in advance
    g

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in order to do this correctly, you need to answer the following questions

    can a single topic have 0 images?
    can a single topic have more than 1 image?
    can a single image have 0 topics?
    can a single image have more than 1 topic?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    liv
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can a single topic have 0 images?
    Yes some time it does not need an image

    can a single topic have more than 1 image?
    No each topic will have a max of one image

    can a single image have 0 topics?
    YES as a new image might be needed, the old one will be stored but not used

    can a single image have more than 1 topic?
    No each image is related to the specific topic

    i hope you can help

    thanks in advance

    g

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    based on those specs, it's a 1-1 relationship and therefore the tables could be merged, or the foreign could go into either table

    but let's, for the sake of argument, say that you can use the same picture in more than one topic

    thus the foreign key goes into the topic

    create your tables in this sequence:

    CREATE TABLE Img
    ( Img_ID integer NOT NULL AUTO_INCREMENT PRIMARY KEY
    , Img_Name varchar(255)
    , Img_Author varchar(255)
    );

    CREATE TABLE Topic
    ( Topic_ID integer NOT NULL AUTO_INCREMENT PRIMARY KEY
    , Topic_Name varchar(255)
    , Topic_info varchar(255)
    , Img_ID integer
    , foreign key (Img_ID) references Img(Img_ID)
    );

    note that ordinary mysql tables don't support foreign keys, but it's a good idea to define them that way anyway

    also, i gave your ID columns the INTEGER datatype, and changed TEXT to VARCHAR(255) because you probably don't need 65K to name a topic or image
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    liv
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks,

    going to start playing around with it now and see how i do.

    thanks again

    g


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
  •