best mysql field setup to have a "couples" member profile data saving.

Question

Couple database profile entry, which would be the best way to save data. Also the data will be retrieved via php coding and if it could be down with one query code that would be ideal.

1. Within the site_member table create multiple field for each field... ex: m_firstname1, m_firstname2, m_age1, m_age2, etc...

2. Store the couple member data in one field each... and separate with a comma in the data field ex: m_firstname (Mike, Sherry)

3. Create a separate table site_member_c duplicating the same fields that are in site_member table. This is roughly about 10 fields

Answer 1

why not just to add a mate_id field to match a couple?

you could use a simple join then:

SELECT h.*, w.name as w_name ... FROM members h, members w WHERE w.mate_id = h.id

Answer 2

You could not 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 a.id <> b.id so that you can condense the data into a single result row for a given couple.