April 18, 2012 at 8:24 am
I am trying to get a developer to change a column in a table that is like so:
CREATE TABLE tableA(
column1 VARCHAR(64) NOT NULL PRIMARY KEY NONCLUSTERED,
column2 VARCHAR(32) NOT NULL DEFAULT 'WORD1' CHECK (column2 IN ('WORD1', 'WORD2', 'WORD3'))
)
There is a clustered index on a date column for this table, but I'm not sure that it is needed to demonstrate my point.
I want him to change column2 to a TINYINT and create a reference table to the VARCHAR description. I would then create a foreign key constraint on the column in the table above to the new table. He is fighting me a bit, but I think my main point of being a DBA and concerned about storage is valid enough. Nevertheless, what other reasons can I give to sway him? i.e. If storage is not a concern, and the application will always be referencing as WORD1 or WORD2 instead of 1 or 2... Is there really a benefit to me having him change this?
Jared
CE - Microsoft
April 18, 2012 at 8:40 am
You are right. Every design should be normalized. But vb programmers are pretty much clueless. They typically want to get everything they need in a single select with no JOINs.
Design is only considered when the DB gets so big that the lack of normalization begins to affect performance and scalability. Good luck.
The probability of survival is inversely proportional to the angle of arrival.
April 18, 2012 at 8:46 am
sturner (4/18/2012)
You are right. Every design should be normalized. But vb programmers are pretty much clueless. They typically want to get everything they need in a single select with no JOINs.Design is only considered when the DB gets so big that the lack of normalization begins to affect performance and scalability. Good luck.
Ha! I know it. These guys program in PERL :sick: I have also stated that it is much easier to maintain. If we need to add/delete/update a description, we don't have to change the constraint or the data in the main table. We can simply change the data in the ref table. That should really be the biggest point to him. If they change WORD1 to WORDS1, they have to update all rows in the table that conatin WORD1, instead of just 1 row.
Jared
CE - Microsoft
April 18, 2012 at 8:55 am
SQLKnowItAll (4/18/2012)
sturner (4/18/2012)
You are right. Every design should be normalized. But vb programmers are pretty much clueless. They typically want to get everything they need in a single select with no JOINs.Design is only considered when the DB gets so big that the lack of normalization begins to affect performance and scalability. Good luck.
Ha! I know it. These guys program in PERL :sick: I have also stated that it is much easier to maintain. If we need to add/delete/update a description, we don't have to change the constraint or the data in the main table. We can simply change the data in the ref table. That should really be the biggest point to him. If they change WORD1 to WORDS1, they have to update all rows in the table that conatin WORD1, instead of just 1 row.
Preaching to the choir here. They may use PERL now, but they are probably vb programmers. :w00t:
The probability of survival is inversely proportional to the angle of arrival.
April 18, 2012 at 9:12 am
SQLKnowItAll (4/18/2012)
I am trying to get a developer to change a column in a table that is like so:
CREATE TABLE tableA(column1 VARCHAR(64) NOT NULL PRIMARY KEY NONCLUSTERED,
column2 VARCHAR(32) NOT NULL DEFAULT 'WORD1' CHECK (column2 IN ('WORD1', 'WORD2', 'WORD3'))
)
There is a clustered index on a date column for this table, but I'm not sure that it is needed to demonstrate my point.
I want him to change column2 to a TINYINT and create a reference table to the VARCHAR description. I would then create a foreign key constraint on the column in the table above to the new table. He is fighting me a bit, but I think my main point of being a DBA and concerned about storage is valid enough. Nevertheless, what other reasons can I give to sway him? i.e. If storage is not a concern, and the application will always be referencing as WORD1 or WORD2 instead of 1 or 2... Is there really a benefit to me having him change this?
If there is a change in the application where column2 now needs to hold 'WORD1','WORD2','WORD3','WORD4', you have to make schema change to update the check constraint. If you put these values into a table with a foreign key constraint, then all that is needed when 'WORD4' has to be added is to add it to the reference table, a DML change.
April 18, 2012 at 9:15 am
Lynn Pettis (4/18/2012)
SQLKnowItAll (4/18/2012)
I am trying to get a developer to change a column in a table that is like so:
CREATE TABLE tableA(column1 VARCHAR(64) NOT NULL PRIMARY KEY NONCLUSTERED,
column2 VARCHAR(32) NOT NULL DEFAULT 'WORD1' CHECK (column2 IN ('WORD1', 'WORD2', 'WORD3'))
)
There is a clustered index on a date column for this table, but I'm not sure that it is needed to demonstrate my point.
I want him to change column2 to a TINYINT and create a reference table to the VARCHAR description. I would then create a foreign key constraint on the column in the table above to the new table. He is fighting me a bit, but I think my main point of being a DBA and concerned about storage is valid enough. Nevertheless, what other reasons can I give to sway him? i.e. If storage is not a concern, and the application will always be referencing as WORD1 or WORD2 instead of 1 or 2... Is there really a benefit to me having him change this?
If there is a change in the application where column2 now needs to hold 'WORD1','WORD2','WORD3','WORD4', you have to make schema change to update the check constraint. If you put these values into a table with a foreign key constraint, then all that is needed when 'WORD4' has to be added is to add it to the reference table, a DML change.
Great! Thanks Lynn!
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply