| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
Home -> Community -> Usenet -> comp.databases.theory -> Correlation Table with One - to - One data
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 - 14:00:23 CDT
![]() |
![]() |