I just read....

I was just reading Chris' blog about the right way and the wrong was to do things. We read many posts every day about the "best" way to write java, or the best way to use ORM or the best way to design a database schema. However, not all co-workers are always in agreement with anything that has the word "best" in the sentence.

Let me highlight a discussion that I had yesterday about a database schema design. (names have been changes to protect their stupidity).

Overview: I found that during the initial database design that I was not involved with some items was left out. For instance foreign keys, check constraints, First normal form, etc. (Small things...) <--- scarsam... During the meeting where I found that a table is not available to hold the attachments for some data since the data relates to another part of the application. I suggested that we create another table and tie in the FK back to the original table that this data will be attached. However, the conversation when a little like this... Person 1 = Boss Person 2 = project owner and BA Person 3 = business expert and technicial architect Person 4 = team member, (Java Programmer) Me = Me, team member, (Java programmer and 12 years experience in Database design) <Me> I'd like to add in this table and then relate it back to the master table with a FK
<Person 4> We don't need FK's since the application takes care of it.
<Me> We had this conversation before and we agreed that FK's are good for the relational integrity of the data. Are you saying that we should use the current table that relates to (this other table).
<Person 4> Yes, because why have another table to store the attachments?
<Me> Because of data integrity...
<Person 2> We could create the table that links the current attachment table to the master data table.
<Me> But we cannot enforce the integrity of the attachments that way because two link tables would be related to the attachment table.
<Person 4> (calls person 3 name) what to you think?
<Person 3> I don't know about databases so it's up to you.
<Me> So we are not going to worry about data integrity on this project?
<Person 4> The application will take care of it.
<Person 2> (referring to me), can you create the table like I showed and let the rest of the team know?
<Me> Are you sure?
<Person 2> Yes


Ok, I'm sure that someone will point out that maybe I caved in too soon. However, this is not the first time that I've had this conversation about database integrity with someone. Most of the time I do convence the other side that we should use the relational database (Oracle in this case) to manage the integrity of the data. Some of the checks can be in the application but it is my belief that the database should be the last line of defense for the data. If there is something that the database can make sure is related or helps to keep the data clean why not do it. In this case there is no concern about space differences. We are adding a table either way so why not do it where the database can make sure that the data from one table can relate to the other table.

I could go on and on about this but I'll start to ramble at some point if I haven't already. I guess my question is how do you convince people to use the best practices when it seems like they are not willing to accept suggestions to make current designs better?


Brian

Dynamic PL/SQL and Bulk Collect

Today was the first time that I needed to use dynamic sql with BULK COLLECT. It was due to a distributed database system that used dblinks from a master reporting database. I programmed this on Oracle 10g but it should work on 9i or later.

declare

  TYPE table1Type is TABLE OF table1%ROWTYPE
        index by binary_integer;
   v_results      table1Type ;

begin
  v_sql := 'SELECT *
             FROM  table1@' || v_dblink || '
             WHERE  mydate_dttm > sysdate - 1';
     
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_results;
end;

That's it... This loaded all of the records into the v_results PL/SQL table and you can loop through the results like a array.

Dallas Techfest 2009 Postponed

I just read in Tim's Blog that the Dallas Techfest 2009 has been postponed and will not happen by March as originally planned. This is due to the last of sponsors for the event. If anyone has additional contacts to get support let Tim know or contact me and I'll pass the information on.