Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008

    Unanswered: Example needed for mysql field setup

    Can someone explain the below statement to me with an example/sample... basically what i'm trying to do is handle a member couple profile which will have multiple Firstnames, Age, Height, etc about 10 fields will be similar.

    You could create duplicate fields, but simply add a single extra field, "coupleId", which would have a unique id for each couple; and two rows (one for each person) per couple; then JOIN the table against itself with a constraint like a.coupleId = b.coupleId AND <> so that you can condense the data into a single result row for a given couple.

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    I am not following what you are asking here? What is a member couple profile? Are you saying that two members join up as a couple and you want to have both members details appearing on a single returned row?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    acctman, if you look at it a little more closely, the answer was "you could not create duplicate fields..."

    obviously the table for the people would have one row per person, and you would not want duplicate columns in there

    the simplest solution, and in my mind the most elegant, is a separate table, which contains only two columns, the ids of the two people who are dating

    204 john smith 42
    206 mary white 27
    213 todd kamalfeszchuk 37
    221 linda brown 28
    242 betty boop 45

    204 242
    206 213

    one of the other questions that always comes up in this database design is how do i prevent duplication, e.g. if john and betty are dating, how do i ensure that i don't have john and betty in the database along with betty and john?

    the answer to that is simple, too -- always store the ids with the lower one first | @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