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 for using with php.

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

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 when using php... 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.

commented: Thanks, to find a new poster who has begun their work before they ask, and its not a Do It For Me, question. +13
Member Avatar

diafol

I suggest two related tables...

**users** (user_id [Primary Key] | username | pw ...)
**ff** (ff_id | user_id [Foreign Key] | ffname | ffnumber)

Examples...

SELECT ff_id, ffname, ffnumber FROM ff WHERE user_id = 7

I doubt whether users want to create a new unique familymember record every time they want to add a member - keep it simple. Just a name and a number.

Member Avatar

diafol

INSERT INTO ff (name, number) SELECT DISTINCT :name AS name, :number AS number FROM users WHERE (SELECT COUNT(user_id) FROM ff WHERE user_id = :user_id) <= 50

This is a workaround (the select distinct clause), and will only insert a record while a users number of family/friends is <= 50.

thanks alot :)