Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Unanswered: Many to Many relationship: Is this correct?

    Hi

    I am new to this forum and also to SQL, DB design. I am stuck with a situation where I cannot define relationship between two tables. It goes something like this. Pls correct me whereever possible.

    The first table 'Products' can have many 'Feature' and a 'Feature' can belong to many products (many to many). 'Feature' can be name-value pair or name-collection. I am storing the collection in the same column as value is stored and collection values are comma-separated values.

    Now if a feature is assigned to a Product, one should be able to configure his own value for a product. For example, P1 and P2 are products and F1 is a collection type feature (V1, V2, V3). If i assign F1 to P1 and P2, then I should be able to configure its own value. V1 for P1 and V2 for P2.

    How do I implement this kind of relationship?
    Just as a work around i have created a new table P_F in which I have (Pid, Fid, Value), where 'Value' is each product's own feature value.

    Pls help me.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your "workaround" is the right solution

    any time you find yourself storing a comma-delimited list of values in a single column, you are almost certainly making a very bad design decision
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Posts
    2
    hmmm, with that said... how can I replace the CSV solution with a better one? Any pointers?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, what you want to do is create a table with the following columns:

    - Pid
    - Fid
    - Value

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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