SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design - two tables

    Hi everyone, I don't have a full vocabulary on this subject, so I apologise if I'm using the wrong words or not making full sense.

    Basically, I need to have a database that has two tables - skills and examples. The records in both these tables need to be able to relate to the other table - so, for example, if I have a record in the "skills" table of "building", I want that record to know which records in the "examples" table are examples of "building".

    I also need the reverse to work, so "example 1" in the "examples" table will need to say which skills in the "skills" table it is an example of. Each record in both tables will correspond to multiple records in the other table, never just one.

    The only idea I've been able to come up with on my own is having a field that is essentially a string list of ids of the other table, which I'd then use with explode(). I don't have a great deal of experience in this, but to me it seems like a stupid idea . . . Ideally I'd be using just the one query to do this (which would not be the case with my idea here).

    Does anyone have any suggestions as to what is the best way to do this? Please let me know if I need to clarify anything about what I've written.

    Thanks for any suggestions.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    It's a many to many relationship, and that means you'll need a third table. Storing a string comma separated id's in a column is a bad idea. It will become very difficult to do the queries you described in your post.

    Tables:

    skills: id, skill, ....
    examples: id, description, ....
    exampleskills: exampleid, skillid

    So the third table will contain a row for each example-skill pair.

    And to extract what you want, you'll have to join the tables in your query. Read the manual for more info on joins if you need to: http://dev.mysql.com/doc/refman/5.5/en/join.html

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    See, I could tell my idea was stupid :P


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
  •