Re: Using FKs to non PK unique-indexes

> 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.

I disagree. Procedural logic is an integral part of a relational database. Triggers make it possible to enforce constraints that cannot be specified declaratively, and there are many examples. Here are a couple: an employee cannot be working on more than one job at the same time (no overlapping intervals), there can be no cycles in a tree (a leaf node must not already exist higher up in the tree).

I also disagree about the overhead incurred. In your example, in order to maintain integrity the existence of a row in both ORDERS and in CLIENTS_PRODUCTS must be verified. Two index seeks must be performed for each inserted row, and it doesn't matter whether the database performs them under the covers or whether a trigger performs them, they must still occur. The only real additional performance overhead incurred is that the trigger must be compiled, but that may only happen whenever the database engine is restarted.

<before.the.gods_at_gmail.com> wrote in message news:1139595110.796090.226540_at_z14g2000cwz.googlegroups.com...
> 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 Sun Feb 12 2006 - 16:15:35 CET