MySQL / PHP：数据库布局，排序和搜索

Question

On my website, for members of my service I provide a contact database where they can store all their contacts. This database is suppose to be more tailored fit for the member. When entering a contact it asks all the standard questions like names and titles, then stores them in table contacts. Then come the additional fields like phone, address, email, and website. But contacts can multiples of those, so I want my members to be able to enter unlimited. I did this by making a table for each field, contacts_phone, contacts_address, contacts_email, contacts_websiteand each row had the id, parent, and data.

This made it extremely hard for me to get a proper search going. The only way these extra fields are connected is through parent which holds the ID of the contact it is connected to. It also cause some other bugs and errors so I decided to trash that idea and considered it a failure.

Now I've decided to limit the member on entering up to 10 multiples of each field. So in table contacts it now has hundreds of columns (phone_1_data, photo_2_data, phone_3_data, ect.) and just seems messy and wrong. It will be so much easier to search through, sort, and interact with but it doesn't seem like the best way.

Answer 1

For any custom columns, add a simple key-value table, with the contact ID, a string key, and a string value. You can use this to match arbitrary numbers of arbitrary custom fields to the contacts, and they'll remain searchable using JOINs.

CREATE TABLE `custom_fields` (
    `contact_id` INT,
    `key` VARCHAR(63),
    `value` VARCHAR(255)
);

To get all the extra fields for a contact:

SELECT `key`, `value` FROM `custom_fields` WHERE `contact_id` = ?

To search for a contact with a particular value in a particular custom field:

SELECT `contact`.* FROM `contacts`
JOIN `custom_fields` ON `custom_fields`.`contact_id` = `contacts`.`contact_id`
WHERE `custom_fields`.`key` = ? AND `custom_fields`.`value` = ?

Or, to find a contact with a particular value in any custom field:

SELECT `contact`.* FROM `contacts`
JOIN `custom_fields` ON `custom_fields`.`contact_id` = `contacts`.`contact_id`
WHERE `custom_fields`.`value` = ?