Correlation Table with One - to - One data
Date: 5 Oct 2006 12:00:23 -0700
Message-ID: <1160074823.594548.161540_at_m7g2000cwm.googlegroups.com>
Hello all,
Decided to see if my uncomfortable feelings with this implementation are misguided or not.
It seems that I need to correlate data from 2 tables in a one to one fashion the problem is either part can be entered into the data independant of the other for business reasons. I cannot use a typical base/extension table approach as there is an implied hierarchy. My only thought is to have a correlation table that has a unique index on each of the foreign key columns.
This feels wrong, but in this instance seems logically correct. Here is the situation:
I have a subscription table
data in this table is entered by one type of user.
I have a patient table
data in this table is entered by a different type of user.
Subscription and Patient Related in a one to one relationship.
Either entry can be performed first/second
Anyone have any thoughts? the script follows
Thanks,
Tal
Here is the script:
CREATE TABLE [dbo].[Patient](
[pk_Patient] [int] NOT NULL,
[Patient] [varchar](50) NOT NULL,
CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED
(
[pk_Patient] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[Subscription](
[pk_Subscription] [int] NOT NULL,
[Subscription] [varchar](50) NOT NULL,
CONSTRAINT [PK_Subscription] PRIMARY KEY CLUSTERED
(
[pk_Subscription] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[SubscriptionPatient](
[pk_SubscriptionPatient] [int] NOT NULL,
[fk_Subscription] [int] NOT NULL,
[fk_Patient] [int] NOT NULL,
CONSTRAINT [PK_SubscriptionPatient] PRIMARY KEY CLUSTERED
(
[pk_SubscriptionPatient] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[SubscriptionPatient]
WITH CHECK ADD CONSTRAINT [FK_SubscriptionPatient_Patient] FOREIGN KEY([fk_Patient]) REFERENCES [dbo].[Patient] ([pk_Patient]) ALTER TABLE [dbo].[SubscriptionPatient] CHECK CONSTRAINT [FK_SubscriptionPatient_Patient] ALTER TABLE [dbo].[SubscriptionPatient] WITH CHECK ADD CONSTRAINT [FK_SubscriptionPatient_Subscription] FOREIGN KEY([fk_Subscription]) REFERENCES [dbo].[Subscription] ([pk_Subscription]) ALTER TABLE [dbo].[SubscriptionPatient] CHECK CONSTRAINT [FK_SubscriptionPatient_Subscription]Received on Thu Oct 05 2006 - 21:00:23 CEST