SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    May 2004
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with database design (many to many relationship)

    Hello

    I have problems designing my FAQ database.

    Now I have 5 tables

    **FAQ**
    pk_id - integer,
    question - varchar,
    answer - varchar
    topic_id - integer

    **TOPIC**
    id - integer,
    name - varchar,
    product - integer,
    software - integer,
    hardware - integer

    **HARDWARE**
    pk_id - integer,
    name - varchar

    **SOFTWARE**
    pk_id - integer,
    name - varchar

    **PRODUCT**
    pk_id - integer,
    name - varchar

    Every FAQ post can be linked to 1 or more hardware, software, product. I have created a extra table to hold the different hardware, software, product_ids and link this value to the FAQ table. Each post can still be linked to different topic_id. How do I design my database correctly? Can anyone please help me in the right direction?

    Thanks
    Svein

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    hardware, software, and product should probably be combined into one table, let's call it subject

    now, which is the entity that can have a many-to-many relationship with subject? can a topic be located in more than one subject, or can a faq be located in more than one subject? can a faq be located in more than one topic?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2004
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your quick reply r937

    Hardware, software and product are combined in the table topic (you called the table subject). I added a field called id in topic to get an unique key. Is it not the correct way to do it?

    A faq post can have one or many topics related and topics can have one or many faq related, since the faq post may have one or many products, hardware and software related. The connection between faq and topic table is topic_id in faq and id in topic.

    A reallife example would be something like this:
    FAQ pk_id 1, 2, 3 is related to PRODUCT pk_id 1,2 and HARDWARE pk_id 2,3 and SOFTWARE pk_id 1.

    I'm sorry if I haven't explained the situation precicely but I hope this is enough info to help me solve this case.

    Best regards
    Svein

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please show actual sample rows from each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    May 2004
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sample table:

    **FAQ**
    id, question, answer, topic_id
    1, this is a question, this is an answer, 1
    2, question no 2, answer no 2, 2

    **TOPIC**
    id, product, hardware, software
    1, 1, 3, 2
    2, 2, 3, 2
    3, 1, 2, 3

    **PRODUCT**
    id, name
    1, product_A
    2, product_B
    3, product_C

    **SOFTWARE**
    id, name
    1, software_A
    2, software_B
    3, software_C

    **HARDWARE**
    id, name
    1, hardware_A
    2, hardware_B
    3, hardware_C

    The problem I'm facing now is that I can only connect one TOPIC id to the FAQ table. I want to relate one or more TOPIC post with one or more FAQ posts. I hope you understand what I am trying to accomplish?

    Regards
    Svein


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
  •