Brett Kaiser (x002548) Blog

I've been asked to assist (this time BEFORE Project initiation for a change) in the developmennt of a new application.  The Business Liason/Tech group that is doing this has been collecting requirements (basically reviewing an EXCEL Spreadsheet on steriods) and is coming up with a data model.  We will be doing a model review, but I was able to guide them in how to record the information so I can leverage the data to generate the tables.  I've done this several times already, and they are still fine tuning the table defintion.  I then take that document (Excel, again) and generate the DDL and drop it into ERWin (I'll post that code later).

Now they are curious on how to define relationships, Primary Keys and Alternate keys.  I decided to figure out the best way to guide them in documenting this so I could leverage that as well.  So I came up with the following.  Just ask your Business Liason group to record the information in the following form, then just use the code below and generate all of your code.

Basically, you need a spreadsheet with the follwoing information

Parent Table, Key Column, Child Table, Key Type, Key Order and  Key Sequence.

Parent Table as it implies is the Parent in a relationship.  For Alterante Keys, Primary Keys it is the table that key info is being generated for.  Key Column is the Column that will be used in the key.  Child Table as is implied the child of a relationship.  It is only used for a Foreign Key type.  Key Type is P for Primary, F for Foreign Key amd A for an Alternate Key.  Key Order defines the Order of Columns in a key, and finally Key Sequence defines the Order in which Foreign or Alternate keys are created.  It is also used as part of the index or constraint.

I have been on the wrong end of documentation gone bad too many times, and there is immense push back when you tell them you can't use it...OK, you can use, you have to read it, then retype everything that they already typed. 

I hope you find this useful.

CREATE TABLE myTable99 (
   Parent  sysname
 , keyColumn  sysname
 , Child  sysname
 , keyType  char(1)
 , keyOrder  int
 , keySequence  int)
GO

INSERT INTO myTable99(Parent, keyColumn, Child, keyType, keyOrder, keySequence)
SELECT 'myEmployee',  'EMPL_ID', 'myDirectory', 'F', 1, 1 UNION ALL
SELECT 'myEmployee',  'EMPL_ID', ''           , 'P', 1, 1 UNION ALL
SELECT 'myEmployee',  'SSN'    , ''           , 'A', 1, 1 UNION ALL
SELECT 'myDirectory', 'PHONE'  , ''           , 'P', 1, 1 UNION ALL
SELECT 'myDirectory', 'EMPL_ID', ''           , 'P', 2, 1
GO

SELECT * FROM myTable99
GO

CREATE TABLE myEmployee(EMPL_ID char(12) NOT NULL, SSN char(9), EMP_NAME varchar(50))
CREATE TABLE myDirectory(PHONE char(10) NOT NULL, EMPL_ID char(12) NOT NULL)
GO

SELECT SQL FROM (
SELECT DISTINCT 'ALTER TABLE ' + Parent
 + ' WITH NOCHECK ADD CONSTRAINT '
 + Parent + '_PK PRIMARY KEY (' AS SQL
 , Parent, 1 AS SQL_GROUP, 1 AS keyOrder
  FROM myTable99
 WHERE keyType = 'P'
UNION ALL
SELECT '   '+keyColumn AS SQL
 , Parent, 2 AS SQL_GROUP, keyOrder
  FROM myTable99
 WHERE keyOrder = 1
   AND keyType = 'P'
UNION ALL
SELECT ' , '+keyColumn AS SQL
 , Parent, 3 AS SQL_GROUP, keyOrder
  FROM myTable99
 WHERE keyOrder <> 1
   AND keyType = 'P'
UNION ALL
SELECT DISTINCT ' )' AS SQL
 , Parent, 4 AS SQL_GROUP, 1 AS keyOrder
  FROM myTable99
 WHERE keyType = 'P'
UNION ALL
SELECT DISTINCT 'GO' AS SQL
 , Parent, 5 AS SQL_GROUP, 1 AS keyOrder
  FROM myTable99
 WHERE keyType = 'P') AS XXX
ORDER BY Parent, SQL_GROUP, keyOrder