In a nutshell here's the situation, we have a database that is used to build a hierarchy of "locations". (Example: Street Address > Building 1 > First Floor > Room).
简而言之,在这种情况下,我们有一个用于构建“位置”层次结构的数据库。 (例如:街道地址> 1号楼> 1楼>房间)。
Each of the locations are stored in a table. Each location can be of a different "type". The types are defined in another table. (We use the types of locations to restrict what locations can be added to a location).
每个位置都存储在一个表中。每个位置可以是不同的“类型”。类型在另一个表中定义。 (我们使用位置类型来限制可以添加到位置的位置)。
Here's the quandary we are facing: We need to be able to store different types of information for different types of locations. For example, a location type of "building" may need to have the address stored where as a location type of "room" may need to have dimensions or paint color stored.
这是我们面临的困境:我们需要能够为不同类型的位置存储不同类型的信息。例如,位置类型的“建筑物”可能需要存储地址,其中“房间”的位置类型可能需要存储尺寸或涂料颜色。
Obviously we could create a table for each location type we define to hold the properties required for the particular location type and then use application logic to query the appropriate table to pull in a particular location's additional information. Is there a more elegant or practical way to accomplish this relationally in the database without having to rely on application logic?
显然,我们可以为我们定义的每个位置类型创建一个表,以保存特定位置类型所需的属性,然后使用应用程序逻辑查询相应的表以提取特定位置的附加信息。有没有更优雅或实用的方法在数据库中实现这种关系,而不必依赖于应用程序逻辑?
Thanks!
1 个解决方案
#1
0
The relationally pure way to do this would be to implement your initial suggestion i.e. have a separate table such as BUILDING_LOCATIONS that holds the attributes that are only applicable to this type of location. A different [TYPE]_LOCATIONS table would be created for each type of location that has its own attributes. In this way you could use standard database constraint functionality to ensure the integrity of the data in these tables.
执行此操作的相对纯粹的方法是实现您的初始建议,即具有单独的表,例如BUILDING_LOCATIONS,其中包含仅适用于此类位置的属性。将为具有自己的属性的每种类型的位置创建不同的[TYPE] _LOCATIONS表。通过这种方式,您可以使用标准数据库约束功能来确保这些表中数据的完整性。
Another method would be to add a series of nullable columns to the LOCATIONS table such as BUILDING_ADDRESS and ROOM_DIMENSIONS. This is not relationally pure as it means null values can exist in this table. However, you can still use standard database functionality to ensure the integrity of the data. It can be a bit more convoluted if certain values are mandatory in certain situations. Also, if there are many types of location with many differing attributes the number of columns in the table can become unwieldy.
另一种方法是向LOCATIONS表中添加一系列可为空的列,例如BUILDING_ADDRESS和ROOM_DIMENSIONS。这不是关系纯粹的,因为它意味着此表中可以存在空值。但是,您仍然可以使用标准数据库功能来确保数据的完整性。如果在某些情况下某些值是强制性的,则可能会更复杂一些。此外,如果有许多类型的位置具有许多不同的属性,则表中的列数可能变得难以处理。
Another method is the Entity-Attribute-Value model. Generally, this is to be avoided if at all possible. It is not relationally pure, as your column values are now no longer defined over domains, and it is extremely difficult, if not impossible, to ensure the integrity of the data. Any real attempt to do so will require a lot of bespoke coding (which needs to be carefully implemented to cater for things like concurrency control that database constraints give you for free) as you cannot use standard database constraints. However, if you are just interested in storing values for information and not doing anything with them you could use this method.
另一种方法是实体 - 属性 - 值模型。通常,如果可能的话,应该避免这种情况。它不是关系纯的,因为现在不再在域上定义列值,并且即使不是不可能,也很难确保数据的完整性。任何真正的尝试都需要大量的定制编码(需要仔细实现以满足数据库约束免费提供的并发控制等),因为您无法使用标准数据库约束。但是,如果您只想存储信息值而不对它们执行任何操作,则可以使用此方法。
The EAV method does have a danger that because it appears so easy to add attributes to an entity, it becomes the default way of doing so. It is then used to add attributes for which vital processing is dependent and, because you cannot ensure the integrity of the data using this method, you find the values being used are meaningless and the whole logical basis for the processing is destroyed.
EAV方法确实存在这样的危险,因为它似乎很容易向实体添加属性,它成为默认的方式。然后,它用于添加重要处理所依赖的属性,并且由于无法使用此方法确保数据的完整性,因此您会发现正在使用的值毫无意义,并且会破坏处理的整个逻辑基础。
#1
0
The relationally pure way to do this would be to implement your initial suggestion i.e. have a separate table such as BUILDING_LOCATIONS that holds the attributes that are only applicable to this type of location. A different [TYPE]_LOCATIONS table would be created for each type of location that has its own attributes. In this way you could use standard database constraint functionality to ensure the integrity of the data in these tables.
执行此操作的相对纯粹的方法是实现您的初始建议,即具有单独的表,例如BUILDING_LOCATIONS,其中包含仅适用于此类位置的属性。将为具有自己的属性的每种类型的位置创建不同的[TYPE] _LOCATIONS表。通过这种方式,您可以使用标准数据库约束功能来确保这些表中数据的完整性。
Another method would be to add a series of nullable columns to the LOCATIONS table such as BUILDING_ADDRESS and ROOM_DIMENSIONS. This is not relationally pure as it means null values can exist in this table. However, you can still use standard database functionality to ensure the integrity of the data. It can be a bit more convoluted if certain values are mandatory in certain situations. Also, if there are many types of location with many differing attributes the number of columns in the table can become unwieldy.
另一种方法是向LOCATIONS表中添加一系列可为空的列,例如BUILDING_ADDRESS和ROOM_DIMENSIONS。这不是关系纯粹的,因为它意味着此表中可以存在空值。但是,您仍然可以使用标准数据库功能来确保数据的完整性。如果在某些情况下某些值是强制性的,则可能会更复杂一些。此外,如果有许多类型的位置具有许多不同的属性,则表中的列数可能变得难以处理。
Another method is the Entity-Attribute-Value model. Generally, this is to be avoided if at all possible. It is not relationally pure, as your column values are now no longer defined over domains, and it is extremely difficult, if not impossible, to ensure the integrity of the data. Any real attempt to do so will require a lot of bespoke coding (which needs to be carefully implemented to cater for things like concurrency control that database constraints give you for free) as you cannot use standard database constraints. However, if you are just interested in storing values for information and not doing anything with them you could use this method.
另一种方法是实体 - 属性 - 值模型。通常,如果可能的话,应该避免这种情况。它不是关系纯的,因为现在不再在域上定义列值,并且即使不是不可能,也很难确保数据的完整性。任何真正的尝试都需要大量的定制编码(需要仔细实现以满足数据库约束免费提供的并发控制等),因为您无法使用标准数据库约束。但是,如果您只想存储信息值而不对它们执行任何操作,则可以使用此方法。
The EAV method does have a danger that because it appears so easy to add attributes to an entity, it becomes the default way of doing so. It is then used to add attributes for which vital processing is dependent and, because you cannot ensure the integrity of the data using this method, you find the values being used are meaningless and the whole logical basis for the processing is destroyed.
EAV方法确实存在这样的危险,因为它似乎很容易向实体添加属性,它成为默认的方式。然后,它用于添加重要处理所依赖的属性,并且由于无法使用此方法确保数据的完整性,因此您会发现正在使用的值毫无意义,并且会破坏处理的整个逻辑基础。