MySQL 大型单表实用规范化
我对 PHP MySQL 比较陌生，并且要求自己通过“实践”方法进行学习。幸运的是，我目前有一个（非常）大的数据库，所有数据库都与硬币数据有关，并且有一个表可供使用。它目前具有以下列（每行代表一个项目 [硬币]）：
Group
ItemNo
ListNo
TypeCode
DenomCode
PeriodCode
ActualDate
SortDate
CostPrice
SalePrice
Estimate
StockLevel
DateEntered
DateSold
Archived
ArchiveWhenSold
Highlight
KeepSold
OnLists
NotForSale
Proof
StockItem
OnWeb
Cats
Ref1
Ref2
Variety
Picture
Description
TypeName
TypeHeading
DenomName
DenomHeading
DenomValue
PeriodName
PeriodHeading
PeriodStartYear
PeriodEndYear
新表的分组相对明显：
期间：
PeriodCode
PeriodName
PeriodHeading
PeriodStartYear
PeriodEndYear
面额：
DenomCode
DenomName
DenomHeading
DenomValue
类型：
TypeCode
TypeName
TypeHeading
其余的，在 Coin 表下：
Group
ItemNo
ListNo
TypeCode
ActualDate
SortDate
CostPrice
SalePrice
Estimate
StockLevel
DateEntered
DateSold
Archived
ArchiveWhenSold
Highlight
KeepSold
OnLists
NotForSale
Proof
StockItem
OnWeb
Cats
Ref1
Ref2
Variety
Picture
Description
所以我希望将表标准化为指定的表。我知道我正在考虑加入，但我想知道最好的方法。我是否首先使用每个数据组（Denom、Period、Type）创建一个新表，然后使用 JOIN 语句插入数据？或者有没有办法使用 JOIN 语句“即时”创建新表。我在这里打开了一本很棒的书，我很好地遵循了有关 MySQL 的部分，也浏览了这个网站，但一直无法找出“正确”的方法来做到这一点。
我在这里询问一些知识渊博的建议的原因是，我有点不确定如何维护“关系”和键等。即，如果我创建一个名为“Denom”的表并用来自所有当前表数据并让它创建一个唯一的主键，然后如何将从 Denom 表中插入对此新主键的引用到主 Coin 表（在新项目 DenomID 下）以便它们匹配？
我基本上需要将该表分成 4 个单独的表。我已经使用 Access 2007 的表分析器向导进行了尝试，对于像我这样的新手来说，它看起来很有希望，但数据太多，它实际上崩溃了。反复。可能是最好的，但现在我需要知道一些最佳实践，以及如何将其付诸实践。任何建议/帮助/相关链接将不胜感激。
I'm relatively new to PHP MySQL and have tasked myself on learning with the "hands on" approach. Luckily, I currently have a (very) large database all relating to coin data with one table to work with. It currently has the following columns (each row representing a single item [coin]):
Group
ItemNo
ListNo
TypeCode
DenomCode
PeriodCode
ActualDate
SortDate
CostPrice
SalePrice
Estimate
StockLevel
DateEntered
DateSold
Archived
ArchiveWhenSold
Highlight
KeepSold
OnLists
NotForSale
Proof
StockItem
OnWeb
Cats
Ref1
Ref2
Variety
Picture
Description
TypeName
TypeHeading
DenomName
DenomHeading
DenomValue
PeriodName
PeriodHeading
PeriodStartYear
PeriodEndYear
The groupings for new tables are relatively obvious:
Period:
PeriodCode
PeriodName
PeriodHeading
PeriodStartYear
PeriodEndYear
Denom:
DenomCode
DenomName
DenomHeading
DenomValue
Type:
TypeCode
TypeName
TypeHeading
All the rest, under a Coin table:
Group
ItemNo
ListNo
TypeCode
ActualDate
SortDate
CostPrice
SalePrice
Estimate
StockLevel
DateEntered
DateSold
Archived
ArchiveWhenSold
Highlight
KeepSold
OnLists
NotForSale
Proof
StockItem
OnWeb
Cats
Ref1
Ref2
Variety
Picture
Description
So I'm looking to normalise the table into the tables specified. I know that i'm looking at JOINs but am wondering the best way to go about it. Do I create a new table FIRST with each data group (Denom, Period, Type) and THEN insert the data using a JOIN statement? Or is there a way to create new tables "on the fly" with a JOIN statement. I've got a honking great book open here and am following along nicely the section on MySQL and also looking through this site, but haven't been able to figure out the "correct" way to do this.
The reason I ask here for some knowledgable advice is that i'm a little unsure about how to maintain the "relationships" and keys etc. i.e If I create a table called "Denom" and populate it with all the distinct items from all the current tables data and also have it create a unique primary key, how to I then insert the reference to this new primary key from the Denom table into the main Coin table (under a new item DenomID) so that they match up?
I basically need to split this table up into 4 separate tables. I've tried this using Access 2007's table analyzer wizard and it looked promising for a n00b like me, but there was so much data, it actually crashed. Repeatedly. Probably for the best, but now I need to know some best practice, and also HOW to put it into practice. Any advice/help/relevant links would be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先创建表，不要忘记向所有包含主表主键的子表添加外键字段（每个新表也必须获得主键），以便您可以连接表。如果您没有主键，则需要在执行其他操作之前创建一个主键。
将数据放入表中是一个简单的插入操作，
您将连接以取出数据库，因此请记住在新表上创建索引，尤其是在外键字段上。
Create the tables first, don't forget to add a foreign key field to all the child tables that contains the Primary key from the main table (also each new table must get a primary key), so that you can join the tables. If you don't have a primary key, you need to create one before doing anything else.
To put the data into the tables is a simple insert
You will join to get the database out, so rememebr to create indexes on the new tables especially onthe foreign key field.