Home -> Usenet -> comp.databases.theory -> Re: Correlation Table with One - to - One data

Re: Correlation Table with One - to - One data

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 06 Oct 2006 01:12:41 GMT
Message-ID: <d4iVg.1101$cz.15371_at_ursa-nb00s0.nbnet.nb.ca> 






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 - 03:12:41 CEST












Original text of this message