规范现有的MS Access数据库 [英] Normalizing an existing MS Access Database

问题描述

我有一个大型访问数据库，需要将其规范化为五个表和一个查找表.我了解规范化背后的理论，并且已经草绘了表的外观，但是我迷失在如何转换表以使数据库规范化方面.表分析器不提供我想要的细分.

I have one large access database that I need to normalize into five tables and a lookup table. I understand the theory behind normalization and have already sketched out the look of the tables but I am lost on how to transform my table to get the database normalized. The table analyzers doesn't offer the the breakdown that I want.

推荐答案

如果只有一个表，则向其中添加一个自动编号字段.

If you have a single table, add an Autonumber field to it.

然后创建您的其他表，并使用原始单个表中的自动编号"值作为外键将它们重新连接回原始数据.

Then create your other tables, and use the Autonumber value from the original single table as the foreign key to join them back to the original data.

如果您有tblPerson:

If you had tblPerson:

  tblPerson
  LastName, FirstName, WorkPhone, HomePhone

您想对其进行分解，添加PersonID自动编号，然后创建电话表:

and you wanted to break it down, add PersonID autonumber and then create a phone table:

  tblPhone
  PhoneID, PersonID, PhoneNumber, Type

然后，您将从tblPerson的数据附加到适当的字段:

Then you'd append data from tblPerson for the appropriate fields:

  INSERT INTO tblPhone (PersonID, PhoneNumber, Type)
  SELECT tblPerson.PersonID, tblPerson.WorkPhone, "Work"
  FROM tblPerson
  WHERE tblPerson.WorkPhone Is Not Null;

，然后您将对家用电话运行另一个查询:

and then you'd run another query for the home phone:

  INSERT INTO tblPhone (PersonID, PhoneNumber, Type)
  SELECT tblPerson.PersonID, tblPerson.HomePhone, "Home"
  FROM tblPerson
  WHERE tblPerson.HomePhone Is Not Null;

有人建议使用UNION查询，因为在Jet SQL中不能将UNION查询作为子选择，因此您必须保存该查询.保存的查询如下所示:

Someone suggested a UNION query, which you'd have to save as you can't have a UNION query as a subselect in Jet SQL. The saved query would look something like this:

  SELECT tblPerson.PersonID, tblPerson.WorkPhone, "Work" As Type
  FROM tblPerson
  WHERE tblPerson.WorkPhone Is Not Null
  UNION ALL 
  SELECT tblPerson.PersonID, tblPerson.HomePhone, "Home" As Type
  FROM tblPerson
  WHERE tblPerson.HomePhone Is Not Null;

如果将其另存为qryPhones，则可以使用以下SQL附加qryPhones:

If you saved that as qryPhones, you'd then append qryPhones with this SQL:

  INSERT INTO tblPhone (PersonID, PhoneNumber, Type)
  SELECT qryPhones.PersonID, qryPhones.WorkPhone, qryPhones.Type
  FROM qryPhones;

显然，这只是最简单的示例.您将对所有字段执行相同的操作.关键是您必须为源表创建一个PK值，以将所有派生的记录重新绑定到原始表.

Obviously, this is just the simplest example. You'd do the same for all the fields. The key is that you have to create a PK value for your source table that will tie all the derived records back to the original table.

这篇关于规范现有的MS Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！