Data source from Oracle 10g problem

Dear Sir,

We met a problem when we move on Oracle 10g from Oracle 9i in the loading process. The scenario is that SQL Server as Data Warehouse, SSIS as ETL tool. But the length of string column as data source table in SSIS grows up 4 times when using Oracle 10g. For example, ABC table x varchar2(8) --> x varchar(32) -->x varchar(8) --> x varchar(8) {Oracle 10g SSIS(source) SSIS(target) SQL Server}

This doesn’t influence the loaded result (I did not find any problem until now), but that influence loading performance and we worry about potential problems.

Do you have any idea or have you met the same problem before?

Thanks,

Jacob

Change your source column in SSIS to be 8 bytes instead of 32.|||

Phil Brammer ,

Thanks for your quick response.

I can't change the length of the column from 8 to 32 by maner because the length of target column is 8. The origal length of the column in Oracle is 8. I guess I should setup some parameters in Oracle side like 'code page'

Thanks,

Jacob

|||No, inside SSIS, set the length of the column in the OLE DB Source to 8 instead of 32.|||

Phil Brammer ,

Thank you very much. This is migration problem. There are more than 100 tables and 1000 columns that ran in Oracle9i very well. But now we have the problem when we want to move on Oracle10g.

Thanks,

Jacob

|||I don't understand the problem then.

Are you asking an SSIS question or an Oracle question?

Please provide more details.

Data replication betweeb different database vendors

Aloha!
On MSDN there are quite a few good patterns when it comes to data
replication between SQL Servers. However in my scenario, a
"Implementing Master-Slave Transactional Incremental Replication Using SQL
Server" or "Implementing Master-Slave Snapshot Replication Using SQL Server"
(MSDN patterns), we will be sitting on a Slave database (SQL Server 2000)
which is dependent upon several different Master databases (from different
vendors and different suppliers). My question is if there are some patterns
that describes this scenario, preferably a XML based solution which is
transparent when it comes to the Master source.
BR,
Max.
Max,
SQL Server can indeed be used as a subscriber, but this is currently just
implemented from the point of view of the vendor's databases ie from within
SQL Server you can't create a publication on another Database server eg DB2,
Sybase etc. Some third-party tools do this type of amalgamation, but it is
also possible to program integration using applications built with SQL-DMO
and the Replication Distributor Interface
Have a look at Heterogenous Publishers in BOL.
HTH,
Paul Ibison
sql

Data replication betweeb different database vendors

Aloha!
On MSDN there are quite a few good patterns when it comes to data
replication between SQL Servers. However in my scenario, a
"Implementing Master-Slave Transactional Incremental Replication Using SQL
Server" or "Implementing Master-Slave Snapshot Replication Using SQL Server"
(MSDN patterns), we will be sitting on a Slave database (SQL Server 2000)
which is dependent upon several different Master databases (from different
vendors and different suppliers). My question is if there are some patterns
that describes this scenario, preferably a XML based solution which is
transparent when it comes to the Master source.
BR,
Max.
Max,
SQL Server can indeed be used as a subscriber, but this is currently just
implemented from the point of view of the vendor's databases ie from within
SQL Server you can't create a publication on another Database server eg DB2,
Sybase etc. Some third-party tools do this type of amalgamation, but it is
also possible to program integration using applications built with SQL-DMO
and the Replication Distributor Interface
Have a look at Heterogenous Publishers in BOL.
HTH,
Paul Ibison

Data Normalization - Best approach?

Hi!

I wonder what would be the best (at to be honest - how to do it at all) to perform data normalization with SSIS. The scenario is as follows:

I got plain table with several columns in it.Some of columns can be copied straight into destination tableSome columns (String) should be lookup in another table to get ID
On success just replace string with IDOn fail - create new record in lookup table and return newly created ID

Thanks for any ideas and maybe short samples

Anrijs Vitolins

Yeah you can do all of this. This link will get you started regarding the lookup and putting new records into the lookup table - I recommend method 2: http://www.sqlis.com/default.aspx?311

You'll need a MULTICAST transform in there at some point as well by the sounds of it.

-Jamie

Data Modelling Question with Cascade Deletes

Hi, I have this scenario that keeps coming up on different models and
I'm yet to find a solution.
In this model I have Customers, Customer Recognitions, Sectors and
Divisions.
Sectors and Divisions are related and reference data.
Customers become recognized in sectors, and then within divisions in
that sector.
The problems I have are these:
1) I need to be able to delete data at all levels & can create all but
1 of the cascade rules - because of circular references. In this case
when I delete a Sector I would want to Cascade and delete all related
CustomerSectors
2) Its feels to me like an unnecessary relationships exists in the
database because of the circular design - but I cant decide how and
exactly to remove a relationship.
Can anyone advise?.
Thanks.
CREATE TABLE [dbo].[CustomerSectorDivision] (
[CSD_ID] [smallint] NOT NULL ,
[D_ID] [smallint] NOT NULL ,
[CS_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customer] (
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerRecognition] (
[CR_ID] [smallint] NOT NULL ,
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerSector] (
[CS_ID] [smallint] NOT NULL ,
[CR_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Division] (
[D_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sector] (
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
(
[CSD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[C_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
(
[CR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
(
[CS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
(
[D_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sector] ADD
CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
(
[S_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
(
[CS_ID]
) REFERENCES [dbo].[CustomerSector] (
[CS_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
(
[D_ID]
) REFERENCES [dbo].[Division] (
[D_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
(
[C_ID]
) REFERENCES [dbo].[Customer] (
[C_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
(
[CR_ID]
) REFERENCES [dbo].[CustomerRecognition] (
[CR_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
)
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [FK_Division_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
) ON DELETE CASCADE
GOIf customers are in sectors and sectors are in divisions, then the
CustomerSectorDivision table breaks normal form.
Please elaborate on the actual (real-life, not current database model)
relationships between the entities.
ML
http://milambda.blogspot.com/|||Remove the FK from CustomerSector to Sector.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1138707797.465718.285110@.g47g2000cwa.googlegroups.com...
Hi, I have this scenario that keeps coming up on different models and
I'm yet to find a solution.
In this model I have Customers, Customer Recognitions, Sectors and
Divisions.
Sectors and Divisions are related and reference data.
Customers become recognized in sectors, and then within divisions in
that sector.
The problems I have are these:
1) I need to be able to delete data at all levels & can create all but
1 of the cascade rules - because of circular references. In this case
when I delete a Sector I would want to Cascade and delete all related
CustomerSectors
2) Its feels to me like an unnecessary relationships exists in the
database because of the circular design - but I cant decide how and
exactly to remove a relationship.
Can anyone advise?.
Thanks.
CREATE TABLE [dbo].[CustomerSectorDivision] (
[CSD_ID] [smallint] NOT NULL ,
[D_ID] [smallint] NOT NULL ,
[CS_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customer] (
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerRecognition] (
[CR_ID] [smallint] NOT NULL ,
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerSector] (
[CS_ID] [smallint] NOT NULL ,
[CR_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Division] (
[D_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sector] (
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
(
[CSD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[C_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
(
[CR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
(
[CS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
(
[D_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sector] ADD
CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
(
[S_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
(
[CS_ID]
) REFERENCES [dbo].[CustomerSector] (
[CS_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
(
[D_ID]
) REFERENCES [dbo].[Division] (
[D_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
(
[C_ID]
) REFERENCES [dbo].[Customer] (
[C_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
(
[CR_ID]
) REFERENCES [dbo].[CustomerRecognition] (
[CR_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
)
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [FK_Division_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
) ON DELETE CASCADE
GO|||Because there are two paths to CustomerSectorDivision, this cascading delete
scenario isn't supported. I prefer to avoid all cascading referential
actions because they cede control over the order in which locks are
obtained, which can make it more difficult to minimize deadlocks.
As far as I can tell, there aren't any unnecessary relationships in this
model. Cascading referential actions in SQL Server require that the
relationships form a tree, but networks can and do occur in perfectly valid
models.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1138707797.465718.285110@.g47g2000cwa.googlegroups.com...
> Hi, I have this scenario that keeps coming up on different models and
> I'm yet to find a solution.
> In this model I have Customers, Customer Recognitions, Sectors and
> Divisions.
> Sectors and Divisions are related and reference data.
> Customers become recognized in sectors, and then within divisions in
> that sector.
> The problems I have are these:
> 1) I need to be able to delete data at all levels & can create all but
> 1 of the cascade rules - because of circular references. In this case
> when I delete a Sector I would want to Cascade and delete all related
> CustomerSectors
> 2) Its feels to me like an unnecessary relationships exists in the
> database because of the circular design - but I cant decide how and
> exactly to remove a relationship.
> Can anyone advise?.
> Thanks.
> CREATE TABLE [dbo].[CustomerSectorDivision] (
> [CSD_ID] [smallint] NOT NULL ,
> [D_ID] [smallint] NOT NULL ,
> [CS_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Customer] (
> [C_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CustomerRecognition] (
> [CR_ID] [smallint] NOT NULL ,
> [C_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CustomerSector] (
> [CS_ID] [smallint] NOT NULL ,
> [CR_ID] [smallint] NOT NULL ,
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Division] (
> [D_ID] [smallint] NOT NULL ,
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Sector] (
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSectorDivision] ADD
> CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
> (
> [CSD_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Customer] ADD
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [C_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerRecognition] ADD
> CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
> (
> [CR_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSector] ADD
> CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
> (
> [CS_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Division] ADD
> CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
> (
> [D_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Sector] ADD
> CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
> (
> [S_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSectorDivision] ADD
> CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
> (
> [CS_ID]
> ) REFERENCES [dbo].[CustomerSector] (
> [CS_ID]
> ) ON DELETE CASCADE ,
> CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
> (
> [D_ID]
> ) REFERENCES [dbo].[Division] (
> [D_ID]
> ) ON DELETE CASCADE
> GO
> ALTER TABLE [dbo].[CustomerRecognition] ADD
> CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
> (
> [C_ID]
> ) REFERENCES [dbo].[Customer] (
> [C_ID]
> ) ON DELETE CASCADE
> GO
> ALTER TABLE [dbo].[CustomerSector] ADD
> CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
> (
> [CR_ID]
> ) REFERENCES [dbo].[CustomerRecognition] (
> [CR_ID]
> ) ON DELETE CASCADE ,
> CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
> (
> [S_ID]
> ) REFERENCES [dbo].[Sector] (
> [S_ID]
> )
> GO
> ALTER TABLE [dbo].[Division] ADD
> CONSTRAINT [FK_Division_Sector] FOREIGN KEY
> (
> [S_ID]
> ) REFERENCES [dbo].[Sector] (
> [S_ID]
> ) ON DELETE CASCADE
> GO
>|||It appears to me that divisions are in sectors. I see nothing wrong with
the model.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:C08CF6D6-993B-4C78-9AD0-C6EC4CE9BCBA@.microsoft.com...
> If customers are in sectors and sectors are in divisions, then the
> CustomerSectorDivision table breaks normal form.
> Please elaborate on the actual (real-life, not current database model)
> relationships between the entities.
>
> ML
> --
> http://milambda.blogspot.com/|||I don't see what the CustomerSectorDivision is there for. The relationship i
s
already defined thorugh the CustomerSector and Division tables, isn't it?
ML
http://milambda.blogspot.com/|||Sectors are areas of industry, Divisions are just sub-sectors within
e.g Finance has several divisions Basic Accounts, Taxation, Assets &
Liabilities. Customers s recognition first in a sector and then for
divisions within the sector.
A customer cannot have recognition for a division without the
corresponsing sector.|||Not necessarily. Since there's a 1:0..n relationship between Sector and
Division, it follows that there could also be a 1:0..n relationship between
CustomerSector and Division. CustomerSectorDivision provides a place to
store information that is related only to the juxtaposition of a
CustomerSector and a Division.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:DEB5E66A-CE4D-43A3-AA63-7A2CB1A15CF1@.microsoft.com...
>I don't see what the CustomerSectorDivision is there for. The relationship
>is
> already defined thorugh the CustomerSector and Division tables, isn't it?
>
> ML
> --
> http://milambda.blogspot.com/|||I see. Now I understand. However, I'd try to further isolate individual
relationships.
Entities:
Sectors
Divisions
Customers
Relationships:
Sector <-- Customer (actual relationship, but I wouldn't implement it
directly in the physical model)
Sector <-- Division
Sector <-- Division <-- Customer (actual relationship, but I wouldn't
implement it directly)
Tables:
Sectors (SectorId : ...)
Divisions (DivisionId : ...)
Customers (CustomerId : ...)
SectorDivisions (SectorDivisionId : SectorId : DivisionId)
DivisionCustomers (SectorDivisionId : CustomerId)
Views:
SectorDivisionCustomers (SectorId : DivisionId : CustomerId)
SectorCustomers (SectorId : CustomerId)
I believe this model supports cascading foreign keys better. But one
question remains - can a Customer belong to a Sector without belonging to a
Division?
ML
http://milambda.blogspot.com/|||The relationship between a Customer and a Sector is implied by the foreign
key constraint between CustomerSector and CustomerRecognition and the
foreign key constraint between CustomerRecognition and Customer.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:002BDCD5-0F8E-4BBE-A4AC-1F996289717F@.microsoft.com...
> That is if the direct relationship between a Customer and a Sector is
> valid.
>
> ML
> --
> http://milambda.blogspot.com/

Data Modeling: Managing a group of events, both Special & Regular.

Scenario: The system needs to manage events by group. There is no hierachica
l
concept in the events. Special events have different attributes that need to
be treated differently. Note that the DeviceID has a many-to-one relationshi
p
with a LocationID (not in the model). Not seen in the model also include
PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
Eventually the system will need to report all the related events for a
special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID i
n
SpecialEvent, what would be the advantages/divantages?
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, the drafted database design is as the
following:
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])Hi CTO,
1. I question the basic structure of the SpecialEvents Table. Are there
Multiple "Special Events" for any single Ordinary Event? If not then the
Special Event Table Primary Key should be the same as the Event Table Primar
y
Key, and it should be a one-to-one relationship, not a many-to-one
relationship. So, what's up with "TypeCode" in the SpecialEvents Table? If
there can be multiple Rows in this table witht he same SpecialEventID, but
with different "TypeCode" values, then this table, as structured, is NOT a
table of SpecialEvents, it's something else, (what I don't know) and should
be renamed, and a real specialEvents table should be added.
2. Please clarify what you mean by "Related Events". Do the groups of
Relate dEvents Overlap? i.e., could Event A be related to EventB and Event
B
be related to EventC, but EventC NOT be related to EventA? like for example
cousins? Bob could be Janes Cousin, and Jane could be Sally's Cousin, even
though Bob and SAlly are unrelated... Or,
Are all the "Groups" formed by the relations non-overlapping distinct
groups?, as for eample Citys and States, or Football teams and Leagues, etc.
If it's the latter then the more appropriate way to "relate" the events, is
to just adda groupID in the events table, and make the value the same for
all the events in the group... If there's some data attribute that is
associated with the group, and not with teach individual event, then add an
EventGroup Table that just has GroupID (as PK) and those Group-specific Data
attributes as additional columns...
hth,
Charly
"C TO" wrote:

> Scenario: The system needs to manage events by group. There is no hierachi
cal
> concept in the events. Special events have different attributes that need
to
> be treated differently. Note that the DeviceID has a many-to-one relations
hip
> with a LocationID (not in the model). Not seen in the model also include
> PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
> Eventually the system will need to report all the related events for a
> special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3) Ple
ase
> comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID
in
> SpecialEvent, what would be the advantages/divantages?
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, the drafted database design is as the
> following:
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>

Data Modeling: manage events by groups

Scenario: The system needs to manage events by groups. There is no
hierachical concept in the events. Special events have different attributes
that need to be treated differently so I create a separate entity. Note that
the DeviceID has a many-to-one relationship with a LocationID (not in the
model). Not seen in the model also include PersonRole (PersonID,
PersonRoleType, RoleTypeStatus, StartDate, EndDate). Eventually the system
will need to report all the related events for a special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment. (4) If I add a nullable RelatedEventID in SpecialEvent, what would
be the advantages/divantages?
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, it is drafted as below.
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])You have already posted this exact same message. Someone has graciously
donated his/her own time to respond; show a little consideration and track
your own threads.
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:E0CEFAD5-B3B2-457E-8E54-346DFADFB8BB@.microsoft.com...
> Scenario: The system needs to manage events by groups. There is no
> hierachical concept in the events. Special events have different
attributes
> that need to be treated differently so I create a separate entity. Note
that
> the DeviceID has a many-to-one relationship with a LocationID (not in the
> model). Not seen in the model also include PersonRole (PersonID,
> PersonRoleType, RoleTypeStatus, StartDate, EndDate). Eventually the system
> will need to report all the related events for a special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3)
Please
> comment. (4) If I add a nullable RelatedEventID in SpecialEvent, what
would
> be the advantages/divantages?
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, it is drafted as below.
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>
>|||I apologize. I use Google Group to search with my Author name, obviously thi
s
thread did not return. I thought that this thread got lost somewhere.
"Scott Morris" wrote:

> You have already posted this exact same message. Someone has graciously
> donated his/her own time to respond; show a little consideration and track
> your own threads.
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:E0CEFAD5-B3B2-457E-8E54-346DFADFB8BB@.microsoft.com...
> attributes
> that
> one
> Please
> would
>
>|||Hello Scott, How to you track your own threads? Can you share it with us?
I posted lots of messages to different newsgroup? But sometimes, I forget to
follow it up...
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23DW6SrXUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> You have already posted this exact same message. Someone has graciously
> donated his/her own time to respond; show a little consideration and track
> your own threads.
>|||Any moderately useful newsgroup reader will do this. I use the one that
comes with outlook.
"js" <js@.someone@.hotmail.com> wrote in message
news:ufq$iBYUFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Hello Scott, How to you track your own threads? Can you share it with us?
>
> I posted lots of messages to different newsgroup? But sometimes, I forget
to
> follow it up...
>
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23DW6SrXUFHA.3436@.TK2MSFTNGP09.phx.gbl...
track
>|||is it outlook express?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OCMgvbYUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Any moderately useful newsgroup reader will do this. I use the one that
> comes with outlook.
> "js" <js@.someone@.hotmail.com> wrote in message
> news:ufq$iBYUFHA.3544@.TK2MSFTNGP10.phx.gbl...
> to
> track
>