Mike Sherrill 'Catcall'

4,422 reputation

620

bio	website
	location	United States
	age	57
visits	member for	2 years, 2 months
	seen	16 hours ago
stats	profile views	106

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,422 reputation	bio	website		visits	member for	2 years, 2 months
620 badges		location	United States		seen	16 hours ago

summary answers questions tags badges favorites bounties reputation activity

183 Comments

suggestions reviews revisions comments badges posts accepts all

1d	comment	Dropping Hypothetical Indexes Do you think running `dbcc checktable` is called for?
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	comment	Query Postgres For Combinations Where Both values are Unique to Each Other And your best effort at doing it yourself looks like . . .
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?
Apr 20	comment	Creating sequence for longer numeric values Is it ok to have gaps in the sequence?
Apr 19	comment	Unsure how to implement certain constraints and relations in this relational database schema MySQL doesn't enforce CHECK() constraints. Sometimes, you can replace a CHECK() constraint with a foreign key constraint. (But not this time.) The only practical way to implement "arrival city is different from departure city" in MySQL is with a trigger.
Apr 17	comment	How to permanently delete a row in oracle forever Define "ever". Are we allowed to recover deleted data by restoring from a year-old offsite backup tape?
Apr 16	comment	Database design: Dividing multiple identical tables, good or bad? Yes, more tables for more referential integrity. (But there's no "areas" table. There could be, though.) Usually you trust the local admins to a certain extent. They have a vested interest in being careful. People who take reservations over the phone wouldn't have insert or update permissions on "seats".
Apr 15	comment	Database design: Dividing multiple identical tables, good or bad? If you're confident in your data entry people, you can drop the check constraint for "area". In general, the more "flexible" your situation, the more tables you need to keep out bad data.
Apr 14	comment	Database design: Dividing multiple identical tables, good or bad? Thank you, Sid. I hope you know that I'm going to quote you on my CV.
Apr 14	comment	speeding up a query on MySql Which columns are varchar(200)? The source columns or the target columns? GROUP BY on 10 million rows will probably take a while.
Apr 13	comment	Functional Dependencies and Normal Forms Are you being asked to determine the normal form for the relation R{ WX Y Z }, or the normal forms for { WX Y }, { WX Z }, and { Y Z }? (When you write "determine the form of:...", the of usually refers to whatever comes after.)
Apr 12	comment	How database administrators can see my requests to MSSQL server? "Is there any other ways to see my activity?" Who cares? Are you trying to keep your activity a secret from the sysadmins?
Apr 11	comment	Column or new table Third normal form has to do with transitive dependencies. It has nothing to do with whether you store a school's name or its id number. (ID numbers have nothing to do with normalization.)
Apr 11	comment	IP Address lookup star schema design If I were in your shoes, I'd also want to test MySQL performance against PostgreSQL and its inet or cidr data type.