Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2007-08-17 : 10:46:13
|
| I have three tables like below.. Can you take a look at the database design and tell me if it looks ok?All three table has the referential integrity relationship.Episodes table has the foreign key from registration table and assessment table has both registration_key and episode_key as its foreign keys.. Does it ok to have multiple foreign keys like this ? What is the best practice that I can follow?CREATE TABLE [Registration]( [Registration Key] [into] NOT NULL, [Unique_ID] [char](16) NOT NULL, [DOB] [datetime] NOT NULL, [Gender] [char](1) NOT NULL, CONSTRAINT [PK_Registration] PRIMARY KEY NONCLUSTERED CREATE TABLE [Episodes]( [Episode_Key] [int] NOT NULL, [Registration_Key] [int] NOT NULL, [MCP_Key] [int] NOT NULL, [SUBCONTRACTOR_MCP_KEY] [int] NULL, [AGENCY_EID] [char](20) NULL, [Enrollment_Date] [smalldatetime] NOT NULL, [Discharge_Date] [smalldatetime] NULL, CONSTRAINT [PK_Episodes] PRIMARY KEY CLUSTERED ( [Episode_Key] ASC)ALTER TABLE [Episodes] WITH CHECK ADD CONSTRAINT [FK_Episodes_Registration] FOREIGN KEY([Registration_Key])REFERENCES [Registration] ([Registration_Key])GOALTER TABLE [Episodes] CHECK CONSTRAINT [FK_Episodes_Registration]CREATE TABLE [Assessment]( [Assessment_Key] [int] NOT NULL, [Episode_KEY] [int] NOT NULL, [Registration_Key] [int] NOT NULL, [FISCAL_YEAR_KEY] [smallint] NOT NULL, [Reason_Code] [tinyint] NOT NULL, [AGREEMENT_TYPE_KEY] [int] NOT NULL, CONSTRAINT [PK_Assessment] PRIMARY KEY CLUSTERED ( [Assessment_Key] ASC)) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [Assessment] WITH CHECK ADD CONSTRAINT [FK_Assessment_Episodes] FOREIGN KEY([Episode_KEY])REFERENCES [Episodes] ([Episode_Key])GOALTER TABLE [Assessment] CHECK CONSTRAINT [FK_Assessment_Episodes]GOALTER TABLE [Assessment] WITH CHECK ADD CONSTRAINT [FK_Assessment_Registration] FOREIGN KEY([Registration_Key])REFERENCES [Registration] ([Registration_Key])GOALTER TABLE [Assessment] CHECK CONSTRAINT [FK_Assessment_Registration] |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
table design