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