CODEWITHSUNDEEP
sqlsql-serverdatabase-designssmssql-server-2008
Arnold Gunawan
Arnold Gunawan

Reputation: 35

SQL Server Database Design and Foreign Key confused

I need help to make my database design better.

The db design is from the old developer, now I'm trying to get the design better by trying to put foreign key around so there won't be any orphan table.

CASE 1:

Here is the story:

I have tables ProductGroup (PG), ProductType (PT), Brand (B), Design (D) to keep all specific detail of the item. Then all the four tables are assembled into one table ProductMaster (PM) where they will be joined and make a ProdCd.

When any row of the PG,PT,B,G is being deleted, all related rows in PM will also be deleted.

There is the sample of the table (The code will be simplified):

CREATE TABLE [dbo].[ProductMaster](
    [ProdCd] [varchar](25) NOT NULL,
    [GCd] [varchar](15) NULL,
    [ACd] [varchar](15) NULL,
    [BCd] [varchar](15) NULL,
    [CCd] [varchar](15) NULL,
    [ProdType] [varchar](50) NULL,
    [BrandCd] [varchar](25) NULL,
    [Design] [varchar](150) NULL,
 CONSTRAINT [PK_ProductMaster] PRIMARY KEY CLUSTERED 
(
    [ProdCd] 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].[ProductGroup](
    [GCd] [varchar](15) NOT NULL,
    [GroupDesc] [varchar](150) NULL,
 CONSTRAINT [PK_ProductGroup] PRIMARY KEY CLUSTERED 
(
    [GCd] 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].[ProductType](
    [GCd] [varchar](1) NOT NULL,
    [ACd] [varchar](15) NOT NULL,
    [ProdType] [varchar](50) NOT NULL,
    [TypeDesc] [varchar](150) NULL,
 CONSTRAINT [PK_ProductType] PRIMARY KEY CLUSTERED 
(
    [GCd] ASC,
    [ACd] ASC,
    [ProdType] 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].[Brand](
    [GCd] [char](1) NOT NULL,
    [BCd] [varchar](15) NOT NULL,
    [BrandCd] [varchar](25) NOT NULL,
    [BrandName] [varchar](75) NULL,
 CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED 
(
    [GCd] ASC,
    [BCd] ASC,
    [BrandCd] 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].[Design](
    [GCd] [char](1) NOT NULL,
    [CCd] [varchar](15) NOT NULL,
    [DesignCd] [varchar](25) NOT NULL,
    [DesignDesc] [varchar](150) NULL,
 CONSTRAINT [PK_Design] PRIMARY KEY CLUSTERED 
(
    [GCd] ASC,
    [CCd] ASC,
    [DesignCd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The sample is:

If I delete the Design row of (1, 0001, PLAIN WHITE, NO DECAL PLAIN WHITE), the whole row in ProductMaster will be deleted.

CASE 2:

I have a table of transaction that have a Header and Detail. The detail only keep "ACd" from table "ProductType" where the "GCd" is always 1. When the PG,PT,B,G is modified, the record on the transaction also modified with "GCd" = 1.

The Table is like this:

CREATE TABLE [dbo].[PFHdr](
    [FNO] [varchar](25) NOT NULL,
    [FMO] [varchar](6) NULL,
    [FDate] [datetime] NULL,
    [GCd] [int] NULL, *This is group related to the transcation not with the PM
    [IQtyc] [float] NULL,
    [RQtyc] [float] NULL,
    [TQtyc] [float] NULL,
 CONSTRAINT [PK_PFrmHdr] PRIMARY KEY CLUSTERED 
(
    [FNO] 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].[PFDtl](
    [FNO] [varchar](25) NOT NULL,
    [ACd] [varchar](15) NOT NULL,
    [Input] [float] NULL,
    [Reject] [float] NULL,
    [Transfer] [float] NULL,
 CONSTRAINT [PK_PFrmDtl] PRIMARY KEY CLUSTERED 
(
    [FNO] ASC,
    [ACd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The sample is:

If the ProductType record of ProdType (1, 0001, PC-10 A, Dinner Plate 10 Inch) being deleted, the record on PFDtl also deleted. But if the record ProdType (7, 0001, PC-12 A, Dinner Plate 12 Inch) deleted , the record on PFdtl did not changed.

Please help me, comment about the design,

  1. How to make it better?
  2. How to apply foreign key is possible?
  3. Any suggestion of DB design?
  4. In CASE 2, should I add column "GCd" to be able to use foreign key?
  5. If the ProdType on ProductType must be unique, should I use it as primary key (with GCd and ACd) or I just need putting UNIQUE or not putting any restriction

NOTE:

  1. The old design do not have any foreign key, all is application restricted
  2. I add Second Case

Upvotes: 3

Views: 452

Answers (1)

Walter Mitty
Walter Mitty

Reputation: 18940

There are really two questions here. Are foreign keys present? If the foreign keys are present, are they declared? As I read your schemas, there are some foreign keys in there such as ProductMaster.GCd. They just aren't declared.

The advantage of declaring a foreign key is that it constrains the data to prevent loss of "referential integrity", a situation where an instance of a foreign key refers to a non existent primary key. This is called "orphaned references". In general, the advantages of enforcing referential integrity are overwhelmingly larger than the cost of having the DBMS check integrity at insert time, and the extra programming needed to deal with rejected data.

If you want to add referential integrity constraints to an existing database, you may have to locate and deal with "orphaned references" before the DBMS will let you put in the constraint. This will generally involve lots of use of the "WHERE NOT EXISTS" construct.

Too bad the previous developer didn't declare foreign keys at the outset.

Upvotes: 1

Related Questions