Mike Sherrill 'Cat Recall'

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

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.

Apr
11
comment Postgresql not working: error
What version of PostgreSQL?
Mar
12
comment Indexes for SQL query with WHERE condition and GROUP BY
What's the range of timestamps? Partitioning might be an option.
Sep
30
comment Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?
"Custom operations like ON UPDATE CASCADE might be ommited, because key values not changing." The effect of surrogate keys is to make every foreign key reference the equivalent of "ON UPDATE CASCADE". The key doesn't change, but the value it represents does.
Sep
28
comment Simple table normalisation
I've written about it here and on SO. Think about a "lookup" table--one column of data and an id number. Now drop the id number, write foreign keys to reference the data, and you avoid a join altogether. With other tables, you avoid a join every time the data you need is in a referenced key. (I was surprised how often this happens.) The tradeoff is between join performance and disk I/O. Prototypes indicated that natural keys would perform better in the test suite (dozens of queries) for decades without improvements in hardware (like SSDs).
Sep
28
comment Simple table normalisation
Have you measured the efficiency? (I have. You'll probably be surprised.)
Sep
5
comment coercibility and collations
Pasting a minimal version of the stored procedure into your question might be a good idea. Making it minimal might solve the problem, too.
Aug
13
comment How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables
"I needed to create a new table (recipe_index) that would show every possible combination of every recipe in recipe_table as a set of 3..." (and 4, and 5, and 6) For all practical purposes, 2.1E13 rows is not possible.
Aug
13
comment How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables
500 recipes taken 3 at a time is about 20 million combinations. 500 recipes taken 4 at a time is about 2.5 billion. 500 taken 5 at a time is about 255 billion. 500 taken 6 at a time is about 2.1E13. You don't need to do this more efficiently; you need to do something completely different.
Jul
23
comment pgsql slow query on table of 500 million rows
Is the only information you want from the database the total number of rows?
Jul
12
comment Dump PostgreSQL without DROP DATABASE
I think DROP DATABASE is included only when you use both -c and -C options on pg_restore. As far as I can tell, pg_dump never includes DROP DATABASE.
Jul
11
comment Is it possible to keep track of changes to tables structure?
In a disciplined development environment, you'd use your version control system--subversion, mercurial, git, even RCS in a pinch.
Jul
9
comment Indexes and Optimizations for Logging System
@ypercube: Right, corrected my answer. Thanks.
Jul
9
comment Storing complex userdata
A to-do application doesn't require a user-defined database. It just requires user-supplied data.
Jul
8
comment Indexes and Optimizations for Logging System
Will you need to sort?
Jul
8
comment Storing complex userdata
I won't speak for other professional developers, but I try to avoid that kind of use case. Letting end users essentially design their own databases has always caused immense pain and suffering.
Jul
2
comment what is the best practice when making changes to the the application and database?
@user24250: Updated answer.
Jun
27
comment How to calculate total ON hours and Total OFF hours in day of a motor using PHP and MySQL
@user2100659: Updated answer.
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
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.