我是否正确设计了此数据库？ [英] Have I designed this database correctly?

问题描述

我正在创建一个使用Access数据库读取和写入数据的订购系统。数据库有很多表。特别是，我被困在产品表上。我有其他类别和制造商的表。我将ManufacturerID作为制造商表中的主键和products表中的外键。我还将类别作为类别表中的主键和product表中的外键。这是建立关系的正确方法吗？



此外，我正在考虑在订单表中添加用户列，以便可以优化订单搜索员工接受该订单。我目前有一个用于登录系统的用户表。我是否需要在users表中的UserName和orders表中的UserName之间创建一个关系才能生效？



我尝试过：



我尝试创建从类别和制造商到产品表的一对多关系。

解决方案

Quote：

这是正确的关系吗？

这一切都取决于要求。一种方法是尝试用自然语言读出关系。根据您的设计，它说

Quote：

每个产品只由一个不同的制造商生产，并且只属于一个不同的类别。

这是否正确描述了要求？

例如：

制造商表产品表类别表
 manufacturer_id（PK）+  - < manufacturer_id（FK）... 
 manufacturer_name product_id（PK）category_name（FK）
 ... category_id（FK）> --- + category_id（PK）
 ...... 

I am creating an ordering system that uses an Access database to read and write data. The database has many tables. In particular, I am stuck on the products table. I have other tables for category and manufacturer. I have put the ManufacturerID as a primary key in the manufacturers table and as a foreign key in the products table. I have also put the category as the primary key in the category table and as a foreign key in the products table. Is this the correct way of doing the relationships?

Also, I'm thinking of adding a users column in the orders table, so that an order search can be refined by which employee took that order. I currently have a users table which I use for the login system. Would I need to create a relationship between the UserName in the users table and the UserName in the orders table for this to work?

What I have tried:

I tried creating a one to many relationship from the category and the manufacturer to the product table.

解决方案

Quote:

Is this the correct way of doing the relationships?

It all depends on the requirements. One way is to try to read out the relationships in natural language. Based on your design, it says

Quote:

each product is made by only one distinct manufacturer, and belongs to only one distinct category.

Does that correctly depict the requirements?
For example:

manufacturer table        product table          category table
manufacturer_id (PK) +--< manufacturer_id (FK)   ...
manufacturer_name         product_id (PK)        category_name (FK)
...                       category_id (FK) >---+ category_id (PK)
                          ...                    ...

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