Composite primary Keys and Foreign Keys - Advice Needed 2

[normm]

Hi All, I wondered if you could clear something up for me.

I have a database that we are currently attempting to roll out as a web app. Large scale changes are happening to the schema and we are currently working on provision for recording history within the database concentrating on the 'main' table initially.

It has been decided that we will insert an updated copy of a record along with the date and time that this operation was completed instead of updating the current record. This has meant that we have expanded the primary key to include the datetime that the insert was completed.

Currently the primary key of this table is used as a foreign key in many other tables within the database which may or may not be updated at the same time.

I decided that the simplest way to maintain this is to create a table with an auto incrementing primary key that can then occur in the 'main' table as a foreign key that is also a composite part of the primary key. All foreign key relationships in the other tables link to this table rather than the main table.

It has been suggested by others within the team that we don't need this additional table and can achieve the same result by keeping the 'main' table with it's identity column and then simply turning identity insert on or off dependent on if it was a logical insert or update that we are performing.

My problem with this is that it could create many to many relationships between the 'main' and FK tables.

I would appreciate any feedback that you may wish to give on this - included below is some code fragments for my solution.

tblEvent used to have a PK of EventID with identity insert on

CREATE TABLE [dbo].[ktblEvent](
	[EventID] [bigint] IDENTITY(1,1) NOT NULL,
	[CreatedDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_ktblEvent] PRIMARY KEY NONCLUSTERED 
(
	[EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [tblEvent] (
[EventID] [bigint] NOT NULL,
..............................
[FromDatetime] [datetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
[ToDatetime] [datetime] NULL,
[UserId] [nvarchar] (256) NOT NULL DEFAULT USER_NAME(),
CONSTRAINT [PK_tblEvent] PRIMARY KEY NONCLUSTERED (
[EventID],
[FromDatetime])
)

ALTER TABLE [dbo].[tblEvent]  WITH CHECK ADD  CONSTRAINT [FK_tblEvent_EventID_ktblEvent] FOREIGN KEY([EventID])
REFERENCES [dbo].[ktblEvent] ([EventID])
ALTER TABLE [dbo].[tblEvent] CHECK CONSTRAINT [FK_tblEvent_EventID_ktblEvent]


CREATE TABLE [tblActivityCodes] (
    [EventID] [bigint] NOT NULL,
    [ActivityDate] [datetime] NOT NULL,
........................................
    [FromDatetime] [datetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
	[ToDatetime] [datetime] NULL,
	[UserId] [nvarchar] (256) NOT NULL DEFAULT USER_NAME(),
CONSTRAINT [PK_tblActivityCodes] PRIMARY KEY NONCLUSTERED (
    [EventID],
    [ActivityDate],
    [FromDatetime])
)


ALTER TABLE [tblActivityCodes] WITH CHECK ADD CONSTRAINT [FK_tblActivityCodes_EventID_ktblEvent] FOREIGN KEY ([EventID]) REFERENCES [ktblEvent]
ALTER TABLE [tblActivityCodes] CHECK CONSTRAINT [FK_tblActivityCodes_EventID_ktblEvent]

 

[bborissov]

Why not keep the main table as it is and have another table where you could store the changes for that MainTable?
There you could add Datetime when that change was happened.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[normm]

Thanks for responding Borislav, that was my initial suggestion and was discussed as a solution however It was decided that the insert method was better for our needs (I agree with this after being talked round).

The issue boils down to,

"Is it possible to maintain a referenced relationship using only part of a primary key as a foreign key?"

I believe it isn't, some of my co-workers believe it is.

Do you have any thoughts about alternate ways to manage the relationships in the above example?

 

[RiverGuy]

Currently the primary key of this table is used as a foreign key in many other tables within the database which may or may not be updated at the same time.

This is the part that seems to be an issue for me. What if a supplementary column is updated in the "main" table, causing a new insert? You will end up without matches in your foreign key tables which reference this main table.

Without knowing more, I like Boris' solution best. It just makes the most sense: your main table stores your data is exists today, and your history table stores the historical audit trail.

If you are saying your tables referencing your main table will never care about this version history, then you can always "reference part of the primary key" (your business key) by using a trigger instead of a foreign key declaration.

 

[normm]

Thanks RiverGuy, that was essentially my issue with it!

As a self taught SQL developer I was just worried that I was being "dumb" and had missed some feature that lets you reference to a component of a primary key rather than the entire key. It seems that after much procrastination on my part my colleagues now agree with me!

Just to clear up your assumption, tables referencing the main table will never care about the version history.
Essentially a record with the highest date will be the current version.

Users will only interact with the system through views and stored procedures so all of this will be transparent to them.

Initially I was a keen advocate of Boris' solution but I can see the benefits of the system we have decided on, They are apparent when you can see the data we are dealing with and the formats we get it in (sadly I can't talk about that).

thanks for your opinions guys, I just needed to check I wasn't going mad or had missed something out during my self education!

BW
Normm