October 8, 2003 at 2:09 pm
Suppose you have the following database would you create artificial keys or would carry all fields around wherever a MachinePart needs to be identifyed?
CREATE TABLE [dbo].[Mill] (
[MillID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Part] (
[Part] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Assembly] (
[Asbl] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Part] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Department] (
[DeptID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MillID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Machine] (
[MachID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeptID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MillID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MachinePart] (
[MachID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeptID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MillID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Asbl] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Part] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MachinePartHistory] (
[MachID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeptID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MillID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Asbl] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Part] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ModDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mill] WITH NOCHECK ADD
CONSTRAINT [PK_Mill] PRIMARY KEY CLUSTERED
(
[MillID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Part] WITH NOCHECK ADD
CONSTRAINT [PK_Part] PRIMARY KEY CLUSTERED
(
[Part]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Assembly] WITH NOCHECK ADD
CONSTRAINT [PK_Assembly] PRIMARY KEY CLUSTERED
(
[Asbl],
[Part]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Department] WITH NOCHECK ADD
CONSTRAINT [PK_Dept] PRIMARY KEY CLUSTERED
(
[DeptID],
[MillID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Machine] WITH NOCHECK ADD
CONSTRAINT [PK_Mach] PRIMARY KEY CLUSTERED
(
[MachID],
[DeptID],
[MillID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MachinePart] WITH NOCHECK ADD
CONSTRAINT [PK_MachinePart] PRIMARY KEY CLUSTERED
(
[MachID],
[DeptID],
[MillID],
[Asbl],
[Part]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Assembly] ADD
CONSTRAINT [FK_Assembly_Part2] FOREIGN KEY
(
[Part]
) REFERENCES [dbo].[Part] (
[Part]
)
GO
ALTER TABLE [dbo].[Department] ADD
CONSTRAINT [FK_Dept_Mill] FOREIGN KEY
(
[DeptID]
) REFERENCES [dbo].[Mill] (
[MillID]
)
GO
ALTER TABLE [dbo].[Machine] ADD
CONSTRAINT [FK_Mach_Dept] FOREIGN KEY
(
[MachID],
[MillID]
) REFERENCES [dbo].[Department] (
[DeptID],
[MillID]
)
GO
ALTER TABLE [dbo].[MachinePart] ADD
CONSTRAINT [FK_MachinePart_Assembly] FOREIGN KEY
(
[Asbl],
[Part]
) REFERENCES [dbo].[Assembly] (
[Asbl],
[Part]
),
CONSTRAINT [FK_MachinePart_Machine] FOREIGN KEY
(
[MachID],
[DeptID],
[MillID]
) REFERENCES [dbo].[Machine] (
[MachID],
[DeptID],
[MillID]
)
GO
ALTER TABLE [dbo].[MachinePartHistory] ADD
CONSTRAINT [FK_MachinePartHistory_MachinePart] FOREIGN KEY
(
[MachID],
[DeptID],
[MillID],
[Asbl],
[Part]
) REFERENCES [dbo].[MachinePart] (
[MachID],
[DeptID],
[MillID],
[Asbl],
[Part]
)
GO
Your thoughts are appreciated :)
October 9, 2003 at 2:56 pm
Seems like a good candidate for an artificial key: a four byte integer (or perhaps a two byte smallinteger) rather than a 16 byte key of concatenated char columns. You will, of course, create unique indexes for the natural keys.
--Jonathan
--Jonathan
October 13, 2003 at 9:30 am
But have I help myself out if I sill have to create a unique index on the natural keys. Then I'll have join back 90% of the time b/c the users have no clue about an artificial key.
October 14, 2003 at 5:00 am
You can make the natural key a composite unique non-clustered constraint (index) to get the performance and uniqueness in addition to a clustered articficial key with an int (or the smallest datatype large enough to handle your data amount). The reason is if you need to add any additional indexes then whatever your clustered index is will also be stored in each non-clustered by association so a 16byte per row overhead will apply if you use the composite key into each index but if you use an int only 4 bytes per row and with the non-clustered key as I described you get your other requirement without the user needing to know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply