保持这个模式清晰的最好方法是什么?

Currently I'm working on a RFID project where each tag is attached to an object. An object could be a person, a computer, a pencil, a box or whatever it comes to the mind of my boss. And of course each object have different attributes.

目前我正在做一个RFID项目，每个标签都附在一个对象上。一个物体可以是一个人，一台电脑，一支铅笔，一个盒子，或者其他任何能让我的老板想到的东西。当然每个对象都有不同的属性。

So I'm trying to have a table tags where I can keep a register of each tag in the system (registration of the tag). And another tables where I can relate a tag with and object and describe some other attributes, this is what a have done. (No real schema just a simplified version)

所以我想要有一个表标签我可以在系统中保存每个标签的寄存器(标签的注册)。另一个表格，我可以把标签和对象联系起来描述其他的属性，这就是a所做的。(没有真正的模式，只有简化版)

Suddenly, I realize that this schema could have the same tag in severals tables. For example, the tag 123 could be in C and B at the same time. Which is impossible because each tag just could be attached to just a single object.

突然，我意识到这个模式可以在几个表中使用相同的标记。例如，标记123可以同时在C和B中。这是不可能的，因为每个标签仅仅可以连接到一个对象上。

To put it simple I want that each tag could not appear more than once in the database.

简单地说，我希望每个标记不能在数据库中出现超过一次。

My current approach

我现在的方法

What I really want

我真正想要的

Update: Yeah, the TagID is chosen by the end user. Moreover the TagID is given by a Tag Reader and the TagID is a 128-bit number.

更新:是的，标签是由最终用户选择的。而且TagID是由标记读取器给出的，TagID是一个128位的数字。

New Update: The objects until now are:

新更新:对象现在是:

-- Medicament(TagID, comercial_name, generic_name, amount, ...)

——药物(TagID, comercial_name, generic_name, amount，…)

-- Machine(TagID, name, description, model, manufacturer, ...)

——机器(TagID, name, description, model，制造商，…)

-- Patient(TagID, firstName, lastName, birthday, ...)

——病人(姓名，姓，姓，生日……)

All the attributes (columns or whatever you name it) are very different.

所有的属性(列或其他名称)都非常不同。

Update after update

更新后更新

I'm working on a system, with RFID tags for a hospital. Each RFID tag is attached to an object in order keep watch them and unfortunately each object have a lot of different attributes.

我正在研究一个系统，一个医院的RFID标签。每个射频识别标签都被附加到一个对象上，以便监视它们，不幸的是每个对象都有很多不同的属性。

An object could be a person, a machine or a medicine, or maybe a new object with other attributes.

对象可以是人、机器或药物，也可以是具有其他属性的新对象。

So, I just want a flexible and cleaver schema. That allow me to introduce new object's types and also let me easily add new attributes to one object. Keeping in mind that this system could be very large.

所以，我只想要一个灵活的，简洁的模式。这允许我引入新对象的类型，并允许我轻松地向一个对象添加新属性。记住，这个系统可能很大。

Examples:

例子:

Tag(TagID)
Medicine(generic_name, comercial_name, expiration_date, dose, price, laboratory, ...)
Machine(model, name, description, price, buy_date, ...)
Patient(PatientID, first_name, last_name, birthday, ...)

We must relate just one tag for just one object.

我们必须仅为一个对象关联一个标记。

Note: I don't really speak (or also write) really :P sorry for that. Not native speaker here.

注:我不是真的说(也不是真的写):警对不起。这里不是母语。

7 个解决方案

#1

You can enforce these rules using relational constraints. Check out the use of a persisted column to enforce the constraint Tag:{Pencil or Computer}. This model gives you great flexibility to model each child table (Person, Machine, Pencil, etc.) and at same time prevent any conflicts between tag. Also good that we dont have to resort to triggers or udfs via check constraints to enforce the relation. The relation is built into the model.

您可以使用关系约束来执行这些规则。检查使用持久化列来执行约束标记:{Pencil or Computer}。这个模型使您能够灵活地对每个子表(Person、Machine、Pencil等)进行建模，同时避免标记之间的冲突。同样好的是，我们不必通过检查约束来使用触发器或udf来加强关系。关系建立在模型中。

create table dbo.TagType (TagTypeID int primary key, TagTypeName varchar(10));
insert into dbo.TagType
    values(1, 'Computer'), (2, 'Pencil');

create table dbo.Tag
(   TagId       int primary key, 
    TagTypeId   int references TagType(TagTypeId), 
    TagName     varchar(10),
    TagDate     datetime,
    constraint UX_Tag unique (TagId, TagTypeId)
)
go
create table dbo.Computer 
(   TagId       int primary key, 
    TagTypeID   as 1 persisted,
    CPUType     varchar(25),
    CPUSpeed    varchar(25), 
    foreign key (TagId, TagTypeID) references Tag(TagId, TagTypeID)
)
go
create table dbo.Pencil 
(   TagId       int primary key, 
    TagTypeId   as 2 persisted,
    isSharp     bit,
    Color       varchar(25),
    foreign key (TagId, TagTypeID) references Tag(TagId, TagTypeId)
)
go



-----------------------------------------------------------
-- create a new tag of type Pencil:
-----------------------------------------------------------
insert into dbo.Tag(TagId, TagTypeId, TagName, TagDate)
    values(1, 2, 'Tag1', getdate());

insert into dbo.Pencil(TagId, isSharp, Color)
    values(1, 1, 'Yellow');

-----------------------------------------------------------
-- try to make it a Computer too (fails FK)
-----------------------------------------------------------
insert into dbo.Computer(TagId, CPUType, CPUSpeed)
    values(1, 'Intel', '2.66ghz')

#2

Have a Tag Table with PK identity insert of TagID. This will ensure that each TagID only shows up once no matter what...

有一个标签表，其中包含了标签id的PK标识。这将确保无论如何每个TagID只出现一次。

Then in the Tag Table have a TagType column that can either be free form (TableName) or better yet have a TagType table with entries A,B,C and then have a FK in Tag pointing TagType.

然后，在标记表中有一个标记类型列，该列可以是*形式(TableName)，也可以是更好的标记类型表，其中包含条目a、B、C，然后在标记指向标记类型中包含FK。

I would move the Tag attributes into Table A,B,C to minimize extra data in Tag or have a series of Junction Tables between Tag and A,B, and C

我将把标记属性移到表A、B、C中，以最小化标记中的额外数据，或者在标记与A、B和C之间有一系列连接表

EDIT: Assuming the TagID is created when the object is created this will work fine (Insert into Tag first to get TagID and capture it using IDENTITY_INSERT) This assumes users cannot edit the TagID itself.

编辑:假设在创建对象时创建了TagID，这将会很好地工作(首先插入到Tag中以获取TagID并使用IDENTITY_INSERT捕获TagID)。

If users can choose the TagID then still use a Tag Table with the TagID but have another field called DisplayID where the user can type in a number. Just put on a unique constraint on Tag.DisplayID....

如果用户可以选择TagID，那么仍然使用带有TagID的标记表，但是有另一个名为DisplayID的字段，用户可以在其中输入数字。把上一个独特的限制Tag.DisplayID ....

EDIT: What attributes are you needing and are they nullable? If they are different for A, B, and C then it is cleaner to put them in A, B, and C especially if there might be some for A and B but not C...

编辑:您需要哪些属性，它们是无效的吗?如果A, B，和C是不同的，那么把它们放在A, B，和C中会更干净，特别是如果A和B中可能有，而不是C。

#3

talked with Raz to clear up what he's trying to do. What he's wanting is a flexable way to store attributes related to tags. Tags can one of multiple types of objects, and each object has a specific list of attributes. he also wants to be able to add objects/attributes without having to change the schema. here's the model i came up with:

和拉兹谈过，想弄清楚他想做什么。他想要的是一种可弯曲的方式来存储与标签相关的属性。标签可以是多种类型的对象之一，每个对象都有一个特定的属性列表。他还希望能够添加对象/属性，而不必更改模式。这是我想到的模型:

#4

if each tag can only be in a, b, or c only once, i'd just combine a, b, and c into one table. it'd be easier to give you a better idea of how to build your schema if you gave an example of exactly what you're wanting to collect.

如果每个标记只能在a、b或c中，我只需要将a、b和c合并到一个表中。如果您给出了您想要收集的内容的示例，那么您将更容易了解如何构建您的模式。

to me, from what i've read, it sounds like you have a list of tags, and a list of objects, and you need to assign a tag to an object. if that is the case, i'd have a tags table, and objects table, and a ObjectTag table. in the object tab table you would have a foreign key to the tag table and a foreign key to the object table. then you make a unique index on the tag foreign key and now you've enforced your requirement of only using a tag once.

对我来说，从我读到的内容来看，这听起来像是有一个标签列表和一个对象列表，你需要给一个对象分配一个标签。如果是这样，我将有一个标签表、一个对象表和一个ObjectTag表。在object选项卡表中，标签表的外键和对象表的外键。然后在标记外键上创建一个惟一的索引，现在您强制要求只使用一次标记。

#5

I would tackle this using your original structures. Relational databases are a lot better at aggregating/combining atomic data than they are at parsing complex data structures.

我会用你们原来的结构来解决这个问题。关系数据库在聚合/组合原子数据方面比在解析复杂数据结构方面要好得多。

• Keep the design of each "tag-able" object type in its own table. Data types, check constraints, default values, etc. are still easily implemented this way. Also, continue to define a FK from each object table to the Tags table.

  将每个“可标记”对象类型的设计保存在它自己的表中。数据类型、检查约束、默认值等仍然很容易实现。另外，继续从每个对象表定义FK到标记表。

• I'm assuming you already have this in place, but if you place a unique constraint on the TagId column in each of the object tables (A, B, C, etc.) then you can guarantee uniqueness within that object type.

  我假设您已经有了这个，但是如果您在每个对象表(a、B、C等)中对TagId列设置了唯一的约束，那么您就可以保证该对象类型中的惟一性。

• There are no built-in SQL Server constraints to guarantee uniqueness among all the object types, if implemented as separate tables. So, you will have to make your own validation. An INSTEAD OF trigger on your object tables can do this cleanly.

  如果实现为单独的表，则不存在确保所有对象类型之间惟一性的内置SQL Server约束。所以，你必须自己验证。对象表上的触发器可以干净地执行此操作。

First, create a view to access the TagId list across all your object tables.

首先，创建一个视图来访问所有对象表上的TagId列表。

CREATE VIEW TagsInUse AS
    SELECT A.TagId FROM A
    UNION
    SELECT B.TagId FROM B
    UNION
    SELECT C.TagId FROM C
;

Then, for each of your object tables, define an INSTEAD OF trigger to test your TagId.

然后，对于每个对象表，定义一个而不是触发器来测试TagId。

CREATE TRIGGER dbo.T_IO_Insert_TableA ON dbo.A
    INSTEAD OF INSERT
    AS
    IF EXISTS (SELECT 0 FROM dbo.TagsInUse WHERE TagId = inserted.TagId)
    BEGIN;
        --The tag(s) is/are already in use. Create the necessary notification(s).
        RAISERROR ('You attempted to re-use a TagId. This is not allowed.');
        ROLLBACK
    END;
    ELSE
    BEGIN;
        --The tag(s) is/are available, so proceed with the INSERT.
        INSERT INTO dbo.A (TagId, Attribute1, Attribute2, Attribute3)
            SELECT  i.TagId, i.Attribute1, i.Attribute2, i.Attribute3
            FROM    inserted AS i
        ;
    END;
GO

Keep in mind that you can also (and probably should) encapsulate that IF EXISTS test in a T-SQL function for maintenance and performance reasons.

请记住，出于维护和性能的考虑，您还(可能应该)封装T-SQL函数中的IF存在测试。

You can write supplementary stored procedures for doing things like finding what object type a TagId is associated with.

您可以编写补充存储过程，以便查找与TagId相关联的对象类型。

Pros

• You are still taking advantage of SQL Server's data integrity features, which are all quite fast and self-documenting. Don't underestimate the usefulness of data types.

  您仍在利用SQL Server的数据完整性特性，这些特性都非常快，而且都是自文档化的。不要低估数据类型的用处。

• The view is an encapsulation of the domain that must be unique without combining the underlying sets of attributes. Now, you won't have to write any messy code to decipher the object's type. You can base that determination by which table contains the matching tag.

  视图是域的封装，域必须是惟一的，而不需要组合底层属性集。现在，您不必编写任何混乱的代码来解密对象的类型。您可以根据表包含匹配标记的位置来确定。

• Your options remain open...

  你的选择保持开放……

Because you didn't store everything in an EAV-friendly nvarchar(300) column, you can tweak the data types for whatever makes the most sense for each attribute.

因为您并没有将所有东西存储在一个EAV-friendly nvarchar(300)列中，所以您可以调整数据类型，以便对每个属性进行最合理的处理。

If you run into any performance issues, you can index the view.

如果遇到任何性能问题，可以对视图进行索引。

You (or your DBA) can move the object tables to different file groups on different disks if you need to balance things out and help with parallel disk I/O. Think of it as a form of horizontal partitioning. For example, if you have 8 times as many RFID tags applied to medicine containers as you have for patients, you can place the medicine table on a different disk without having to create the partitioning function that you would need for a monolithic table (one table for all types).

如果您(或您的DBA)需要平衡事务并帮助处理并行磁盘I/O，则可以将对象表移动到不同磁盘上的不同文件组。可以把它看作是水平分区的一种形式。例如，如果你有8倍的RFID标签应用到医疗容器中，你可以将医疗表放在不同的磁盘上，而不必创建一个单片表(所有类型的一个表)所需的分区功能。

If you need to eventually partition your tables vertically (for archiving data onto a read-only partition), you can more easily create a partitioning function for each object type. This would be useful where the business rules do

如果您最终需要垂直地分区表(为了将数据归档到只读分区)，您可以更容易地为每个对象类型创建一个分区函数。这在业务规则中很有用

Most importantly, implementing different business rules based on object type is much simpler. You don't have to implement any nasty conditional logic like "IF type = 'needle' THEN ... ELSE IF type = 'patient' THEN ... ELSE IF....". If you need to apply different rules, then apply them to the relevant object table without having to test a "type" value.

最重要的是，基于对象类型实现不同的业务规则要简单得多。你不需要执行任何讨厌的条件逻辑，比如“IF type = 'needle' THEN……”否则，如果类型= 'patient'那么…ELSE IF ....”。如果需要应用不同的规则，那么将它们应用到相关的对象表，而不必测试“type”值。

Cons

• Triggers have to be maintained. However, this would have to be done in your application anyway, so you are performing the same data integrity checking at the database. That means that you will have no extra network overhead and this will be available for any application that uses this database.
• 必须维护触发器。但是，无论如何，这都必须在应用程序中完成，所以您在数据库中执行相同的数据完整性检查。这意味着您将没有额外的网络开销，这对于任何使用此数据库的应用程序都是可用的。

#6

What you're describing is a classical "table-per-type" ORM mapping. Entity Framework has built-in support of this, which you should look into.

您所描述的是一个典型的“表/类型”ORM映射。实体框架对此有内置的支持，您应该对此进行研究。

Otherwise, I don't think most databases have easy integrity constraints that are enforced over primary keys of multiple tables.

否则，我不认为大多数数据库具有简单的完整性约束，这些约束在多个表的主键上执行。

However, is there any reason why you can't just use a single tags table to hold all the fields? Use a type field to hold the type of object. NULL all the irrelevant fields -- this way they don't consume disk space. You'll end up with far fewer tables (only one) that you can maintain as one single coherent object; it also makes you write far fewer SQL queries to work on tags that may span multiple object types.

但是，为什么不能使用单个标记表来保存所有字段呢?使用类型字段保存对象类型。空所有不相关的字段——这样它们就不会占用磁盘空间。您将得到更少的表(只有一个)，您可以作为一个统一的对象来维护;它还使您编写更少的SQL查询来处理可能跨多个对象类型的标记。

Implementing it as one single table also saves you disk space because you can implement tiers of inheritance -- for example, "patient" and "doctor" and "nurse" can be three different object types, each having similar fields (e.g. firstname, lastname etc.) and some unique fields. Right now you'll need three tables, with duplicated fields.

将它实现为单个表还可以节省磁盘空间，因为您可以实现继承层——例如，“patient”和“doctor”和“nurse”可以是三种不同的对象类型，每一种都有相似的字段(例如firstname、lastname等)和一些惟一的字段。现在您将需要三个表，其中包含重复的字段。

It is also simpler when you add an object type. Before, you need to add a new table, and duplicate some SQL statements that span multiple object types. Now you only need to add new fields to the same table (maybe reuse some). The SQL you need to change are far fewer.

当您添加对象类型时，它也更简单。在此之前，需要添加一个新表，并复制一些跨多个对象类型的SQL语句。现在您只需要将新字段添加到同一个表中(可能重用一些)。需要更改的SQL要少得多。

The only reason why you won't go with one single table is when the number of fields make a row too large to fit inside a SQL-Server page (which I believe is 8K). Then SQL will complain and won't allow you to add any more fields. The solution, in this case, is to adopt an ORM tool (like Entity Framework), and then "reuse" fields. For example, if "Field1" is only used by object type #1, there is no reason why object type #3 can't use it to store something as well. You only need to be able to distinguish it in your programs.

不使用单个表的唯一原因是，字段的数量太大，无法放入SQL-Server页面(我认为是8K)。然后SQL会抱怨，不允许您添加任何字段。在这种情况下，解决方案是采用ORM工具(如实体框架)，然后“重用”字段。例如，如果“Field1”只被对象类型#1使用，那么对象类型#3就没有理由不能使用它来存储内容。你只需要在程序中区分它。

#7

You could have the Tags table such that it can have a pointer to any of those tables, and could include a Type that tells you which of the tables it is

您可以使用Tags表，这样它就可以有指向任何这些表的指针，并且可以包含一个类型，该类型告诉您它是哪个表

Tags
-
ID
Type (A,B, or C)
A (nullable)
B (nullable)
C (nullable)

A
-
ID
(other attributes)