没有1列表的数据库设计 [英] Database design without a 1 column table

问题描述

我一直在进行数据库设计，但遇到了麻烦.我最后读到的不是规范化的数据库结构，但是在尝试找到更正确的"设计时遇到了问题，如果可以接受该设计，该如何在Access中执行它?

I have been working on a database design and I'm stuck hitting a wall. I'm ending up with what I'm reading is not a normalized database structure but I'm having issues trying to find a "more correct" design and if this design is acceptable how do I execute it in Access?

TLDR:如果将具有单列设置为自动编号的表是可以接受的设计，那么如何使用Access在其中插入记录呢?

TLDR: If a table with a single column set as an auto number is an acceptable design, how do you go about inserting a record in it using Access?

关注的数据库部分正在创建用于存储公司的结构.这样做的要求是，任何更改都需要由另一位用户批准，并且所有历史更改都必须被捕获，以便可以轻松还原，而且公司可以有多个别名，但只有一个法定名称.

The segment of the database of concern is creating a structure for storing companies. Requirements for this is that any changes need to be approved by another user and all historical changes need to be captured so that it can be easily reverted also a company can have multiple aliases but only one legal name.

我的解决方案中有三个表，但其中一个是单列表.根据我的阅读，有95％的堆栈溢出人员都认为这是一个非常糟糕的主意，但我发现有人发表了这样的观点:存在这种情况.我认为这也不正常，因为我无法找到一种仅在具有自动编号列的表中创建新记录的方法(在Access中我还没有尝试其他方法).

There is three tables in my solution but one of them is a single column table. From what I've read 95% of people on stack overflow all think its a very bad idea but I've found one post were people are that there are cases for it. I think this is not normal also because I can't find a way to just create a new record in a table with only an auto number column (In Access I have not tried others yet).

表结构

1. 公司名称:ID，公司ID，法定名称，创建者，创建者，批准者，批准者，事件ID，有效 (公司可能有几个不同的名称，公众都知道它:TD vs Toronto Dominion.每个名称都在此处插入，以提及其所属的公司).

1. Company Names : ID, Company ID, Is Legal Name, Created By, Created On, Approved On, Approved By, Event ID, Is Active (A company could have a few different names known to the public: TD vs Toronto Dominion. Each name is inserted here with a reference to the company it belongs to)

公司:ID(自动编号) (一家公司存在，这就是它的ID)

Companies : ID (Auto Number) (A company exists and this is its ID)

公司历史记录:ID，公司ID，市场ID，控股公司ID，创建者，创建者，批准者，事件者，活动ID (这些是对公司进行的历史更改，由谁来做，谁被批准了)

Companies History : ID, Company ID, Market ID, Holding Company ID, Created By, Created On, Approved On, Approved By, Event ID, Is Active (These are the historical changes that have been made to the company and who did them and who approved them)

列注释:

事件ID:是对表的FK引用，该表保存已创建，更新或删除的记录的每个动作记录. (用户研究使用方法[y]，Typo Fix等...)

Event ID : is a FK reference to a table holding each record of actions that have either created, updated or deleted records. (User Research using method [y], Typo Fix, ...)

处于活动状态:由于无法删除记录(需要保留历史记录)，此列用于跟踪是否要在查询中包含该记录.

Is Active : Since deleting records is not possible (historical records need to be kept) this column is used to track if this record is to be included in queries.

我看到的选项及其问题:

Options I see and their issues:

我可以摆脱companies表，并将Company History:ID设为新的公司ID，但是我发现在这种情况下，每次我想更新公司时，都需要将每个FK引用都更新为以前的公司ID. (我认为这不是一种非常规范的方法)

I could get rid of the companies table and make Companies History : ID be the new company id but I find that in that case each time I want to update a company I would need to update each FK reference to the previous company id (I don't think this would be a very normalized approach)

我看到的另一个选择是，我摆脱了Companies表，并使用Company Names:ID作为公司ID，然后将一列添加到Company Name中，称为Alias of Company ID.我发现该解决方案给我存储的数据增加了复杂性，其中别名的公司信息与别名条目不同.

Another Option I see is that I get rid of Companies table and use Company Names : ID as the company id and I would add a column to Company Names called Alias of Company ID. I find that solution adds a log of complexity to my stored data where an alias has company information that differs from the entry that was aliased.

另一个选择是，我可以添加以下列:创建者，创建者，批准者，批准者，事件ID和处于活动状态，但这将复制在公司历史记录表中该公司的第一条记录中找到的信息，并且这并没有在该记录中添加任何真实的描述.

Another Option is that I could add the columns: Created By, Created On, Approved On, Approved By, Event ID and Is Active but this would be duplicating information found in the first record for this company in the Companies History table and this isn't adding any real description to this record.

另一种选择是，我使公司"表成为公司历史"的镜像，并且当我在公司"中更新或插入记录时，我还将插入一条公司历史"记录.使用此解决方案，我发现我再次重复了信息，公司历史记录"中的最新记录将保留与公司

Anther Option is that I make the Companies table a mirror of Companies History and that when I update or insert a record in Companies I would also insert a record Companies History. With this solution I find that again I duplicate information, that newest record in "Companies History" would hold the same information found in last Inserted or Updated record in in Companies

另一种选择是用短文本替换Companies:ID自动编号，而我只是得到当前时间戳的哈希值+一个随机整数.现在，我可以使用access将新记录插入到该表中，但是由于我只需要与自动编号完全相同的功能，因此这感觉有些过头了.

Another option but is to replace the Companies : ID auto number with a short text and I just get the hash of the current timestamp + a random int. I can now insert new records into this table using access but I feel that this is overkill since I just need the exact same functionality as the auto number.

另一种选择是仅将法定名称移动到公司"表中，但是现在当公司的法定名称更改时，我无法跟踪它.另外，如果我要列出所有名称，则需要在公司"和公司名称"上使用联合.我发现使用联合会降低查询的性能，并且仅在明确需要时才使用它们.

Another option is move only the legal name into Companies table but now when the legal name of a company changes I have no way of tracking this. Also if I want a list of all names I need to use a union on Companies and Company Names. I find that using unions can reduce performances of queries and I use them only when explicitly needed.

如果我不想重复任何信息并且不想更新所有FK，似乎我需要一个带有单列的表.如果可以接受，我该如何将记录插入到表中，并且在Access中将单列设置为自动编号.

If I don't want to duplicate any information and I don't want to update all FK it seems that I need a table with a single column. If this is acceptable how do I go about inserting a record into a table with a single column set to auto number in Access.

推荐答案

如果可以从CompanyNames(select distinct CompanyId from CompanyNames)派生Companies，则没有必要再次存储该信息.如果需要，只需将其替换为视图即可(但它几乎没有附加值).

If Companies can be derived from CompanyNames (select distinct CompanyId from CompanyNames), there is no point storing again that information. Just replace that table by a view if you want it (but it as little added value).

另一方面，如果CreatedOn指的是公司创建(而不是行创建)，则显然它是公司的财产，我宁愿与
Companies-> Aliases.
但是我当然不知道您正在处理的现实的来龙去脉.

On the other hand, if CreatedOn refers to the Company creation (not the row creation) then it is obviously a property of the Company, and I would rather work with
Companies --> Aliases.
But of course I don't know the ins and outs of the reality you're dealing with.

这篇关于没有1列表的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！