避免使用动态表名，但如何？ [英] Avoid using dynamic table names, but how?

问题描述

我是数据库设计的新手，我正在努力尝试早期的最佳实践。到目前为止，我看到他们似乎是一个协议，你在开始开发时创建表名，并插入您的数据，不要使用变量表名称。我一直在寻找，一个资产管理系统，我不能超越似乎是一个设计缺陷。如果你熟悉，这个软件将非常类似于quickbase。

I am new to database design and I'm trying to get into the best practices early. So far everywhere I look their seems to be an agreement that you "Create the table names at the start of development, and insert your data, do not use variables for table names. Well I was looking to make an asset management system and I can't get past what seems like a design flaw. This software would be very similar to "quickbase" if you're familiar.

• 用户可以创建帐户，每个帐户都在一个帐户表中，其中的数据链接回到其他相关


• 
  
• 每个帐户中有足够多的类别，需要跟踪信息，例如：
  
  
  
  • 财务
  
  
  • 行程
  
  
  • 任务
  
  
  • 等
  
  
  • User can create accounts, each account is in a table of accounts that has data inside that links back to the other related tables.
  • Inside each account there is as many catagories as they need to track info such as:
    • Financials
    • Trips
    • Tasks
    • etc

    现在，这些类别中的每一个都包含大量信息，并且需要它自己的表。显然，我可以使用关系将这些表连接到帐户，他们是一个问题（我想）。用户可以创建尽可能多的帐户，因为他们需要的每一次，他们的需要是一些与它一起创建的其他表。例如account_name_financials_table等。

    Now each one of these categories would hold a significant amount of information and would require it's own table. Obviously I could use relationships to connect these tables back to the account but their is a problem (I think). The user can create as many accounts as they need and every time they do their will need to be a number of other tables created along with it. Such as "account_name_financials_table" etc.

    所以我的问题是：在动态创建它们时，可以不使用表名称的变量。 或是这种情况下，在创建表名时使用变量是可以接受的。

    So my question is: Can this be possible to do without using variables for table names when dynamically creating them. OR is this a scenario where it would be considered acceptable to use variables for table names when creating them.

    如果这太局限或模糊，请让我知道，我会提供任何进一步的详细信息。

    If this is too localized or vague please let me know and I will provide any further details needed.

    感谢您的时间

    EDIT

    为了回应Davids的真棒答案。

    In response to Davids awesome answer.

    我完全理解，现在每个帐户实际上只是一个条目，将保存每个帐户的信息。然后列名称将描述描述我的帐户的数据，并随后与其他表相关。

    I fully understand now that each account is actually just an entry, or a row into one table that would hold the information for each account. The column names would then describe the data that describes my account and subsequently relates to the other tables.

    现在这个问题的核心是，当我创建帐户时，还有其他需要创建的表，或至少我认为他们是这样。这将是一个财务表，例如持有该帐户财务的具体信息。这些表上有一对一的关系。含义帐户A对于特定帐户只能有1组财务。因此，当我创建帐户B时，我目前拥有的财务表不再具有帐户B的相关列名称，行或数据。因此，当我为帐户B创建这些新表时，它们更可能需要动态完成。有了这个说，我将如何创建他们没有变量名？

    Now the core of this problem is that when I create the account there are other tables that need to be created, or at least I think they do. This would be a financials table for instance that holds specific information about that accounts financials. There is a one to one relationship on these tables. Meaning Account A can only have 1 set of financials for the specific account. So when I create Account B the financials table I currently have would no longer have relevant column names, rows, or data for Account B. So when I create these new tables for Account B, they would more then likely need to be done dynamically. With this being said, how would I create them without variable names?

    或者我还是缺少点。一个想法发生在我，也许我可以有一个大的财务表，保存所有帐户的信息，该信息被定义/相关的特定帐户的'id'或一些其他独特的fk pk标识符？

    Or perhaps I'm still missing the point. A thought occurs to me that perhaps I could have one large financial table that holds information for all accounts and that information is defined/related back to the specific account by 'id' or some other unique fk pk identifier?

    推荐答案

    不要将表视为您域中的实体，将其视为对实体描述的定义以及该实体中的每个记录表是该实体的实例。

    Don't think of a table as an entity in your domain, think of it as a definition of the what describes an entity and each record in that table is an instance of that entity.

    鉴于此，此语句不正确：

    Given that, this statement becomes incorrect:

    
    

    用户可以创建帐户，每个帐户都是自己的表格。

    User can create accounts, each account is its own table.

    帐户（或 Accounts ，具体取决于您的命名约定）表是描述帐户实体的原理定义。它可能有列，如帐户的名称，注册日期，类似的东西。表中 Account 表中的每个记录都是一个帐户。因此，会有一个 Account 表，其中包含域中的所有帐户。

    The Account (or Accounts, depending on your naming conventions) table is the schematic definition of what describes an Account entity. It might have columns like the Account's name, the date it registered, things like that. Each record in the Account table is an Account. So there would be one Account table which holds all of the Accounts in the domain.

    
    

    
    

    Inside each account there is 4 - 8 catagories they can track

    这种说法似乎有点违反直觉来描述关系数据。如何：

    That statement seems a little counterintuitive to describe relational data. How about:

    
    

    每个帐户也可以有其他相关的数据，例如...

    Each Account can also have additional data related to it, such as...

    现在，下一行不是描述帐户中的内部，而是域中与相关的另一个实体帐户：

    And now the next lines aren't describing something that's "inside" an Account, but rather another entity in the domain which happens to relate to an Account:

    
    
    • 财务
    
    
    • 行程
    
    
    
    
    • 等。
    
    
    • Financials
    • Trips
    • Tasks
    • etc.

    这些都是自己的实体无论如何，目前的术语有点模糊），其不一定描述一个帐户，但它们自己由 帐户描述。也就是说，帐户不指向任务。任务指向帐户。帐户本身是描述任务的数据点。

    Each of these is an entity in its own right (or can be anyway, the terms currently are a little vague) which don't necessarily describe an Account, but themselves are described by an Account. That is, an Account doesn't point to a Task. A Task points to an Account. The Account itself is a data point which describes the Task.

    因此，它们中的每一个都成为自己的表，其中记录具有外键返回到 Account 表。这么简单：

    Thus, each of these becomes tables in their own right, in which the records have foreign keys back to the Account table. Something as simple as this:

    Task
    ----------------------
    ID          | int
    AccountID   | int
    Description | nvarchar
    ScheduledOn | datetime
    

    等等。该表中的每个记录（每个记录代表域中的一个任务）将包括 AccountID 值，其指示哪个帐户拥有该任务。

    And so on. Each record in that table (each record represents one Task in the domain) would include an AccountID value which indicates which Account owns that Task.

    这些表应该特定于实体的任何特定子集，但应该是该特定实体的所有实例的通用表。

    The tables should be specific to any particular subset of entities, but should be general to all instances of that particular entity.

    您可以进一步自定义实体通过子类型表转换为不同类型。例如，也许你有不同种类的帐户，有不同的字段描述它们。您可能有一个单独的 Account 表，但是会有额外的表，其中主键也是帐户 table。这些表将表示与帐户表的一对一关系，并且可以向帐户的子集添加更多值。这样的表，例如：

    You can further customize entities into different types by sub-typing tables. For example, maybe you have different kinds of Accounts and there are different fields which describe them. You might have a single Account table as above, but would have additional tables where the primary key is also the foreign key to the Account table. These tables would represent one-to-one relationships to the Account table and could add more values to a "subset" of Accounts. A table like this, for example:

    PreferredAccount
    ---------------------
    AccountID       | int (PK, also FK to Account.ID)
    PreferredStatus
    BecamePreferredOn
    

    等等。这样，您就可以按类型逻辑分隔帐户，而无需创建多个表格来表示帐户本身。

    And so on. That way you can logically separate your Accounts by type without having to create multiple tables to represent an Account itself.

    要回到问题... ，当用户创建帐户时，他们不会更改数据库的模式。他们只是在帐户表中添加记录。

    To get back to the question... Ultimately, when a user creates an Account, they're not changing the schema of the database. They're simply adding a record to the Account table.

    这篇关于避免使用动态表名，但如何？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！