MySQL列与各种类型 [英] MySQL column with various types

问题描述

我似乎经常发现自己想要在MySQL数据库的同一列中存储多个类型的数据（通常是特定的整数和文本）。我知道这是可怕的，但它发生的原因是当我存储人们对调查问卷中提出的答复时。一些问题需要一个整数响应，有些需要一个文本响应，有些需要一个从列表中选择的项目。

过去我采取的方法是：

1. 将所有内容存储为文本，稍后需要转换为int（或任何）。




2. 有两列 - 一个用于文本，一个用于int。那么你只需要在每个响应中每行填一个，并将另一个作为null。




3. 有两个表 - 一个用于文本响应，一个用于整数响应

虽然我并不喜欢任何一种，但我有一种感觉，更好地处理这种情况。

为了更具体地说明，下面是我可能有的表的一个例子：

  CREATE TABLE问题（
 id int（11）NOT NULL auto_increment，
文本VARCHAR（200）NOT NULL default''，
 PRIMARY KEY（'id'）
）
 
 CREATE TABLE响应（
 id int（11）NOT NULL auto_increment，
问题int（11）NOT NULL， 
用户int（11）NOT NULL，
响应VARCHAR（200）NOT NULL默认值'
）
  

或者，如果我使用上面的选项2进行操作：

  CREATE TABLE response b $ b id int（11）NOT NULL auto_increment，
问题int（11）NOT NULL，
用户int（11）NOT NULL，
 text_response VARCHAR（200），
 numeric_response int（11）
）
  

如果我使用选项3， responseInteger表和一个responseText表。

这些是正确的方法，还是我缺少一个明显的替代方法？

解决方案

[选项2]不是最正常化的选项[如@Ray声称]。最标准化将不会有可空字段，显然，选项2将需要在每行上都为空。

在设计的这一点上，您必须考虑使用情况，你会做的查询，你会写的报告。你想同时对所有的数字回答做数学？即WHERE numeric_response不是NULL？可能不太可能。

更有可能的是，平均响应是WHERE Question = 11.在这种情况下，您可以选择INT表或INT列，也不会比其他人更容易做到。

如果你做了两张桌子，你可能会不断地将他们合并在一起，提出一些问题，比如说回复等等。

你能看出你的数据库问题的答案开始如何驱动设计？

I seem to often find myself wanting to store data of more than one type (usually specifically integers and text) in the same column in a MySQL database. I know this is horrible, but the reason it happens is when I'm storing responses that people have made to questions in a questionnaire. Some questions need an integer response, some need a text response and some might be an item selected from a list.

The approaches I've taken in the past have been:

1. Store everything as text and convert to int (or whatever) when needed later.

2. Have two columns - one for text and one for int. Then you just fill one in per row per response, and leave the other one as null.

3. Have two tables - one for text responses and one for integer responses.

I don't really like any of those, though, and I have a feeling there must be a much better way to deal with this kind of situation.

To make it more concrete, here's an example of the tables I might have:

CREATE TABLE question (
  id int(11) NOT NULL auto_increment,
  text VARCHAR(200) NOT NULL default '',
  PRIMARY KEY ('id')
)

CREATE TABLE response (
  id int(11) NOT NULL auto_increment,
  question int (11) NOT NULL,
  user int (11) NOT NULL,
  response VARCHAR(200) NOT NULL default ''
)

or, if I went with using option 2 above:

CREATE TABLE response (
  id int(11) NOT NULL auto_increment,
  question int (11) NOT NULL,
  user int (11) NOT NULL,
  text_response VARCHAR(200),
  numeric_response int(11)
)

and if I used option 3 there'd be a responseInteger table and a responseText table.

Is any of those the right approach, or am I missing an obvious alternative?

解决方案

[Option 2 is] NOT the most normalized option [as @Ray claims]. The most normalized would have no nullable fields and obviously option 2 would require a null on every row.

At this point in your design you have to think about the usage, the queries you'll do, the reports you'll write. Will you want to do math on all of the numeric responses at the same time? i.e. WHERE numeric_response IS NOT NULL? Probably unlikely.

More likely would be, What's the average response WHERE Question = 11. In those cases you can either choose the INT table or the INT column and neither would be easier to do than the other.

If you did do two tables, you'd more than likely be constantly unioning them together for questions like, what % of questions have a response etc.

Can you see how the questions you ask your database to answer start to drive the design?

这篇关于MySQL列与各种类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！