Three suggestions on the database design

Hello Chuck:

Here are three suggestions for the database developers (not knowing how much the new db would take from the old one):

------
--1--ID column generation
------
Please do not utilize a table (currently it is T_NEXT_ID) as means to generate ID filed of tables. Instead, use IDENTITY across the board. Using a centralized table as a source of the IDs for multiple tables can cause a db bottleneck.

------
--2--TRIGGERS: fixing an issue of a batch db update
------
Currently, if a db user runs update SQL for multiple rows, triggers of many tables fail to update fields last_update_by, last_updated_dt of ALL of the records involved.

--For instance, in the trigger TG_CUSTOMER_UPDATE (T_CUSTOMER) find the code:
UPDATE  [dbo].t_customer
SET  last_updated_by = user_name(), last_update_dt = getdate()
WHERE customer_no = @customer_no -- (select min(customer_no) from inserted) -- changed this line CWR 7/24/2007

--The correct code would be:
UPDATE  t
SET  t.last_updated_by= user_name(), t.last_update_dt = getdate()
from t_customer t join inserted i on t.customer_no = i.customer_no--use PK col/cols here!

--TO TEST that, pick three customers and run the SQL:
select customer_no,last_update_dt,last_updated_by from t_customer where customer_no in(A,B,C)

--Then note the output and run a harmless Update statement like this:
update t_customer set mname=mname where customer_no in(A,B,C)

Now, when you run the Select statement again you'll notice that ONLY ONE of three records have being updated. Then substitute the piece of the trigger code as suggested and you'll see the difference - do it in Test! :-)

------
--3--How to avoid an anonymous "dbo" in the columns "created_by" and "last_updated_by"
------
To populate/update columns "created_by" and "last_updated_by", in the corresponding db triggers have "substring(system_user,1,8)" instead of "user_name()".

In this case, for the front-end updates the behavior will remain the same, but for the back-end updates recorded data will be <db login of the user> instead of the anonymous  "dbo".

Thanks,
Simon Basyuk
Manager, Database Administration
Carnegie Hall
Tel: 212-903-9691
sbasyuk@carnegiehall.org