Re: Correlation Table with One - to - One data

tal_mcmahon_at_hotmail.com wrote:

> 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]
>

Is it really 1:1? Or is it really (0 or 1):(0 or 1)?

How and when does the correlation get recorded? Received on Thu Oct 05 2006 - 21:39:09 CEST