Re: Correlation Table with One - to - One data

Oh, O.K.
one person enrolls patients.
one person Creates Subscriptions.

These are independant events. (any order) 3rd person puts Patients into Subscriptions.

thanks again

Bob Badour wrote:
> tal_mcmahon_at_hotmail.com wrote:
>
> > 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?
>
> That still does not answer how and when the correlation gets recorded.
Received on Fri Oct 06 2006 - 14:57:52 CEST