Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    8

    Unanswered: how to relate two tables (was "real basic question")

    hi, i am an absolute begginer with this.
    i was wondering how i do the following:

    I have a table called topic i created it like this:

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

    i have another table called Img

    CREATE TABLE Img (
    Img_ID NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Img_Name TEXT,
    Img_Autor TEXT
    );

    I want to have the Img_ID also in the Topic table (as a foreing key??)

    how do i do this? do i just type it in bellow Topic_info? or do i have to call it a key?

    any help would be great

    thanks in advace
    g
    Last edited by godonholiday; 01-02-05 at 16:26. Reason: no help given

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    Join Date
    Jan 2005
    Posts
    8
    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
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Jan 2005
    Posts
    8
    thanks, going to give it a go now,

    thanks again

    g

Posting Permissions

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