Hi Dev Masters,
I had to call uncle on this one!
I am trying to build a check constraint on a table that tracks shared resources using an EAV model.
The shared resource percentage does not care how many components are included in the whole but does care that the components add up to 100 percent for a given effective date. I was trying use a natural key for the PK (see code below) but I ran into a scenario where on a specific date for the same entity, entity type, and attribute id I need to record factors that add up to 1 (or 100% ) depending on how you look at it.
Thanks for any help!
For example.
Entity = Food Plate
Entity type = Dinner
Effdate = 1/1/2016
entAttVal_int (FK to food component table) = Carrot
entAttVal_dbl (% of plate utilized by carrot) = 25%
Thanks a ton if you have any ideas! The T-SQL code I tried to use to solve the problem is shown below.
I had to call uncle on this one!
I am trying to build a check constraint on a table that tracks shared resources using an EAV model.
The shared resource percentage does not care how many components are included in the whole but does care that the components add up to 100 percent for a given effective date. I was trying use a natural key for the PK (see code below) but I ran into a scenario where on a specific date for the same entity, entity type, and attribute id I need to record factors that add up to 1 (or 100% ) depending on how you look at it.
Thanks for any help!
For example.
Entity = Food Plate
Entity type = Dinner
Effdate = 1/1/2016
entAttVal_int (FK to food component table) = Carrot
entAttVal_dbl (% of plate utilized by carrot) = 25%
Thanks a ton if you have any ideas! The T-SQL code I tried to use to solve the problem is shown below.
Code:
/*=============================================================*/
IF (SELECT OBJECT_ID('EntAttValTb')) IS NOT NULL
DROP TABLE EntAttValTb;
go
--------
create table EntAttValTb
(
entId int
,entTypID int
,entAttId int
,effDate datetime
,entAttVal_int int
--,entAttVal_str varchar(255)
,entAttVal_dbl float
,constraint entAttVal_pk primary key(entId, entTypID, entAttId, effDate) -- this PK arrangement will backfire if more than one ratio needs to be documented for the same entity, entity type, entity attribute id
)