locked
Best Practice for design for database RRS feed

  • Question

  • CREATE TABLE dbo.Vendor
    (Vendor_Key varchar(5) NOT NULL,
    Vendor_Description varchar(50) NOT NULL,
    PRIMARY KEY CLUSTERED 
    (Vendor_Key ASC))
    GO
    CREATE TABLE dbo.Client
    (ClientNumber char(4) NOT NULL,
    Financial_Institution varchar(100) NOT NULL,
    PRIMARY KEY CLUSTERED
    (ClientNumber ASC))
    GO
    CREATE TABLE dbo.Vendor_To_Client
    (Vendor_Key varchar(5) NOT NULL,
    ClientNumber char(4) NOT NULL)
    GO 
    ALTER TABLE dbo.Vendor_To_Client  WITH CHECK ADD FOREIGN KEY(ClientNumber)
    REFERENCES dbo.Client (ClientNumber)
    GO
    ALTER TABLE dbo.Vendor_To_Client  WITH CHECK ADD FOREIGN KEY(Vendor_Key)
    REFERENCES dbo.Vendor (Vendor_Key)
    GO
    INSERT INTO dbo.Client (ClientNumber, Financial_Institution)
    VALUES ('1111', 'Bank of America'),
      ('2222', 'Bank of The West'),
      ('3333', 'New Bank')
    INSERT INTO dbo.Vendor (Vendor_Key, Vendor_Description)
    VALUES ('RT', 'Radion Technology'),
      ('BG', 'BioDesiel Grand')
    INSERT INTO dbo.Vendor_To_Client (Vendor_Key, ClientNumber)
    VALUES ('RT', '1111'),
      ('RT', '1111'),
      ('BG', '3333')
    I have a requirement to build a set of tables that would contain a vendor to client relationship.  A vendor can have many clients.  In addition to this I need to build a key based upon the Vendor_Key + ClientNumber to create a composite key, ie RT-1111.  My question is where would be the best place to store this key?  Within the Vendor_To_Client  table?  I already have the 2 data columns within the table, would it not be redundant to store key again.

    SQL - new guy
    Monday, January 30, 2012 5:15 AM

Answers

  • >>My question is where would be the best place to store this key?  Within the Vendor_To_Client  table?  I already have the 2 data columns >>>within the table, would it not be redundant to store key again.

    You  can create a composite key on VendorKey and Client number  to  enforce uniqueness as you have right now in dbo.Vendor_To_Client table

    One note, if your VendorKey  is update able (means , could be changed) then I think  you would be better of having a surrogate key (could be an identity property) to reference on dbo.Vendor_To_Client table...

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2010/03/13/database-design-surrogate-keys-part-1-of-many-rules-for-surrogate-keys-e-f-codd-and-c-j-date-research-and-problems-they-solve.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Peja TaoEditor Wednesday, February 1, 2012 2:53 AM
    Monday, January 30, 2012 6:06 AM
  • Part of my requirements states that I would need to store this key as a whole in the table or else where as this key would tie to other areas.

     

    Thanks for the DDL.

    There is a duplicate row in your sample data ('RT', '1111'). I assume that was a mistake.  One method to address your problem and provide a primary key to prevent duplicate rows is to create a persisted computed column as the primary key.  This will allow you to create foreign key constraints back to this table referencing the computed column.  The referencing column data type should need be varchar(10) to match the referenced column.  For example:

     

    ALTER TABLE dbo.Vendor_To_Client
    	ADD Vendor_Client_Key AS Vendor_Key + '-' + ClientNumber
    	PERSISTED;
    
    ALTER TABLE dbo.Vendor_To_Client
    	ADD CONSTRAINT PK_Vendor_To_Client
    	PRIMARY KEY (Vendor_Client_Key);
    
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, January 30, 2012 12:33 PM
    Answerer

All replies

  • >>My question is where would be the best place to store this key?  Within the Vendor_To_Client  table?  I already have the 2 data columns >>>within the table, would it not be redundant to store key again.

    You  can create a composite key on VendorKey and Client number  to  enforce uniqueness as you have right now in dbo.Vendor_To_Client table

    One note, if your VendorKey  is update able (means , could be changed) then I think  you would be better of having a surrogate key (could be an identity property) to reference on dbo.Vendor_To_Client table...

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2010/03/13/database-design-surrogate-keys-part-1-of-many-rules-for-surrogate-keys-e-f-codd-and-c-j-date-research-and-problems-they-solve.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Peja TaoEditor Wednesday, February 1, 2012 2:53 AM
    Monday, January 30, 2012 6:06 AM
  • Part of my requirements states that I would need to store this key as a whole in the table or else where as this key would tie to other areas.
    SQL - new guy
    Monday, January 30, 2012 11:41 AM
  • Part of my requirements states that I would need to store this key as a whole in the table or else where as this key would tie to other areas.

     

    Thanks for the DDL.

    There is a duplicate row in your sample data ('RT', '1111'). I assume that was a mistake.  One method to address your problem and provide a primary key to prevent duplicate rows is to create a persisted computed column as the primary key.  This will allow you to create foreign key constraints back to this table referencing the computed column.  The referencing column data type should need be varchar(10) to match the referenced column.  For example:

     

    ALTER TABLE dbo.Vendor_To_Client
    	ADD Vendor_Client_Key AS Vendor_Key + '-' + ClientNumber
    	PERSISTED;
    
    ALTER TABLE dbo.Vendor_To_Client
    	ADD CONSTRAINT PK_Vendor_To_Client
    	PRIMARY KEY (Vendor_Client_Key);
    
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, January 30, 2012 12:33 PM
    Answerer
  • Thank you both, Dan, Uri, for your assistance.
    SQL - new guy
    Monday, January 30, 2012 2:55 PM