I'm a beginner with SQL and am working on one of my first databases. I am trying to create a very cut-down shopping cart and am currently working on getting the database together before I start coding the site.
我是SQL的初学者,正在开发我的第一个数据库。我正在尝试创建一个非常简化的购物车,我正在开始编写网站之前将数据库放在一起。
One of the things I am struggling with is how to structure the products portion of the database. If all my products were individual items it would be ok. My problem is thatI would also like the ability to create "packages" of sorts that would combine several products into one Package/product with its own unique ID but different price.
我正在努力的一件事是如何构建数据库的产品部分。如果我的所有产品都是单独的产品,那就没关系。我的问题是,我还希望能够创建各种类型的“包”,将几种产品组合成一个具有自己唯一ID但价格不同的包/产品。
Has anyone tackled this before...Heck, I am sure someone has. My question is: Is there a best practice way to structure the database tables to allow for this sort of thing? Logic-wise I am having a hard time wrapping my head around this one.
有没有人在此之前解决过这个......哎呀,我相信有人有。我的问题是:是否有一种最佳实践方法来构建数据库表以允许这种事情?从逻辑上讲,我很难绕过这一个。
4 个解决方案
#1
3
Ayende Rahien (of RhinoMocks) has recently been doing a series on building a shopping cart, starting here: http://ayende.com/Blog/archive/2008/12/07/designing-a-shopping-cart.aspx. It does use a class diagram but you should be able to convert the concept into a database diagram.
Ayende Rahien(RhinoMocks)最近一直在做一个关于构建购物车的系列,从这里开始:http://ayende.com/Blog/archive/2008/12/07/designing-a-shopping-cart.aspx。它确实使用类图,但您应该能够将概念转换为数据库图。
#2
1
I would probably create a package table with the package id and price for the package and then have an intermediate (cross reference) table between the package and products with package IDs (as a FK to the package table) and corresponding product IDs (as FK to the product table).
我可能会创建一个包表,其中包含包和包的价格,然后在包和带有包ID的产品(作为包表的FK)和相应的产品ID(如FK)之间有一个中间(交叉引用)表。到产品表)。
#3
1
Anything Ayende is doing is going to be over the head of a beginner. To answer your question, you need 3 tables. One for your products, one for your packages and one to "link" products to packages. I'm assuming you've got the first two covered. The third is called a Many-To-Many relationship.
艾恩德正在做的任何事情都将成为初学者的头脑。要回答您的问题,您需要3个表格。一个用于您的产品,一个用于包装,一个用于将产品“链接”到包装。我假设你有前两个被覆盖。第三种称为多对多关系。
It should be called ProductsPackages and have two columns (PackageId and ProductId). You should create foreign key constraints to the Products and Packages tables.
它应该被称为ProductsPackages并有两列(PackageId和ProductId)。您应该为Products和Packages表创建外键约束。
Now, to add a product to a package you insert the id of each into the ProductsPackages table.
现在,要将产品添加到包中,请将每个产品的id插入到ProductsPackages表中。
#4
1
One way that avoids extra tables, but imposes some limits is to simply add a self-referencing "ParentID" field to the product table. That "ParentID" simply points to the associated package product. Since it's all the same table, your packages have the same fields (price, etc.) as your individual products. To find what products make up a package, simply query for all product with a "ParentID" of the package's "ProductID".
避免额外表的一种方法,但强加一些限制是简单地将自引用“ParentID”字段添加到产品表。 “ParentID”只是指向相关的包产品。由于它们都是相同的表,因此您的包与您的单个产品具有相同的字段(价格等)。要查找包含哪些产品的产品,只需使用包“ProductID”的“ParentID”查询所有产品。
The limitation is that products can only be a part of one package. So if you sell a baseball, bat, and glove, as well as a 'baseball' package including all three, the glove could not ever be part of the 'football' package.
限制是产品只能是一个包装的一部分。因此,如果你出售棒球,蝙蝠和手套,以及包括所有三个的“棒球”套装,手套就不可能成为“足球”套装的一部分。
However, one advantage is that both the 'baseball' and the 'football' packages and the 'coach handbook' product could be part of the 'complete sports' package. And if you remove the bat from the 'baseball' package, it's removed from the 'complete sports' package as well. All without adding a single table.
然而,一个优点是“棒球”和“足球”套装以及“教练手册”产品都可以成为“完整体育”套餐的一部分。如果你从'棒球'包装中取出蝙蝠,它也会从'完整的运动'套装中移除。所有没有添加单个表。
Of course the single package per product limitation means this certainly won't work for every situation.
当然,每个产品限制的单个包装意味着这肯定不适用于所有情况。
#1
3
Ayende Rahien (of RhinoMocks) has recently been doing a series on building a shopping cart, starting here: http://ayende.com/Blog/archive/2008/12/07/designing-a-shopping-cart.aspx. It does use a class diagram but you should be able to convert the concept into a database diagram.
Ayende Rahien(RhinoMocks)最近一直在做一个关于构建购物车的系列,从这里开始:http://ayende.com/Blog/archive/2008/12/07/designing-a-shopping-cart.aspx。它确实使用类图,但您应该能够将概念转换为数据库图。
#2
1
I would probably create a package table with the package id and price for the package and then have an intermediate (cross reference) table between the package and products with package IDs (as a FK to the package table) and corresponding product IDs (as FK to the product table).
我可能会创建一个包表,其中包含包和包的价格,然后在包和带有包ID的产品(作为包表的FK)和相应的产品ID(如FK)之间有一个中间(交叉引用)表。到产品表)。
#3
1
Anything Ayende is doing is going to be over the head of a beginner. To answer your question, you need 3 tables. One for your products, one for your packages and one to "link" products to packages. I'm assuming you've got the first two covered. The third is called a Many-To-Many relationship.
艾恩德正在做的任何事情都将成为初学者的头脑。要回答您的问题,您需要3个表格。一个用于您的产品,一个用于包装,一个用于将产品“链接”到包装。我假设你有前两个被覆盖。第三种称为多对多关系。
It should be called ProductsPackages and have two columns (PackageId and ProductId). You should create foreign key constraints to the Products and Packages tables.
它应该被称为ProductsPackages并有两列(PackageId和ProductId)。您应该为Products和Packages表创建外键约束。
Now, to add a product to a package you insert the id of each into the ProductsPackages table.
现在,要将产品添加到包中,请将每个产品的id插入到ProductsPackages表中。
#4
1
One way that avoids extra tables, but imposes some limits is to simply add a self-referencing "ParentID" field to the product table. That "ParentID" simply points to the associated package product. Since it's all the same table, your packages have the same fields (price, etc.) as your individual products. To find what products make up a package, simply query for all product with a "ParentID" of the package's "ProductID".
避免额外表的一种方法,但强加一些限制是简单地将自引用“ParentID”字段添加到产品表。 “ParentID”只是指向相关的包产品。由于它们都是相同的表,因此您的包与您的单个产品具有相同的字段(价格等)。要查找包含哪些产品的产品,只需使用包“ProductID”的“ParentID”查询所有产品。
The limitation is that products can only be a part of one package. So if you sell a baseball, bat, and glove, as well as a 'baseball' package including all three, the glove could not ever be part of the 'football' package.
限制是产品只能是一个包装的一部分。因此,如果你出售棒球,蝙蝠和手套,以及包括所有三个的“棒球”套装,手套就不可能成为“足球”套装的一部分。
However, one advantage is that both the 'baseball' and the 'football' packages and the 'coach handbook' product could be part of the 'complete sports' package. And if you remove the bat from the 'baseball' package, it's removed from the 'complete sports' package as well. All without adding a single table.
然而,一个优点是“棒球”和“足球”套装以及“教练手册”产品都可以成为“完整体育”套餐的一部分。如果你从'棒球'包装中取出蝙蝠,它也会从'完整的运动'套装中移除。所有没有添加单个表。
Of course the single package per product limitation means this certainly won't work for every situation.
当然,每个产品限制的单个包装意味着这肯定不适用于所有情况。