I use a model similar in nature to what Richard Harrison posted.
Your code table can simplify matters if you have a handful of states specific to a subset of tables, but if these states aren't shared or needed by other parts of your database, then you risk making the code table even more confusing by storing everything in one place. This is a judgment call you'll have to make for yourself -- in my current application, I use one centralized Code table because the database is so small.
As for what you store here: I only keep records in the database of data that might change based on business needs. Some examples of this:
Shipping destinations (you might only ship to a few states in the US, or a handful of countries in a particular region, etc.)
Order status codes.
Application roles.
If you anticipate changing the answer to a "sex" question from only M/F to Male, Female, Transgendered (or something like that, although strictly speaking we're now talking about gender rather than sex), or accommodating other genders, then I'd suggest including it in your database. Otherwise, for something that's as close to permanent as you can get, don't store it in the database.
Basic design is simple, you have a Parameters table that has a 1-many relationship with each table that needs a parametrised field. It looks something like this:
Expanding on this to provide a real example, where we are working with a users table that contains a status field. We index and link the field to the params table via a constraint as follows;
INDEX `FK_user_status` (`status`),
CONSTRAINT `FK_user_status` FOREIGN KEY (`status`) REFERENCES `params` (`id`)
ON UPDATE CASCADE ON DELETE CASCADE
NOTE: I'm using CASCADE here, there are times when you don't want to do this
This gives us the following schema;
The key concept of this is to allow the database to contain parameterised data that maintains referential integrity, and integrates with a data model within the code. The code is able to find out by querying the database how entities are related, and for example what the valid values for a specific field are.
Lastly I want to introduce and explain the concept of Parameters Tuples. This is another table that allows us to associate a pair of parameters (the Tuple) with a value. This a data neutral way in which we can extend PET provide the lookup and expected values. This is most suited to an extensible model where it is possible to add new enumerations, and yet we need to allow them to contain a value. It is often better to do this with *relationships*
I'm not in favour of enums in databases, but this is only my opinion and it may be something that you're happy with.
Most applications use an "enumeration". How you use these enums are wholly dependant on how the application is implemented.
Unless you are catering for more than just "M" and "F" genders, I'd put those two options in their own table.
You would "envelope" parameters in subsequent layers by writing a binding, which may or may not use some kind of marshalling. This is wholly dependant on how you implement the application.
Maybe you are looking for what is called a Dimension table. This is related to SCDs. One of these could be your "single parameter table". With time you might find it's better to have multiple tables (sex, location, etc.)
发布评论
评论（4）
我使用的模型本质上与理查德·哈里森发布的模型相似。
如果您有少数特定于表子集的状态，但如果这些状态不是如果数据库的其他部分不共享或不需要，那么您可能会将所有内容存储在一个位置，从而使代码表变得更加混乱。这是您必须自己做出的判断——在我当前的应用程序中，我使用一个集中式代码表，因为数据库很小。
至于你在这里存储的内容：我只在数据库中记录可能会根据业务需求而变化的数据。一些示例：
如果您预计将“性别”问题的答案从仅限男性/女性更改为男性、女性、跨性别（或类似的内容，尽管严格来说我们现在谈论的是性别而不是性别），或者适应其他性别，那么我建议将其包含在您的数据库中。否则，对于尽可能接近永久的东西，不要将其存储在数据库中。
I use a model similar in nature to what Richard Harrison posted.
Your code table can simplify matters if you have a handful of states specific to a subset of tables, but if these states aren't shared or needed by other parts of your database, then you risk making the code table even more confusing by storing everything in one place. This is a judgment call you'll have to make for yourself -- in my current application, I use one centralized Code table because the database is so small.
As for what you store here: I only keep records in the database of data that might change based on business needs. Some examples of this:
If you anticipate changing the answer to a "sex" question from only M/F to Male, Female, Transgendered (or something like that, although strictly speaking we're now talking about gender rather than sex), or accommodating other genders, then I'd suggest including it in your database. Otherwise, for something that's as close to permanent as you can get, don't store it in the database.
我已经多次构建并使用了我所说的参数枚举表。 模块是我的 ZXAF 开源框架的一部分。
基本设计很简单，您有一个
Parameters表，它与每个需要参数化字段的表具有一对多关系。它看起来像这样：对此进行扩展以提供一个真实的示例，我们正在使用
users表包含一个status字段。我们通过约束将该字段索引并链接到params表，如下所示；注意：我在这里使用
CASCADE，有时您不想这样做这为我们提供了以下架构；
其关键概念是允许数据库包含保持引用完整性的参数化数据，并与数据集成代码中的模型。该代码能够通过查询数据库来找出实体如何相关，例如特定字段的有效值是什么。
最后我想介绍和解释参数的概念元组。这是另一个表，它允许我们将一对参数（元组）与一个值关联起来。这是一种数据中立的方式，我们可以通过这种方式扩展 PET 来提供查找和预期值。这最适合可扩展模型，可以添加新的枚举，但我们需要允许它们包含一个值。 通常最好使用 *relationships* 来做到这一点
我不是支持数据库中的枚举，但是这只是我的意见，也许你会感到满意。
I've built and used many times what I call Parameter Enumeration Tables. The module is part of my ZXAF opensource framework.
Basic design is simple, you have a
Parameterstable that has a 1-many relationship with each table that needs a parametrised field. It looks something like this:Expanding on this to provide a real example, where we are working with a
userstable that contains astatusfield. We index and link the field to theparamstable via a constraint as follows;NOTE: I'm using
CASCADEhere, there are times when you don't want to do thisThis gives us the following schema;
The key concept of this is to allow the database to contain parameterised data that maintains referential integrity, and integrates with a data model within the code. The code is able to find out by querying the database how entities are related, and for example what the valid values for a specific field are.
Lastly I want to introduce and explain the concept of Parameters Tuples. This is another table that allows us to associate a pair of parameters (the Tuple) with a value. This a data neutral way in which we can extend
PETprovide the lookup and expected values. This is most suited to an extensible model where it is possible to add new enumerations, and yet we need to allow them to contain a value. It is often better to do this with *relationships*I'm not in favour of enums in databases, but this is only my opinion and it may be something that you're happy with.
大多数应用程序都使用“枚举”。如何使用这些枚举完全取决于应用程序的实现方式。
除非您不仅仅满足“M”和“F”性别，否则我会将这两个选项放在自己的表中。
您可以通过编写绑定来“封装”后续层中的参数，该绑定可能会也可能不会使用某种编组。这完全取决于您如何实现应用程序。
Most applications use an "enumeration". How you use these enums are wholly dependant on how the application is implemented.
Unless you are catering for more than just "M" and "F" genders, I'd put those two options in their own table.
You would "envelope" parameters in subsequent layers by writing a binding, which may or may not use some kind of marshalling. This is wholly dependant on how you implement the application.
也许您正在寻找所谓的维度表。这与 SCD 有关。其中之一可能是您的“单一参数表”。随着时间的推移，您可能会发现拥有多个表（性别、位置等）会更好。
Maybe you are looking for what is called a Dimension table. This is related to SCDs. One of these could be your "single parameter table". With time you might find it's better to have multiple tables (sex, location, etc.)