Hi,
I have a special case of data model, which is the following:
1- I have a table where my entities, i.e table names are stored:
CREATE TABLE [Entity_tbl]
( [id] [INT] NOT NULL , -- (PK)
[name] [NVARCHAR] NOT NULL
)
2- I have a table where I store values, which it's used by
any entity, for instance [Status] value
CREATE TABLE [Status_tbl]
( [id] [INT] NOT NULL , -- (PK)
[name] [NVARCHAR] NOT NULL
)
3- I have a table where I store values assigned to a specific
entity instance:
CREATE TABLE [Entity_Status_tbl]
( [entity_id] [INT] NOT NULL, -- (FK = [Entity_tbl].[id])
[instance_id] [INT] NOT NULL, --
[status_id] [INT] NOT NULL -- (FK = [Status_tbl].[id])
)
4- I have others tables which are considered as entities which
they handle the instances, for example a concrete entities
[Service], [Resource]
CREATE TABLE [Service_tbl]
( [id] [INT] NOT NULL , -- (PK)
[name] [NVARCHAR] NOT NULL
)
CREATE TABLE [Resource_tbl]
( [id] [INT] NOT NULL , -- (PK)
[name] [NVARCHAR] NOT NULL,
)
Having this model, I can assign a status value to any service or resource created instance, without changing [Service_tbl] and [Resource_tbl] schema.
In fact, the references (one-to-one or one-to-many) between [Service_tbl], [Resource_tbl] and [Entity_Status_tbl] could be described as following:
[Service_tbl].[id] = [Entity_Status_tbl].[instance_id]
AND
[Entity_Status_tbl].[entity_id] = id of the entity service stored
in [Entity_tbl]
[Resource_tbl].[id] = [Entity_Status_tbl].[instance_id]
AND
[Entity_Status_tbl].[entity_id] = id of the entity resource stored
in [Entity_tbl]
MY QUESTION: How I exprime these references?
THANKS A LOT.
[ February 08, 2005: Message edited by: Djamel Djemaoun ]
I have a special case of data model, which is the following:
1- I have a table where my entities, i.e table names are stored:
CREATE TABLE [Entity_tbl]
( [id] [INT] NOT NULL , -- (PK)
[name] [NVARCHAR] NOT NULL
)
2- I have a table where I store values, which it's used by
any entity, for instance [Status] value
CREATE TABLE [Status_tbl]
( [id] [INT] NOT NULL , -- (PK)
[name] [NVARCHAR] NOT NULL
)
3- I have a table where I store values assigned to a specific
entity instance:
CREATE TABLE [Entity_Status_tbl]
( [entity_id] [INT] NOT NULL, -- (FK = [Entity_tbl].[id])
[instance_id] [INT] NOT NULL, --
[status_id] [INT] NOT NULL -- (FK = [Status_tbl].[id])
)
4- I have others tables which are considered as entities which
they handle the instances, for example a concrete entities
[Service], [Resource]
CREATE TABLE [Service_tbl]
( [id] [INT] NOT NULL , -- (PK)
[name] [NVARCHAR] NOT NULL
)
CREATE TABLE [Resource_tbl]
( [id] [INT] NOT NULL , -- (PK)
[name] [NVARCHAR] NOT NULL,
)
Having this model, I can assign a status value to any service or resource created instance, without changing [Service_tbl] and [Resource_tbl] schema.
In fact, the references (one-to-one or one-to-many) between [Service_tbl], [Resource_tbl] and [Entity_Status_tbl] could be described as following:
[Service_tbl].[id] = [Entity_Status_tbl].[instance_id]
AND
[Entity_Status_tbl].[entity_id] = id of the entity service stored
in [Entity_tbl]
[Resource_tbl].[id] = [Entity_Status_tbl].[instance_id]
AND
[Entity_Status_tbl].[entity_id] = id of the entity resource stored
in [Entity_tbl]
MY QUESTION: How I exprime these references?
THANKS A LOT.
[ February 08, 2005: Message edited by: Djamel Djemaoun ]