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?
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.
value unique within the attribute table,
value unique within the attribute table at every point in time,
The constraint must prevent:
The constraint must allow:
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
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.