Re: Using FKs to non PK unique-indexes

thank you for your comment Jan ... there are some other elements I would like to add :

1- Normalization

Regarding the normalization question, it is clear at first glance that the attribute ORDERS_PRODUCTS.clientID is related only to part of the primary key - the column ORDERS_PRODUCTS.orderID - which is a clear violation of 2NF. But my understanding of how 2NF violations should be resolved is to put any redundancy in a separate table.

As you said, it is basically the case here. ORDERS_PRODUCTS.clientID is redundant, but it is fully constrained by ORDERS.clientID, which eliminate any eventual update problems.

That's a little bit why I asked my question ... is this model still considered not-normalized with ORDERS_PRODUCTS.clientID being redundant and constrained at the same time?

2- Candidate key vs PK superset

I understand candidate keys as a set of column(s), completely different from the PK column(s) set, having a unicity without violating 2NF or 3NF.

For example, in the ORDERS table, we could have a column named "workOrderID" to create a unique number to be used by the manufacturer employees for executing any work related to what have been ordered. This attribute would constitute another case of uniticy within the ORDERS entity and would be entirely related to the PK, therefore avoiding conflict with 2NF and 3NF.

In our case, the unique-index created on clientID and orderID is only a superset of the PK. It is not a candidate key since any group of columns embedding the PK is trivially unique.

So the question is still opened .... does MSSQL violates database relational theory by allowing multi-unicities on an entity? I always learned that an entity should have a single PK elected from the different candidates.

I understand the need of having indexes on tables (I use them a lot for optimization purposes) but I got very surprised to find out I could use them to point out my FKs.

3- Triggers vs unique-index

Like many DB developpers, I try to reduce the use of triggers. IMHO, triggers are something external to relational databases and SQL paradigm since they introduce procedural logic into a declarative environment.

Beside that, they are not easy to debug, they expose poor capacities beside a true programming language and they can add considerable overhead to queries.

In our case, considering the design is not normalized, triggers would probably be the unique way to reach a normalized model while being sure that data are consistant in the ORDERS_PRODUCTS table. I am almost certain though that triggers would generate here more overhead than having an additional column part of the FK between ORDERS and ORDERS_PRODUCTS. But on an other hand, I think it's the perfect example that normalization should really not be considered as a dogma. I am always amused to see people seeing a not-normalized schema as a sign of poor design quality. Received on Fri Feb 10 2006 - 19:11:50 CET