Re: Correlation Table with One - to - One data

Ultimately the idea of one without the other is relatively useless. so it is truly 1-1. I guess the extension table analogy was a bit flawed.

The hurdle is the business requirement that either bit of data can enter the database first.

This is kind of like making a martini.

Both Gin and Vermouth have to be there ultimately, it Doesn't matter if you put in the gin then splash the vermouth or drizzle the vermouth then pour the gin.

Bob Badour wrote:
> 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 Fri Oct 06 2006 - 00:06:18 CEST