First time poster on DBForums.com, Hello @ All.


Would having two surrogate keys be considered bad DB design, for example:

Table1 (Parent):
T1_ID (Primary Key)(newsequentialid())
T1_Object_ID (Secondary Key)(GUID Generated in AppCode before insert)
Data (varchar(50))

Table2 (Child):
T2_ID (Primary Key)(newsequentialid())
T1_Object_ID_FK (Foreign Key)
Data (varchar(50))

Table3 (Child):
T3_ID (Primary Key)(newsequentialid())
T1_Object_ID_FK (Foreign Key)
Data (varchar(50))


Here is an example of three tables. Table1 is the parent, its primary key is an uniqueidentifer (or GUID) which will be generated on SQL server on every insert, there is also a secondary key which is also a uniqueidentifer but the AppCode will generate this key. This secondary key is what all child tables reference as their foreign key.

The queries will include both primary and secondary keys depending on the AppCodes needs. I have a highly normalized database, each query requires at least a three way join (some queries are up to fifteen way joins). So by having this secondary key I can update parent records without cascading updates to the child records.

So my question is, is this a good idea or does this go against DB best practices. If it is doable, what are the pros and cons.

If you are updating this key then it is not a surrogate.
Why would you want to update T1_ID?

pootle flump ur codings are working excelent.

Actually, that's not accurate. I meant if it is a surrogate there should be no need to update it.

pootle flump ur codings are working excelent.

Actually, I assumed the SQL Server generated key is the secondary key since you said updates to this would not be cascaded. But you have referred to the AppCode key as the secondary one, but also used it for the foreign keys.

Is your design correct and accurate?

pootle flump ur codings are working excelent.

Yes the T1_Object_ID key will not update to keep the foreign key relationship to all of its child tables. The parent record can change (the row ID but not the secondary key). Does that clear it up?

yep, everything except why you think it's necessary to use two keys when one will do

So I do not have to do cascading updates throughout my 12 tables. The parent record is made "Inactive" and a new parent record is inserted. The secondary key will remain no matter how many parent records are inserted. This my shred some light:

(I probably should have posted something like this before)

Parent Table:

CONFIGURATION_ID uniqueidentifier
MAS_MODE_LU_ID_FK uniqueidentifier
COMPUTER_NAME varchar(60)
COMPUTER_IP_ADDRESS varchar(39)
IS_LOGIN_SERVER bit
DATE_ADDED datetime
ADDED_BY_ID uniqueidentifier
DATE_CHANGED datetime
CHANGED_BY_ID uniqueidentifier
IS_ACTIVE bit
IS_ARCHIVED bit
X509CERTIFICATE_BASE64 varchar(MAX)

Child Table:

REGISTERED_MODULE_ID uniqueidentifier
MAS_CONFIGURATION_ID_FK uniqueidentifier
ASSEMBLY_GUID uniqueidentifier
ASSEMBLY_VERSION varchar(20)
ASSEMBLY_SHORT_NAME varchar(60)
ASSEMBLY_STRONG_NAME varchar(600)
ASSEMBLY_FILE_NAME varchar(60)
PARTIAL_SERVICE_ADDRESS varchar(200)
SERVICE_CONTRACT_TYPE_FULLNAME varchar(600)
SERVICE_TYPE_FULLNAME varchar(600)
USERCONTROL_TYPE_FULLNAME varchar(600)
DISPLAY_NAME varchar(60)
DATE_ADDED datetime
ADDED_BY_ID uniqueidentifier
DATE_CHANGED datetime
CHANGED_BY_ID uniqueidentifier
IS_ACTIVE bit
IS_ARCHIVED bit

Looking at these two tables you will notice a foreign key relationship in the child table "MAS_CONFIGURATION_ID_FK", so this is a 1:many relationship. You can have many child records keyed to one parent record.

Lets say I have 500 child records, and I want to update the parent record. Currently our requirements force us to do things similar to this design. Basically we can not truly "delete" anything, we have flags that tell us either the record is active or not ("IS_ACTIVE"). So if we make an update to a parent record, we update the current active record to IS_ACTIVE = false, then insert a new parent record with the data we wanted to change. Since every record has a unique ID I would have to do cascading updates throughout the database.

Now lets say we change to this table structure:

Parent Table:

CONFIGURATION_ID uniqueidentifier
CONFIGURATION_REAL_ID uniqueidentifier <-- NEW COLUMN (Secondary Key)
MAS_MODE_LU_ID_FK uniqueidentifier
COMPUTER_NAME varchar(60)
COMPUTER_IP_ADDRESS varchar(39)
IS_LOGIN_SERVER bit
DATE_ADDED datetime
ADDED_BY_ID uniqueidentifier
DATE_CHANGED datetime
CHANGED_BY_ID uniqueidentifier
IS_ACTIVE bit
IS_ARCHIVED bit
X509CERTIFICATE_BASE64 varchar(MAX)

Child Table:

REGISTERED_MODULE_ID uniqueidentifier
MAS_CONFIGURATION_REAL_ID_FK uniqueidentifier <-- Foreign Key to Secondary Key in parent table
ASSEMBLY_GUID uniqueidentifier
ASSEMBLY_VERSION varchar(20)
ASSEMBLY_SHORT_NAME varchar(60)
ASSEMBLY_STRONG_NAME varchar(600)
ASSEMBLY_FILE_NAME varchar(60)
PARTIAL_SERVICE_ADDRESS varchar(200)
SERVICE_CONTRACT_TYPE_FULLNAME varchar(600)
SERVICE_TYPE_FULLNAME varchar(600)
USERCONTROL_TYPE_FULLNAME varchar(600)
DISPLAY_NAME varchar(60)
DATE_ADDED datetime
ADDED_BY_ID uniqueidentifier
DATE_CHANGED datetime
CHANGED_BY_ID uniqueidentifier
IS_ACTIVE bit
IS_ARCHIVED bit

could you not accomplish this with a history or auditing table maintained by triggers or maybe even perhaps Change Data Capture provided SQL 2008. this design sounds disastrous like everybody else. Is this HIPPA compliance?

Keeping all of the inactive parent records in the table complicates your design, and it does not sound very maintainable. It sounds like you will bloat your parent table with history data which will cause you a number of issues trying to get usable information back out.

Well I know what you saying but, architecture of the database is in place for a reason but its to in depth to really say why. Just assume this is what I have to work with. The question is. From a DB best practice stand point, is there anything wrong with having two row level keys, one as its primary ID, and another key that child records foreign key to, what are the pros and cons of doing this.

Foreign key must reference a PK or a unique constraint in the parent. Since your secondary "key" is not a key at all, because it will contain non-unique values, you will have to deal with that as well.

you asked about best practices. well best practices would be the 2 other approaches I mentioned. this is ugly and difficult to maintain and complicated to implement. all of that does not equal best practices. good luck.

You are already talking about modifying the architecture if you are considering adding a secondary surrogate key.
You asked for opinions, and the general consensus is that what you have is a bad design, and what you are planning to do is also a bad design.

I am not clear on this design and the need to have many parent records with a current status to distinguish historical rows. Why would you need to do cascading updates/deletes on the child record if the parent changed? Isn't the relationship still coherent without the necessity of creating new parent records? If the parent changed does this mean the relationship with child records is no longer valid?