数据库设计 [英] Database Design

问题描述

这是一个常见的数据库问题，与任何特定的数据库或编程语言无关。

This is a general database question, not related to any particular database or programming language.

我以前做过一些数据库工作，作品。这一次我想为未来做计划。

I've done some database work before, but it's generally just been whatever works. This time I want to plan for the future.

我有一个表存储备件列表。名称，零件号码，位置等。我还需要存储它们也适用的设备。

I have one table that stores a list of spare parts. Name, Part Number, Location etc. I also need to store which device(s) they are applicable too.

一种方法是为每个设备创建一个列在我的备件表。这是如何在当前数据库中完成。一个问题是，如果将来我想添加一个新的设备，我必须创建一个新的列，但它使编程更容易。

One way to do is to create a column for each device in my spare parts table. This is how it's being done in the current database. One concern is if in the future I want to add a new device I have to create a new column, but it makes the programming easier.

我的想法是创建一个单独适用性表。它将存储零件ID和设备ID，如果零件适用于多个设备，它将有多行。

My idea is to create a separate Applicability table. It would store the Part ID and Device ID, if a part is applicable to more than one device it would have more than one row.

Parts
-------
ID
Name
Description
Etc...

PartsApplicability
-------
ID
PartID
DeviceID

Devices
------
ID
Name

我的问题是这是否是一个有效的方法，

My questions are whether this is a valid way to do it, would it provide an advantage over the original way, and is there any better ways to do it?

感谢任何答案。

推荐答案

我同意Rex M的回答，这是一种标准方法。您可以在PartsApplicability表上做的一件事是删除ID列，并使PartID / DeviceID成为复合主键。这将确保您的部件不能多次关联到同一设备，反之亦然。

I agree with Rex M's answer, this is a standard approach. One thing you could do on the PartsApplicability table is remove the ID column, and make the PartID/DeviceID a composite primary key. This will ensure that your Part cannot be associated to the same Device more than once, and vice-versa.

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