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

问题描述：

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.

在我的网站上，对于我的服务成员，我提供了一个联系人数据库，他们可以存储所有联系人。假设该数据库更适合该成员。输入联系人时，它会询问所有标准问题，如姓名和职务，然后将其存储在表contacts code>中。然后是其他字段，如电话，地址，电子邮件和网站。但是联系人可以是那些的倍数，所以我希望我的成员能够无限制地进入。我这样做是为每个字段创建一个表， contacts_phone，contacts_address，contacts_email，contacts_website code>，每行都有id，parent和data。 p>

这让我很难进行正确的搜索。这些额外字段连接的唯一方法是通过 parent code>来保存它所连接的联系人的ID。它还会导致其他一些错误和错误，所以我决定废弃这个想法并认为它是一个失败。 p>

现在我决定限制成员每次输入10个倍数。因此，在表 contacts code>中，它现在有数百列（phone_1_data，photo_2_data，phone_3_data等），而且看起来很混乱。搜索，排序和交互将变得更加容易，但这似乎不是最佳方式。 p> div>

答

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` = ?

答

You were on the right track storing repeatable elements in a separate table. Why did it become "extremely hard ... to get a proper search going"?

Joining on parent id should have got you what you waned. Perhaps you just needed another column to sort, or key off of for the detail tables?

parent_id, data_desc, data '123','mobile1','123.456.7890'

Adding new columns to the contact row will cause you more pain in the long run. Look up "Database Normalization" for table design ideas that will work best for you.

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

相关推荐