I have 2 tables. One stores nothing by email address along with a unique ID. Both fields are unique. I have another table which stores data about a person. Name, Phone, Address, Occupation, etc. There are a fixed amount of fields in the person table. I believe 10 is the number.
我有2张桌子。一个人不会通过电子邮件地址和唯一ID存储任何内容。这两个领域都是独特的我有另一个表存储有关一个人的数据。姓名,电话,地址,职业等人员表中有固定数量的字段。我相信10是数字。
The reason for this setup is that I track changes to an individual person by email, as this is the only unique piece of information I have about someone. These individuals may post via several methods to my site with no long in. Depending on where they post and what method, I collect certain data. Data could change over time. People move, change phone numbers or occupations. So I have several records to match up to an email so that I can display the changes over time.
这种设置的原因是我通过电子邮件跟踪对个人的更改,因为这是我对某人的唯一独特信息。这些人可以通过几种方法发布到我的网站,不用多久。根据他们发布的位置和方法,我收集某些数据。数据可能会随时间而变化。人们移动,更改电话号码或职业。所以我有几条记录可以匹配一封电子邮件,这样我就可以随时间显示变化。
My question is, which is a better setup with MySQL. I'm sure this is a loaded question. Should I have:
我的问题是,这是一个更好的MySQL设置。我确定这是一个有问题的问题。我应该:
ID | EmailID | Name | Phone | Address | City | Created | etc....
Or
ID | EmailID | FieldType | Value | Created
The first setup obviously stores all data for a person along with an associated emailID in one row. The second stores data field by field. So I could insert a row with the FieldType occupation and the value developer under and emailID, as opposed to the whole data set.
第一个设置显然存储了一个人的所有数据以及一行中的关联emailID。第二个按字段存储数据。因此,我可以插入一个具有FieldType占用的行和值为developer的email和emailID,而不是整个数据集。
Since not all data is required on each insertion, I feel that the second method is best, but I am not 100% sure. With the second method, I am only adding a row and inserting what I need to, as opposed to the whole row.
由于并非每次插入都需要所有数据,我觉得第二种方法是最好的,但我不是100%肯定。使用第二种方法,我只添加一行并插入我需要的内容,而不是整行。
Hope this makes sense.
希望这是有道理的。
1 个解决方案
#1
1
The second method is called entity-attribute-value modeling (EAV).
第二种方法称为实体 - 属性 - 值建模(EAV)。
In general, the first method is preferred. Among the problems with EAV is the typing of columns. For instance, one of your columns is created, which is presumably a date column. The others seem to be strings.
通常,第一种方法是优选的。 EAV的问题之一是列的输入。例如,您的一个列已创建,可能是日期列。其他似乎是字符串。
With the EAV structure, it is difficult to maintain foreign key relationships. It is harder to assign constraints on row values. Queries to return all values from a row require bringing together multiple rows. And, because the entity information has to be repeated (the email is your case), they often take up more space.
使用EAV结构,很难保持外键关系。在行值上分配约束更加困难。从一行返回所有值的查询需要将多行组合在一起。并且,由于必须重复实体信息(电子邮件是您的情况),它们通常会占用更多空间。
There are some situations where EAV is the right solution. However, I would encourage you to go with your first structure where all the columns are in a single table. Having NULL values is not a problem in terms of performance or in terms of inserting data.
在某些情况下,EAV是正确的解决方案。但是,我建议您使用第一个结构,其中所有列都在一个表中。具有NULL值在性能方面或插入数据方面不是问题。
#1
1
The second method is called entity-attribute-value modeling (EAV).
第二种方法称为实体 - 属性 - 值建模(EAV)。
In general, the first method is preferred. Among the problems with EAV is the typing of columns. For instance, one of your columns is created, which is presumably a date column. The others seem to be strings.
通常,第一种方法是优选的。 EAV的问题之一是列的输入。例如,您的一个列已创建,可能是日期列。其他似乎是字符串。
With the EAV structure, it is difficult to maintain foreign key relationships. It is harder to assign constraints on row values. Queries to return all values from a row require bringing together multiple rows. And, because the entity information has to be repeated (the email is your case), they often take up more space.
使用EAV结构,很难保持外键关系。在行值上分配约束更加困难。从一行返回所有值的查询需要将多行组合在一起。并且,由于必须重复实体信息(电子邮件是您的情况),它们通常会占用更多空间。
There are some situations where EAV is the right solution. However, I would encourage you to go with your first structure where all the columns are in a single table. Having NULL values is not a problem in terms of performance or in terms of inserting data.
在某些情况下,EAV是正确的解决方案。但是,我建议您使用第一个结构,其中所有列都在一个表中。具有NULL值在性能方面或插入数据方面不是问题。