User Defined Fields PHP Mysql

问题:

I am currently building a small crm application. I need each user to be able to define their own custom fields. I am currently building this crm using php and mysql.

Example: I have a "customer" table which has the standard fields: name, phone, address, email, etc. But i want to allow the user (unique session) to add fields that are custom to his/her business which are only accessible to him (not other users). I then want these custom fields to function just like all the other fields in the table (ability to search, send and received data). I am hoping i can accomplish this in mysql and php but am open to any technology or solution that is considered best practice. Thank you for your help.

回答1:

This can be done by creating a table called "customfields" with the elements "id, fieldname, company_id", then another table that would associate those custom fields with data, eg "customercustomdata: id, customfields_id, customer_id". Associate "ownership" of a field the same way

To create a new custom field, "insert into customfields (fieldname,company_id) values ('Birthday',companyid);"

Does that help?

回答2:

@Matt H: Is this method considered AEV or just standard relational db?

So because i will have many users in many dif industries that will want to add their own custom fields to a number of different tables (contacts, transactions, events, etc) i am assuming that i would need the customfield table to have a user_fk/id or company fk/id, a related table fk/id, an id, and a field name? Am i on the right track? Then in the need to create a 2nd table to hold the data for each custom field buy having a customfield fk/id, customer fk/id, id and a data field to hold the actual data. Is this correct?

Ok so once i build those two additional tables how do I add them to the contacts table so it looks like one big table for the user, instead of the 3 tables?

Thanks again for you help.

Answer

after much research i have found that most people who wish to accomplish this are using document databases not relational databases.

回答3:

You could place an extra column for storing string data and store an array describing the contents for custom cells. For example:

$custom = array(
    array("field" => "bikesOwned", "value" => 4),
    array("field" => "travelled", "value" => 14)
);

then use something like PHPs json_encode to store that data in the extra cell. Then all you would need to do is decode and process the array.

回答4:

Some people suggesting using the Entity-Attribute-Value design, but before you do, please read Bad CaRMa, a story about an EAV-like design that nearly destroyed a company because it was unmaintainable.

To solve this better, read How FriendFeed uses MySQL to store schema-less data. You can lump all the custom columns into a single BLOB, and store it that way. Then if you want individual attributes to be searchable, create a table for that attribute, that maps values back to the customers table.