Mike Sherrill 'Cat Recall'

less info
5,177 reputation
823
bio website
location United States
age
visits member for 3 years, 1 month
seen yesterday

Database design and software development consultant. My clients included everything 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.

Jun
27
revised How to calculate total ON hours and Total OFF hours in day of a motor using PHP and MySQL
Added output.
Jun
27
answered How to calculate total ON hours and Total OFF hours in day of a motor using PHP and MySQL
Jun
20
revised Good explanation of cascade (ON DELETE/UPDATE) behavior
Added interpretation.
Jun
20
answered Good explanation of cascade (ON DELETE/UPDATE) behavior
Jun
19
comment Is it ok to let users add new columns and tables to your database?
Yes, but it would need more than one table. The OP seems to be saying that the database his code creates is a single table. (But I might have misunderstood him.)
Jun
19
comment Is it ok to let users add new columns and tables to your database?
The OP said, "An administrative User can go in and hit 'Create Database' (which is basically a table)..." I meant that they're probably better off with a database that's actually a database than with a database that's really just a table.
Jun
19
answered Is it ok to let users add new columns and tables to your database?
Jun
19
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.
Jun
16
answered Is it possible to avoid filesort?
Jun
16
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