How would you design your database to allow user-defined schema

Let's try again.

If you want them to be able to create their own schema, then why not build the schema using, oh, I dunno, the CREATE TABLE statment. You have a full boat, full functional, powerful database that can do amazing things like define schemas and store data. Why not use it?

If you were just going to do some ad-hoc properties, then sure.

But if it's "carte blanche, they can do whatever they want", then let them.

Do they have to know SQL? Umm, no. That's your UIs task. Your job as a tool and application designer is to hide the implementation from the user. So present lists of fields, lines and arrows if you want relationships, etc. Whatever.

Folks have been making "end user", "simple" database tools for years.

"What if they want to add a column?" Then add a column, databases do that, most good ones at least. If not, create the new table, copy the old data, drop the old one.

"What if they want to delete a column?" See above. If yours can't remove columns, then remove it from the logical view of the user so it looks like it's deleted.

"What if they have eleventy zillion rows of data?" Then they have a eleventy zillion rows of data and operations take eleventy zillion times longer than if they had 1 row of data. If they have eleventy zillion rows of data, they probably shouldn't be using your system for this anyway.

The fascination of "Implementing databases on databases" eludes me.

"I have Oracle here, how can I offer less features and make is slower for the user??"

Gee, I wonder.

There's no way you can predict how complex their data requirements will be. Entity-Attribute-Value is one typical solution many programmers use, but it might be be sufficient, for instance if the user's data would conventionally be modeled with multiple tables.

I'd serialize the user's custom data as XML or YAML or JSON or similar semi-structured format, and save it in a text BLOB.

You can even create inverted indexes so you can look up specific values among the attributes in your BLOB. See http://bret.appspot.com/entry/how-friendfeed-uses-mysql (the technique works in any RDBMS, not just MySQL).

Also consider using a document store such as Solr or MongoDB. These technologies do not need to conform to relational database conventions. You can add new attributes to any document at runtime, without needing to redefine the schema. But it's a tradeoff -- having no schema means your app can't depend on documents/rows being similar throughout the collection.

I'm a critic of the Entity-Attribute-Value anti-pattern.

I've written about EAV problems in my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

Here's an SO answer where I list some problems with Entity-Attribute-Value: "Product table, many kinds of products, each product has many parameters."

Here's a blog I posted the other day with some more discussion of EAV problems: "EAV FAIL."

And be sure to read this blog "Bad CaRMa" about how attempting to make a fully flexible database nearly destroyed a company.

I would go for a Hybrid Entity-Attribute-Value model, so like Antony's reply, you have EAV tables, but you also have default columns (and class properties) which will always exist.

Here's a great article on what you're in for :)

As an additional comment, I knocked up a prototype for this approach using Linq2Sql in a few days, and it was a workable solution. Given that you've mentioned Entity Framework, I'd take a look at version 4 and their POCO support, since this would be a good way to inject a hybrid EAV model without polluting your EF schema.