I have a group of database tables that lack normalization. They all have a similar primary key called 'doc_index', which is either a PK by itself or part a composite PK.
As you can see below, there are no foreign key (FK) columns in any of the tables. Given what I have below could some one show be how to best have PK and FK relationships on these tables?
The major table is tblDocument. Which in my opinion has the main PK doc_index column. All other tables revolve around tblDocument.
See script below:
CREATE TABLE [dbo].[tblDocument](
[doc_index] [int] IDENTITY(1,1) NOT NULL,
[annual_review_reqd] [bit] NOT NULL,
[available] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[doc_number] [nvarchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[doc_status_code] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[doc_subtype_code] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[doc_type_code] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[file_location] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[inactive_reason] [nvarchar](45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[is_form] [bit] NOT NULL,
[keywd_index] [nvarchar](90) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[opr_agency_code] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[original_ao] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sec_class_code] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[special_tag] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[special_tag_rmk] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[stocked_char] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[internet_approved] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
[birth_date] [datetime] NULL,
[cancellation_date] [datetime] NULL,
[doc_num_assn_date] [datetime] NULL,
[next_review_date] [datetime] NULL,
CONSTRAINT [tblDocument$PrimaryKey] PRIMARY KEY CLUSTERED
(
[doc_index] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblForm](
[doc_index] [int] NOT NULL,
[elect_auth_code] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[is_electronic] [bit] NOT NULL,
[prescr_doc_index] [int] NULL,
[privacy_act] [bit] NOT NULL,
[unit_issue_code] [nvarchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
[when_electronic] [datetime] NULL,
CONSTRAINT [tblForm2$PrimaryKey] PRIMARY KEY CLUSTERED
(
[doc_index] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblReview_Remark](
[doc_index] [int] NOT NULL,
[rmk_index] [smallint] NOT NULL,
[rmk_initials] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[rmk_text] [nvarchar](165) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[rvw_created_date] [datetime] NOT NULL,
[rmk_created_date] [datetime] NULL,
CONSTRAINT [tblReview_Remark2$PrimaryKey] PRIMARY KEY CLUSTERED
(
[rvw_created_date] ASC,
[doc_index] ASC,
[rmk_index] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblReview](
[Field1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[doc_index] [int] NOT NULL,
[action_number] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[action_officer] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ao_phone] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[rvw_status_code] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[rvw_type_code] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[rvw_created_date] [datetime] NOT NULL,
[rvw_completen_date] [datetime] NULL,
[rvw_tasked_date] [datetime] NULL,
[rvw_suspense] [datetime] NULL,
CONSTRAINT [tblReview3$PrimaryKey] PRIMARY KEY CLUSTERED
(
[doc_index] ASC,
[rvw_created_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblJs_Document](
[doc_index] [int] NOT NULL,
[cmd_distribution] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fiscal_year] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[num_pages] [smallint] NULL,
[system_char] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cjcs_jel_www] [bit] NULL,
[jcs_jwfc_lan] [bit] NULL,
[jcs_jel_cd_rom] [bit] NULL,
[cinc_cd_rom] [bit] NULL,
[controled_internet] [bit] NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [tblJs_Document$PrimaryKey] PRIMARY KEY CLUSTERED
(
[doc_index] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Any help would be greatly appreciated.
Thanks.