unique attribute & not null attributes

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

unique attribute & not null attributes

koenjanssens
Dear,

We are thinking of implementing AM into our project because we like the concept more than other alternatives we have been looking into (like EAV, document based database ...), but we have some questions before we can decide whether or not it will suit our needs...

How can we make sure that an attribute is unique? For example, 2 students (the anchor) CANNOT have the same student_id (the attribute)?
The same question for attributes that CANNOT be null? Like a student HAS TO HAVE a student_id?

I hope I am clear enough :-s

Best regards,
Koen
Reply | Threaded
Open this post in threaded view
|

Re: unique attribute & not null attributes

roenbaeck
Administrator
These are interesting questions, and they require quite a bit of work in SQL Server due to limitations of its functionality. Because of this, we generally recommend that  "business rules" like the ones you describe are handled in the application layer. Note that these become even trickier when you do bitemporal modeling.

First, to guarantee uniqueness of an attribute value.

STATIC ATTRIBUTE

value unique within the attribute table,
simple constraint

HISTORIZED ATTRIBUTE

value unique within the attribute table at every point in time,
complex constraint

The constraint must prevent:
   Timepoint
ID 123456789
1  |--A-|-B-
2    |--A---

The constraint must allow:
   Timepoint
ID 123456789
1  --A-|--B-
2      |--A-

For example:
The value AC_NAM_Actor_Name is not unique within the table AC_NAM_Actor_Name, but it is unique with respect to the point-in-time perspective pAC_NAM_Actor_Name(corresponding AC_NAM_ChangedAt).

Secondly, to ensure existence of an attribute value.

STATIC & HISTORIZED ATTRIBUTES

value is not null with respect to the latest view of the anchor,
theoretically possible constraint

For example:
The value AC_NAM_Actor_Name is not null in the view lAC_Actor.

Unfortunately SQL Server does not allow constraints on views (yet), so the only way I see this being set up is as a check constraint in the anchor, that ensures a row exists in AC_NAM_Actor_Name for every AC_ID. However, AC_ID in AC_NAM_Actor_Name is a foreign key to AC_Actor(AC_ID), meaning that the anchor row must exist before the AC_NAM_Actor_Name row, preventing such a constraint from working. But, disabling the FK may have other consequences, like rendering the insert/update/delete triggers unusable. In other words, may be hard to achieve in practice.

Anyone have any other ideas?
Reply | Threaded
Open this post in threaded view
|

Re: unique attribute & not null attributes

roenbaeck
Administrator
An example of the uniqueness check constraint can be found here: http://pastebin.com/mX7G90hV

Please note that it only serves to demonstrate the functionality and probably has very poor performance for large amounts of data.
Reply | Threaded
Open this post in threaded view
|

Re: unique attribute & not null attributes

koenjanssens
I have looked into this code, but I'm not really seeing how this can prevent having 2 actors with the same name?

I am also not sure what you are trying in lines 45-55...

Thank you already for your time!