数据库是关系 [英] Database Is-a relationship

问题描述

我的问题与数据库模式的开发有关，如下。

我正在开发一个采购模块，我想在其中使用采购项目和服务。
以下是我的EER图，（请注意，服务具有很少的特殊属性-最多2个）

我的问题是保留产品和服务放在两个表中还是一个表中？

一个表选项–
降低了复杂性，因为我只需要指定引用项目表的项目ID将会有一个 item_type字段来标识它是产品还是服务

两个表选项–
到处都必须引用单独的产品或服务我想引用它们，并且必须在引用产品或服务的每个表中都保留 item_type字段？

当前计划使用选项1，但是想要了解对此事的专家意见。非常感谢您的时间和建议。谢谢。

解决方案

我当然会转到两个表选项。您会看到，必须区分产品和服务，因此可以在程序中使用 switch（item_type）{...} 或完全不同的Product和服务。而且，如果需要更新数据库架构，则 switch 很难维护。

第二个原因是NULL 。我建议您尽量避免使用它们，因为它们所产生的问题多于解决的问题。使用两个表，您可以将所有字段声明为非NULL，而无需进行NULL处理。使用一个表选项，您必须手动编写代码以确保如果 item_type = product ，则特定于产品的字段不是NULL，而特定于服务的字段是NULL，并且如果 item_type = service ，则服务特定字段不为NULL，而产品特定字段为NULL。这不是一件令人愉快的工作，并且DBMS无法为您做到这一点（如果SQL或其他类似方法中的another_field = value 列约束不存在 NOT NULL）。 / p>



有两个表。支持更容易。我曾经见过一个数据库，其中 everything ，每条数据都只放在两个表中-有页面和代码页来确保必要的字段不为NULL。

My problem relates to DB schema developing and is as follows.

I am developing a purchasing module, in which I want to use for purchasing items and SERVICES. Following is my EER diagram, (note that service has very few specialized attributes – max 2)

My problem is to keep products and services in two tables or just in one table?

One table option – Reduces complexity as I will only need to specify item id which refers to item table which will have an "item_type" field to identify whether it’s a product or a service

Two table option – Will have to refer separate product or service in everywhere I want to refer to them and will have to keep "item_type" field in every table which refers to either product or service?

Currently planning to use option 1, but want to know expert opinion on this matter. Highly appreciate your time and advice. Thanks.

解决方案

I'd certainly go to the "two tables" option. You see, you have to distinguish Products and Services, so you may either use switch(item_type) { ... } in your program or entirely distinct code paths for Product and for Service. And if a need for updating the DB schema arises, switch is harder to maintain.

The second reason is NULLs. I'd advise avoid them as much as you can — they create more problems than they solve. With two tables you can declare all fields non-NULL and forget about NULL-processing. With one table option, you have to manually write code to ensure that if item_type=product, then Product-specific fields are not NULL, and Service-specific ones are, and that if item_type=service, then Service-specific fields are not NULL, and Product-specific ones are. That's not quite pleasant work, and the DBMS can't do it for you (there is no NOT NULL IF another_field = value column constraint in SQL or anything like this).

Go with two tables. It's easier to support. I once saw a DB where everything, every single piece of data went in just two tables — there were pages and pages of code to make sure that necessary fields are not NULL.

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