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.
Best Answer
Clearly
[doc_index]
is the PK in tblDocument linked to by the FK's in all the other tables. While they may not be actually linked (i.e. the FK may be enforced in stored procs or code), it appears the database is actually normalised in terms of having PK/FK's.The first step though is to ensure that every other table that has
[doc_index]
has a corresponding record in the tblDocument field. So you will need to run something like:This needs to be run for every "FK" table (tblForm/tblReview_Remark/tblReview/tblJs_Document). If any are missing, they need to be deleted or fixed to link to an existing record or a new record in tblDocument to be made that reflects this record (or potentially a new tblDocument record that reads "Unknown").
You then have a bit of work to do to be able to put in proper FKs. I think the best way forward would probably be to clean it up, which is what I am detailing:
tblReview_Remark by its name sounds like it should be linked to tblReview in a M:1 configuration. Create a new field called "ReviewID" and use the current primary key fields to decide which ReviewID (created in the last step) to link to. Something like:
UPDATE tblReview_Remark SET ReviewID = r.ReviewID FROM tblReviewRemark rr INNER JOIN tblReview r ON rr.rvw_created_date = r.rvw_created_date AND rr.doc_index = r.doc_index
Hopefully you should have every record linked, but check for records that have nothing in the ReviewID column. Those records may need to be manually checked or deleted.
doc_index
.Once all of that clean up stuff is done, you should have
doc_index
as a Primary Key, ReviewID as a Primary Key on tblReview. You should then be ready to create the FK relationships using the fields available.tblReview FK
doc_index
referencing tblDocument tblReview_Remark FKReviewID
referencing tblReview tblForm and tblJsDocument should no longer need to exist tblReview_Remark can now havedoc_index
andrvw_created_date
and possiblyrmk_index
removed.While you may be tempted to just insert new surrogate keys, remove the current PK's and link everything by the new keys because its easy, it really appears you need to tidy stuff up. That is my recommendation anyway!