Mike Sherrill 'Catcall'

4,497 reputation

620

bio	website
	location	United States
	age	57
visits	member for	2 years, 3 months
	seen	1 hour ago
stats	profile views	114

Long-time database design and software development consultant. My clients included evetrything from Fortune 100 companies to non-profits and mom-and-pop companies. I've worked with SQL databases since 1985. I'm a database generalist; I'm comfortable with every modern SQL dbms. My home server runs a half-dozen different database management systems. I test most of my answers under more than one of them.

I've also developed relational models for implementation in non-SQL environments, like Lotus Notes and plain C++ code.

I'm known for frequently saying

ID numbers have nothing to do with normalization.
Everything you know about state codes is wrong.
Table names, column names, etc., are your database's API.
Natural keys reduce the number of joins, and often improve performance.
5NF is your friend.
Don't assume. Test.

	4,497 reputation	bio	website		visits	member for	2 years, 3 months
620 badges		location	United States		seen	1 hour ago

summary answers questions tags badges favorites bounties reputation activity

380 Actions

suggestions reviews revisions comments badges posts accepts all

1h	comment	Deploying database changes in high availability environment @Chris: When you want to rename a column, you don't rename a column. Column names are part of your public API. Instead, you 1) add a new column with the right name, 2) update it with the right values, 3) add either a trigger that keeps the two columns identical or you add a check constraint that guarantees both columns have the same values. Then 4) change all the applications to use the new column. Finally, 5) drop the old column. You can also use views to provide some logical data independence, depending on what kinds of changes you need to make.
2d	answered	Is it possible to avoid filesort?
2d	comment	Is it possible to avoid filesort? What version of MySQL?
May 29	comment	Dropping Hypothetical Indexes DROP INDEX requires ALTER permission on the table. Can you issue a benign ALTER TABLE statement to test your permissions? (I know. I know. You're a sysadmin. Do it anyway.)
May 25	comment	Mysql query grouping by two columns for max date takes too long I wrote the same query. Keep in mind that l_act and l_n might need to be indexed, too.
May 25	comment	Mysql query grouping by two columns for max date takes too long What does EXPLAIN show? Paste that into your question.
May 22	comment	Dropping Hypothetical Indexes Do you think running `dbcc checktable` is called for?
May 21	answered	Dropping Hypothetical Indexes
May 20	awarded	Caucus
May 20	awarded	Constituent
May 16	comment	Database table design question A sales order line item requires a minimum of four shipping containers? Is that right?
Apr 25	comment	Enforce indirect relationship You could well be right. That would be one of the hoops.
Apr 25	answered	Enforce indirect relationship
Apr 25	answered	Which of tables key to reference?
Apr 25	answered	What is the benefit of two FK, when one of them can be deduced from another table?
Apr 24	comment	Is this table in 3NF? Neither normalization nor 3NF depend on my perspective or on my opinion. 3NF depends on eliminating transitive dependencies. Where's the transitive dependency in the original table? Even if there were one, it wouldn't justify the decomposition we see in this answer. No principle of normalization justifies what we see in this answer.
Apr 23	comment	Is this table in 3NF? "The argument goes that country depends on state and state depends on city." That's not true, though, is it? State certainly doesn't depend on city in the USA; we have dozens of Springfields. There are Santa Cruzes all over the world. (Springfields, too.) A foreign key can eliminate spelling errors and typos, but that won't affect the normal form. Any time you introduce an ID number--let alone three different ID numbers--in the name of normalization, you're almost certainly on the wrong track.
Apr 23	comment	Is this table in 3NF? If it's not in 3NF, there must be a transitive dependency. Where's the transitive dependency?
Apr 21	comment	Help with modeling this table Over the course of their careers, employees will have many companies. But at a single company, they also have something like a department (maybe more than one), a hire date, maybe a termination date, etc. What additional information do you want to store besides "Fred works for Slate & Co" and "George works for Spacely Sprockets"? Anything?
Apr 21	comment	Help with modeling this table I can imagine several different 5NF schemas for this. It depends mostly on how you want to model employer/employee relationships. How do you want to do that?