Need help with database structure

[siublue]

Hi. I am creating a database where a user can store up to 50 names of their family members. I have two table structures in mind and curious which one is the more efficient structure.


====================================================================================================

TABLE A:
userid-firstfamilymemberid-firstfamilymembername-secondfamilymemberid-secondfamilymembername   

 

 

Note:(the columns would repeated until the fiftiethfamilymemberid and fiftiethfamilymembername for a total of 101 columns)

 

 

TABLE B:
userid-familymemberid-familymembername

======================================================================================================
which is more efficient... Table A which would have more columns but less rows or Table B which would have less rows but more columns.. also keep in mind that the fixed amount is 50 family members per a user. Thanks in advance.





 

[Psycho]

You would have one table for the users and a separate table for their family members. The family member table would look something like Table B. But, it is customary to have the first field to be the primary ID of the table. So, it would look mire like this:

 

Table: family_members

family_member_id (primary key)

user_id (foreign key)

name

. . . additional fields to describe the family member

 

 

THe above assumes that a family member would belong to one, and only one, user. If a family member can be associated with multiple users, then you would not include the user_if in that table. Instead, you would use a third table to maintain the associations. Each record would contain foreign keys for the users and the family members.

[Barand]

I would have two tables

+------------+
|   user     |
+------------+              +------------+
| userid     |-----+        |  family    |
| name       |     |        +------------+
+------------+     |        | id         |
                   |        | membername |
                   +-------<| userid     | 
                            +------------+

then any user can have as few or many family members as they need. No arbitrary limits because you allowed 50 columns.

 

(Sorry, Psycho. I refreshed before typing and you weren't there)

Edited May 25, 2014 by Barand

[Frank_b]

Later you can retrieve information out of the two tables with just one query. For example all family members from only one user WITH the information from the usertable for that one user.

 

just google on 'mysql joins'

[siublue]

thanks everyone for the solution