Alex Rivera | Logout

What is the best way to keep this schema clear?

Asked 2011-03-30T15:18:43.753
9

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.

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)

enter image description here

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.

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

My current approach enter image description here

What I really want enter image description here

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.

New Update: The objects until now are:

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

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

-- 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.

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

sql-server database polymorphic-associations
Edit
Report

1 Answer

2

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.

  • 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.

  • 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.

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

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.

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

answered 2011-04-02T06:47:12.780

Your Answer