What is the best way to keep this schema clear?

Question

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)

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

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.

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

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

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')

Answer 2

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

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.

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

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.

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

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