如何构建一个完全可自定义的应用程序（也称为数据库），而不会失去性能/良好的设计？

Question

im in the beginning of the complete restyle of an my web application, and i have some doubt about a good database-design that can be reliable, query-performance, and in the same time fully customizable by the users (users wont customize the database structure, but the funcionality of the application).

So, my actual situation is, for example, a simple user's table:

id | name | surname | nickname | email       | phone
1  | foo  | bar     | foobar   | [e-mail] | 99999

Thats it.

But, lets say that one of my customer would like to have 2 email addresses, or phone numbers, for one specific user.

Untill now, i used to solve that problem simply adding columns in the users table:

id | name | surname | nickname | email       | phone | email_two    | phone_two
1  | foo  | bar     | foobar   | [e-mail] | 99999 | [e-mail]  | 999998

But i cant use that way with the new application's version.. i'll like to be drinking mojito after that, dont like costumer's call to edit the structure :)

So, i thought a solution where people can define customs field, simply with another table:

id | table_refer | type_field | id_object | value
1  | users       | phone      | 1         | 999998
2  | users       | email      | 1         | [e-mail]

keeping the users table unaltered.

But this way have 2 problems:

1. For what i know, there is no possibility to use foreigns key in that way, that if i delete 1 user automatically the foreign key delete in cascade all the row in the second table that have the 'table_refer' value=users and the id_object=users.id. Sure, i'll can use some triggers function, but i'll lose some of the reliability.
2. When i'll need to query the database, fore retrieve the users that match '[e-mail]', i'll have to check all the... hem.. option_table as well, and that will make my code complex and less-reliable and messy with many joins.. assuming that the users table wont be the only one 'extended' by the 'option_table', seem to be a gray view.

My goal is to let my customers adding as many custom fields as they need, for almost all the object in the application (users, items, invoices, print views, photos, news, etc...), assuming that most of those table would be partitioned (splitted in 2 table, with a 3 table and inheritance gerarchy).

You think my way can be good, do you know some other better, or am i in a big mistake? Please, every suggest is gold now!

EDIT:

What i'm lookin for could be simplifyed ith the 'articles-custom-fields' in wordpress blogs. My goal is to let the user to define new fields that he needs, for example, if my users table is the one above, and a customer need a field that i havent prevent, like the web-site url, he must be able to add it dinamically, without edit the database structure, but just the data.

I think that the 2° table (maibe 1 for each object) can be a good solution, but i am still waiting for better ways!

Answer 1

As I said in my Answer to a similar question, "Database Design is Hard." You are going to have to make the decision about which is better for you, normalizing the tables and bringing phone numbers and e-mail addresses into their own tables, with the associated JOIN-ing to retrieve the data, and the extra effort of referential integrity, or having some number n e-mail and phone fields in your table, and the "data-messiness" that that entails.

Database design is always a series of tradeoffs. You need to look at all angles, maybe bodge up some prototypes and do some profiling, etc. There is no "One True Answer™".