I am am new to PHP/Databases... But I am picking it up fairly swiftly. What I would like to ask you guys is pretty simple. I want to normalize my database and am not positive how to go about it. I get the concept, but see multiple ways to do it. Figure I'd ask people with some experience.
我对PHP/数据库很陌生……但我很快就学会了。我想问你们的问题很简单。我想规范我的数据库，但我不确定该怎么做。我理解了这个概念，但是看到了多种方法。我想问问有经验的人。
Here is my Database (2 tables so far): Brands Products
这是我的数据库(到目前为止有两个表):品牌产品
***Brands Breakdown:***
1 id int(6)
**Note:** Above, I will probably use 4-Letter codes for each brand instead of primary/int/auto.
2 name text
3 logo varchar(20)
4 phone varchar(20)
5 website varchar(30)
6 contact_name text
7 contact_number varchar(20)
8 contact_email varchar(30)
9 warehouse varchar(20)
10 pricing varchar(15)
11 bio varchar(300)
***Products Breakdown***
id (INT(6) / Auto_Increment)
brand (This is where I'll insert the four letter code for brand)
category (e.g. Brakes)
subCategory (e.g. Brake Rotors)
details (e.g. Drilled and Slotteed 'Razr')
sku (Part #)
minYear
maxyear
make (e.g. Subaru)
model (e.g. Impreza)
subModel (e.g. WRX STi)
description (Paragraph on part describing it)
specs (I imagine this can be expanded on. need cells somewhere for sizes / colors / engine codes / etc.)
msrp
jobber
price
cost
weight (of part)
warehouse (Could be moved to brand's table)
image (URL of image for the part)
So My main question is: Do I make each brand have there own table similar to my current 'products' table? or have 'category' tables? 'subCategories'? How would you guys normalize this data?
所以我的主要问题是:我是否让每个品牌都有与我目前的“产品”表类似的表?或“类别”表吗?“子类”?你们如何将这些数据规范化?
I would like to have a solid database while I'm learning this stuff so I learn the right way. Any advice would be appreciated.
我想要有一个坚实的数据库，而我正在学习这些东西，所以我学习正确的方式。如有任何建议，我们将不胜感激。
UPDATE: To anyone who comes across this question who is trying to learn how to structure their database, one major thing I was unaware of when I asked this was something called "cardinality". Research this topic and learn how to apply it to your database schemas!
更新:对于任何遇到这个问题想要学习如何构建数据库的人来说，当我问这个问题时，我没有意识到的一个主要问题是“基数性”。研究这个主题并学习如何将它应用到数据库模式中!
4 个解决方案
#1
1
Don't make each brand have its own table. That's not normalization, that's partitioning. Don't do that until your data base gets very large.
不要让每个品牌都有自己的桌子。这不是标准化，这是分区。在你的数据库变得非常大之前不要这样做。
It's not clear what your brand table means. I am guessing you mean parts-manufacturer, but I'm not sure. The rest of this discussion assumes that you do mean parts-manufacturer.
不清楚你的品牌表是什么意思。我猜你是说零件制造商，但我不确定。剩下的讨论假设你是指零件制造商。
Here's my suggestion.
这是我的建议。
Rename your brand table. Call it "Manufacturer" and split it in two, for Manufacturer and Contact.
重命名你的品牌表。叫它“制造商”，把它分成两部分，用于制造商和联系人。
Manufacturer:
制造商:
mfrid (your four letter code, primary key)
mfrname text
mrflogo varchar(20)
mfrwebsite varchar(30)
mfrphone varchar(20)
warehouse varchar(20)
Contact:
联系人:
mfrid (four letter code) (part of primary key)
contactid (autoincrement) (part of primary key)
contact_name text
contact_number varchar(20)
contact_email varchar(30)
bio varchar(300)
Why is "pricing" an attribute of the manufacturer? What do you mean by "pricing?" Isn't it an attribute of an individual part?
为什么“定价”是制造商的一个属性?“定价”是什么意思?它不是一个个体的属性吗?
Split your parts table into two. One table will have a row for each part sku. The other will have a table for each application (that is, each make and model of car in which the part may be used). Like so:
把你的零件表分成两部分。每个sku部件都有一行。另一个将为每个应用程序拥有一个表(即可以在其中使用部件的每个make和car的模型)。像这样:
SKU:
SKU:
sku (your stock-keeping unit number, primary key).
mfrid (maker of the PART, not the vehicle in which it fits, foreign key to mfr table).
mfrsku (the brand's stock keeping unit, not necessarily unique in your system)
category (e.g. Brakes)
subCategory (e.g. Brake Rotors)
details (e.g. Drilled and Slotteed 'Razr')
description (Paragraph on part describing it)
saleprice (?)
cost (?)
Application:
应用程序:
ApplicationID (auto incrementing primary key)
make (e.g. Subaru)
model (e.g. Impreza)
subModel (e.g. WRX STi)
firstYear.
lastYear.
Then, you'll need a join table (because each Application can have zero or more SKUs and vice versa; that is, your SKU and Application entities can have many-to-many relationships). In your example, you know that multiple models of Subarus often take the same parts. This schema allows for that.
然后，您将需要一个连接表(因为每个应用程序可以有零个或多个sku，反之亦然;也就是说，您的SKU和应用程序实体可以具有多对多关系)。在您的示例中，您知道Subarus的多个模型经常使用相同的部分。这个模式允许这样做。
ApplicationSKU:
ApplicationSKU:
ApplicationID
SKU
The trick to normalizing is to understand your application domain. Figure out what entities you have: e.g.
规范化的技巧是理解应用程序域。找出你拥有的实体:例如。
- manufacturers like Delco and Subaru
- 像Delco和Subaru这样的制造商
- contact people like Joe and Harry
- 联系像乔和哈里这样的人
- parts like Left Front Wiper Assembly and Rear Wiper Assembly
- 零件如左前雨刷总成和后雨刷总成
- applications like 1999-2006 Subaru Forester and 1998-2007 Subaru Impreza
- 申请如1999-2006年斯巴鲁森林人和1998-2007年斯巴鲁Impreza
Create a table that matches each entity you have. Figure out how you will uniquely identify each entity (in other words, figure out what you will use for a primary key).
创建一个与每个实体匹配的表。弄清楚如何惟一地标识每个实体(换句话说，弄清楚您将对主键使用什么)。
Create join tables when you have many-to-many relationships between entities.
当实体之间存在多对多关系时，创建连接表。
Create foreign keys to connect the various entities together.
创建外键，将各种实体连接在一起。
I hope this helps.
我希望这可以帮助。
#2
2
Change products.brand to products.brand_id and have it be a foreign key to brands.id.
改变产品。品牌产品。brand_id，它是brands.id的外键。
Create a categories table and with fields id, name and parent_id(allow NULL) which will house the categories.id of its parent (NULL means top-level category). Alternatively, you can use a nested set model. products would then have a products.category_id field (no subCategory field necessary).
创建类别表，并使用字段id、名称和parent_id(允许NULL)来存放类别。父类的id (NULL表示*类别)。或者，您可以使用嵌套集模型。产品就会有一个产品。category_id字段(没有必要的子类别字段)
#3
1
And remember when you get to the part where you actually have orders or put things in the warehouse inventory, store theactual price at the time the action was taken. Price on a product is a lookup, it changes over time but the orders or the value of the item in inventory should be related to the actual costs at the time the record was entered.
记住，当你拿到订单或者把东西放进仓库库存的时候，把实际价格储存起来。产品的价格是一个查找，它随时间而变化，但是在库存中项目的订单或价值应该与记录输入时的实际成本有关。
#4
0
One product can fit more than one car -- for example, you might have a wiper blade that fits a 2010 Toyota Camry, a 2009 Scion tC and a 2011 Acura TL. So, you will need to split year/make/model out of the products table, and make a separate table for vehicles (id, year, make, model) and a cross table (id, product_id, vehicle_id) that joins them.
一个产品可以适应多个汽车——例如,您可能有一个雨刷,符合2010年丰田凯美瑞,2009 2009 tC和讴歌TL。所以,你需要跨年度/制造/产品表的模型,车辆,使一个单独的表(id、年制作,模型)和交叉表(id、product_id vehicle_id)加入他们。
#1
1
Don't make each brand have its own table. That's not normalization, that's partitioning. Don't do that until your data base gets very large.
不要让每个品牌都有自己的桌子。这不是标准化，这是分区。在你的数据库变得非常大之前不要这样做。
It's not clear what your brand table means. I am guessing you mean parts-manufacturer, but I'm not sure. The rest of this discussion assumes that you do mean parts-manufacturer.
不清楚你的品牌表是什么意思。我猜你是说零件制造商，但我不确定。剩下的讨论假设你是指零件制造商。
Here's my suggestion.
这是我的建议。
Rename your brand table. Call it "Manufacturer" and split it in two, for Manufacturer and Contact.
重命名你的品牌表。叫它“制造商”，把它分成两部分，用于制造商和联系人。
Manufacturer:
制造商:
mfrid (your four letter code, primary key)
mfrname text
mrflogo varchar(20)
mfrwebsite varchar(30)
mfrphone varchar(20)
warehouse varchar(20)
Contact:
联系人:
mfrid (four letter code) (part of primary key)
contactid (autoincrement) (part of primary key)
contact_name text
contact_number varchar(20)
contact_email varchar(30)
bio varchar(300)
Why is "pricing" an attribute of the manufacturer? What do you mean by "pricing?" Isn't it an attribute of an individual part?
为什么“定价”是制造商的一个属性?“定价”是什么意思?它不是一个个体的属性吗?
Split your parts table into two. One table will have a row for each part sku. The other will have a table for each application (that is, each make and model of car in which the part may be used). Like so:
把你的零件表分成两部分。每个sku部件都有一行。另一个将为每个应用程序拥有一个表(即可以在其中使用部件的每个make和car的模型)。像这样:
SKU:
SKU:
sku (your stock-keeping unit number, primary key).
mfrid (maker of the PART, not the vehicle in which it fits, foreign key to mfr table).
mfrsku (the brand's stock keeping unit, not necessarily unique in your system)
category (e.g. Brakes)
subCategory (e.g. Brake Rotors)
details (e.g. Drilled and Slotteed 'Razr')
description (Paragraph on part describing it)
saleprice (?)
cost (?)
Application:
应用程序:
ApplicationID (auto incrementing primary key)
make (e.g. Subaru)
model (e.g. Impreza)
subModel (e.g. WRX STi)
firstYear.
lastYear.
Then, you'll need a join table (because each Application can have zero or more SKUs and vice versa; that is, your SKU and Application entities can have many-to-many relationships). In your example, you know that multiple models of Subarus often take the same parts. This schema allows for that.
然后，您将需要一个连接表(因为每个应用程序可以有零个或多个sku，反之亦然;也就是说，您的SKU和应用程序实体可以具有多对多关系)。在您的示例中，您知道Subarus的多个模型经常使用相同的部分。这个模式允许这样做。
ApplicationSKU:
ApplicationSKU:
ApplicationID
SKU
The trick to normalizing is to understand your application domain. Figure out what entities you have: e.g.
规范化的技巧是理解应用程序域。找出你拥有的实体:例如。
- manufacturers like Delco and Subaru
- 像Delco和Subaru这样的制造商
- contact people like Joe and Harry
- 联系像乔和哈里这样的人
- parts like Left Front Wiper Assembly and Rear Wiper Assembly
- 零件如左前雨刷总成和后雨刷总成
- applications like 1999-2006 Subaru Forester and 1998-2007 Subaru Impreza
- 申请如1999-2006年斯巴鲁森林人和1998-2007年斯巴鲁Impreza
Create a table that matches each entity you have. Figure out how you will uniquely identify each entity (in other words, figure out what you will use for a primary key).
创建一个与每个实体匹配的表。弄清楚如何惟一地标识每个实体(换句话说，弄清楚您将对主键使用什么)。
Create join tables when you have many-to-many relationships between entities.
当实体之间存在多对多关系时，创建连接表。
Create foreign keys to connect the various entities together.
创建外键，将各种实体连接在一起。
I hope this helps.
我希望这可以帮助。
#2
2
Change products.brand to products.brand_id and have it be a foreign key to brands.id.
改变产品。品牌产品。brand_id，它是brands.id的外键。
Create a categories table and with fields id, name and parent_id(allow NULL) which will house the categories.id of its parent (NULL means top-level category). Alternatively, you can use a nested set model. products would then have a products.category_id field (no subCategory field necessary).
创建类别表，并使用字段id、名称和parent_id(允许NULL)来存放类别。父类的id (NULL表示*类别)。或者，您可以使用嵌套集模型。产品就会有一个产品。category_id字段(没有必要的子类别字段)
#3
1
And remember when you get to the part where you actually have orders or put things in the warehouse inventory, store theactual price at the time the action was taken. Price on a product is a lookup, it changes over time but the orders or the value of the item in inventory should be related to the actual costs at the time the record was entered.
记住，当你拿到订单或者把东西放进仓库库存的时候，把实际价格储存起来。产品的价格是一个查找，它随时间而变化，但是在库存中项目的订单或价值应该与记录输入时的实际成本有关。
#4
0
One product can fit more than one car -- for example, you might have a wiper blade that fits a 2010 Toyota Camry, a 2009 Scion tC and a 2011 Acura TL. So, you will need to split year/make/model out of the products table, and make a separate table for vehicles (id, year, make, model) and a cross table (id, product_id, vehicle_id) that joins them.
一个产品可以适应多个汽车——例如,您可能有一个雨刷,符合2010年丰田凯美瑞,2009 2009 tC和讴歌TL。所以,你需要跨年度/制造/产品表的模型,车辆,使一个单独的表(id、年制作,模型)和交叉表(id、product_id vehicle_id)加入他们。