您的问题，主要是由于的设计的数据库。你有，因为你已经翻译这将是一列动态添加列（在3NF ）至排在你的表。显然，这是因为你允许用户添加自己的栏目 - 我的心灵颤抖，但是这是应用程序的工作方式： - ）

Your problem is mainly due to the design of the database. You have to dynamically add columns because you've translated what would be a column (in 3NF) to a row in your tables. Obviously, this is because you allow users to add their own columns - my mind shudders, but that's the way the app works :-).

由于结构 MetaDataColumns 我要假设一个用户定义一组列名的，他们可以再选择应用到的能力因为他们希望单个文档。

Due to the structure of MetaDataColumns I'm going to assume that a user has the ability to define a set of column names that they can then choose to apply to an individual document as they wish.

我认为问题是，在试图正确地正常化的一切，在一个完全反规范化的数据库，你已经成功地使自己很多的麻烦。我的解决办法是denormalise你的表 MetaDataValues​​ 。你不提您所使用的RDBMS，但MySQL有的 4096列或65K字节。 Oracle中的限制是 1000 和1024在SQL Server中。

I think the problem is that in trying to normalise everything properly, in a completely de-normalised database, you've managed to cause yourself a lot of hassle. My solution would be to denormalise your table MetaDataValues. You don't mention what RDBMS you're using but MySQL has a hard limit of 4096 columns or 65k bytes. The limit in Oracle is 1000 and 1024 in SQL Server.

如果您 MetaDataValues​​ 的结构更改为以下，你应该能够适合的至少的332套的信息在里面。这将是对用户名分别独特， DocumentID ，所以你可以，理论上，去除代理键 ID 。

If you change the structure of MetaDataValues to the following you should be able to fit at least 332 sets of information in there. This would be separately unique on UserID, DocumentID so you could, theoretically, remove the surrogate key ID.

MetaDataValues:
ID int
UserID int foreign key
DocumentID int foreign key
KeyName1 varchar
Order1 int
Value1 varchar(100) 
...
KeyNameN varchar
OrderN int
ValueN varchar(100)

当然，这并设置上，你就可以允许个人用户创建332列数的上限;但是，这是正常的限制去疯狂，谁能够想到的元数据的332位独立存储在一个单一的PDF值得以某种方式限制用户的能力。

Of course, this does set an upper-limit on the number of columns that you'll be able to allow an individual user to create to 332; but, it's normal to limit the abilities of users to go insane and anyone who can think of 332 separate bits of metadata to store on a single PDF deserves to be limited somehow.

如果你有特别的信息的用户痴迷于你总是可以声明第二个表具有相同的结构，并保持在加油吧。

If you do have particularly information obsessed users you can always declare a second table with the same structure and keep on filling it in.

这样做将意味着 MetaDataColumns 将不会被用于任何东西，但显示用户选项给他们。你会的有无的更新 MetaDataValues​​ 每次更改，并确保你没有过写现存已有的信息可能会有点时间的痛苦。我怀疑你必须做的是这样选择的记录更新前，通过 KeyName1 .. KeyNameN 迭代>和填充在不具有在它的任何数据的第一个。或者你可以只写一个绝对可怕的SQL查询。无论哪种方式，这将成为瓶颈。

Doing this would mean that MetaDataColumns would not be used for anything but displaying the users options to them. You would have to update MetaDataValues each time a change was made and ensuring that you didn't over-write already extant information may be a little bit of a pain. I would suspect you'd have to do something like selecting the record before you update it, iterating through KeyName1 .. KeyNameN and filling in the first one that doesn't have any data in it. Alternatively you could just write an absolutely horrible SQL query. Either way, this would become the "choke point".

另一种选择将是一个除了列添加到 MetaDataColumns ，这表明了N列有关，但这种将用户限制为332列绝对，而不是332每文档。

The other option would be to add an addition column to MetaDataColumns, which indicated which N the column related to but this restricts a user to 332 columns absolutely rather than 332 per document.

不过，从数据库中选择现在非常轻松：

However, your selection from the database is now insanely easy:

select d.*, m.*
  from DocumentsTable d
  join MetaDataValues m
    on d.ID = m.DocumentID
   and d.UserID = m.UserID
 where d.UserId = ?

有没有必要试图通过表动态生成1000列的select语句进行迭代。所有的信息是正确的，并有方便你。

There's no need to try to iterate through tables dynamically generating 1,000 column select statements. All the information is right there and easily accessible for you.

在这一天结束的正确回答你的问题取决于你要花费的时间。你想它采取半秒更长的时间来创建或更新文档或半秒（可能更多）选择在该文件中的信息。

At the end of the day the "correct" answer to your question depends on where you want to spend the time. Do you want it to take half a second longer to create or update a document or half a second (probably more) to select the information on that document.

我个人认为，用户理解，创造的东​​西需要时间，但没有什么比不必等待时代看到的东西显得更烦人。

Personally, I think users understand that creating something takes time but there's nothing more annoying than having to wait ages to see something appear.

还有一个社会，而不是数据库解决方案。不要让你的用户创建自己的列。挑选用户希望的元数据的最常见的片，并在数据库中的一个归一化的形式正确地创建它们。你能够创建具有正确数据类型的列（其中的将会的为您节省大量的长远麻烦），并有它的容易得多。我怀疑你会很幸运地有这种情况发生;但它是值得铭记。

There is another, social, rather than database solution. Don't allow your users to create their own columns. Pick the most common pieces of metadata that your users want and create them properly in a normalised form in the database. You'd be able to create columns with the correct data-type (which will save you a lot of hassle in the long run) and have a much easier time of it. I doubt you'll be lucky enough to have that happen; but it's worth bearing in mind.