如何为用户定义的字段设计SQL数据库？ [英] How to design a SQL database for User Defined Fields?

问题描述

我构建Project，让客户端能够在表单中添加新的部分，新字段，

需要能够动态添加任何数据类型的用户定义字段，如date， boolean或text



有很多选项：



1）创建一个大表与StringValue1，StringValue2 ... IntValue1，IntValue2，...等我讨厌这个想法，但如果有人能告诉我它比其他想法更好，为什么会考虑它。



2）创建一个动态表，根据需要按需添加新列。我也不喜欢这个想法，因为除非你索引每一栏，否则我觉得表现会很慢。



3）还有别的吗？



任何帮助

解决方案

不确定你的意思...



在极少数情况下，我不得不设计一个表，以后可能需要额外的列，我通常做一些像添加： -



user-field- 1文本值文本

..

user-field-5-text-value text

user-field-1-int- value int

..

user-field-5-int-value int



的值（并允许空值），然后为每个用户字段保存值代表什么的相应文本字段，所以..



user-field-1-文字说明文字

..

user-field-1-int-description text

..


对于说SQLite来说是
- 如果你认为你可能需要用户日期f ields，然后添加一些那些



我看到商业产品做同样的事情，最终我认为'打字'字段更好强迫转换，但我不确定这是一个很好的方式...

I build Project that give the client the power to add new section,new field in form,
Need to be able to dynamically add User-Defined fields of any data type Like "date", "boolean" or "text"

There are many Options :

1) Create a big table with StringValue1, StringValue2... IntValue1, IntValue2,... etc. I hate this idea, but will consider it if someone can tell me it is better than other ideas and why.

2) Create a dynamic table which adds a new column on demand as needed. I also don't like this idea since I feel performance would be slow unless you indexed every column.

3) Something else?

Any Help

解决方案

not sure exactely what you mean ...

On the rare occasion I have had to design a table, that may require extra columns later on, I usually do something like add :-

user-field-1-text-value text
..
user-field-5-text-value text
user-field-1-int-value int
..
user-field-5-int-value int

for the values (and allow null values), then have a corresponding text field for each user field that holds 'what the value represents', so ..

user-field-1-text-description text
..
user-field-1-int-description text
..

for say SQLite - if you think you might need user date fields, then add a number of those

I have seen commercial products that do the same sort of thing, ultimately I think 'typing' the fields is better that forcing converts, but Im not sure its a great way of doing it ...

[Edit : it really, really, depends on your circumstances/requirements, and how much flexibility you need - but it occurs to be you could define a 'blob' (or clob) type field, and do the same sort of thing with/by storing JSON in that field - means a lot more work though /Edit]

这篇关于如何为用户定义的字段设计SQL数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！