Identifying vs. non-identifying relationships

[duh]

This is a general data modeling question, not specific to Visio.

I'm confronting the terms identifying and non-identifying for the first
time.

I thought that identifying meant that the primary key, or identify field
as I use it, will be propagated to the child as the foreign key in the
child. Looks like it will make it part of the primary key in the child,
which then gets propagated on down the line.

And I thought non-identifying meant I could connect whatever columns I
wanted and fields would not be created in the child table.

Well, I'm finding that is not necessarily the case.

So, I"m confused, and confused enough that I don't really know how to
compose a question here.

Any help?

 

[duh]

This is a general data modeling question, not specific to Visio.

I'm confronting the terms identifying and non-identifying for the first
time.

I thought that identifying meant that the primary key, or identify field
as I use it, will be propagated to the child as the foreign key in the
child. Looks like it will make it part of the primary key in the child,
which then gets propagated on down the line.

And I thought non-identifying meant I could connect whatever columns I
wanted and fields would not be created in the child table.

Well, I'm finding that is not necessarily the case.

So, I"m confused, and confused enough that I don't really know how to
compose a question here.

Any help?

Ok, maybe my way of designing tables doesn't exactly match "data
modeling" methodology.

Lets say I've got a table, Account_Contract. It links Accounts to
Contracts, and adds a few attributes.

I also add an identity field to this table. So we have:


Account_Contract_ID (Identity)
Contract_ID (PK)
Account_ID (PK)
------------------
Signup_Date
Terminate_Date
Status


So, I was taught to use identity fields to cascade on down the line.
That way, if a primary key value changes somewhere up the line, it
doesn't have to be changed in all the child tables.

That Account_Contract_ID field cannot be null in the child table.

Am I going about this wrong?

Should I be propagating the Contract_ID/Account_ID fields on down to a
child table, where it then becomes part of the primary key? To then be
cascaded down even more.

 

[Jeremy Shovan]

I would say that it depends on your UOD..
If you think that it is likely that the primary key will change in the
future then it may be more safe to use an identity column for your foreign
keys. But if there is not much of a chance of the primary key changing then
it should be used.

If you use the true primary key rather than an identity column it will
simplify your queries and improve performance. Quite frankly, I think you
should just drop the notion of "identifying" and "non-identifying" keys all
together.. If a field is identifying then it is a primary key [primary key =
identifying column(s)], period. If you are using fields to identify rows in
your table that are not the primary key then maybe you should rethink the
table becuase chances are it is denormalized (look it up). Every column in
the table should be dependant on the primary key, the entire primary key,
and nothing but that primary key. If that is not the case then you
probabally need another table somewhere.

However, if you think there is a good chance that the primary key will
change then there is something called controled denormalization. Meaning, if
the situation calls for it and you have a good reason to denormalize you
database (such as that there is a good chance the primary key could change),
then go ahead and do it.

You may also come up with a situation where you have a primary key that
spans something like 5 columns. Well, if this happens it is not likely that
you want to include all 5 columns in a foreign key reference for obviouse
reasons. In this situation I would suggest that you go ahead and use a
generated key.


Here is an example to put things a little more in perspective.. Lets say you
have a Product table and an ProductType table as follows

Product ProductType
---------- -------------
Id (identity) (PK) | ---Id (identity) (PK)
SKU (nVarchar, uniqu) | Name (nVarchar, uniqu)
ProductType Id(int)--------| OtherField (someType)
Name (nVarchar)

Now, come up with a query to select all of the product names that have the
productType of 'Fruit'
your query would be something like

QUERY 1
SELECT name
FROM Product
JOIN ProductType on Product.productTypeId = ProductType.Id
WHERE ProductType.Name = 'Fruit'



Compare that to the following.

Product ProductType
---------- -------------
SKU (nVarchar, PK) |--------Name (nVarchar, PK)
ProductTypeName(nVarchar) -----| OtherField (someType)
Name (nVarchar)

Now, write a query that will get the same result..

QUERY 2
SELECT name
FROM Product
WHERE productTypeName = 'Fruit'


Now.. you tell me which query is better.

If you choose query # 2 you are the winner.. If not, you can always re-take
a DB 1 class.

Jeremy Shovan
http://www.jeremyshovan.com