Mike Sherrill 'Cat Recall'

less info
5,177 reputation
823
bio website
location United States
age
visits member for 3 years, 1 month
seen Apr 16 at 12:08

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.

Sep
23
answered Is there a tool to check if my database is normalized to the third normal form?
Sep
12
comment What is the purpose of the keyword “ID” in PostgreSQL?
It acts as if your GUI tool passed an uppercase column name, including double quotes, to PostgreSQL. (Other things might account for this behavior, but this is the most common one in my experience.) Any time you create a PostgreSQL column name within double quotes, it becomes case-sensitive. In this case, your tool probably created the column "ID" (including the double quotes). PostgreSQL automatically interprets an unquoted column name as lowercase, so in the SELECT query above, it interprets ID as id. And the id column doesn't exist, but "ID" (with quotes) does.
Sep
11
answered What is the purpose of the keyword “ID” in PostgreSQL?
Sep
11
answered Data type for a fuzzy date?
Sep
11
answered Replacing composite key with surrogate
Sep
11
comment Replacing composite key with surrogate
What's the compelling reason to add an integer and make the existing primary key a nonclustered index instead of just making the existing primary key a nonclustered primary key?
Aug
30
revised Is there a way to recover a dropped mysql database?
Corrected spelling. ("Fegiment" is a word, but it's the wrong word here.)
Aug
30
comment How to shard 14 gb of data for archival database?
How selective is that one indexed column? How many rows do you expect each query to return?
Aug
22
comment Bug in PL/pgSQL function creation
"...it seems I have to truncate all our local tables before restoring..." The pg_dump utility can drop database objects before trying to create them. See the --clean argument on this page. Depending on your environment, it might be practical to drop the dev database before restoring to it. (Make sure the dump creates the database before you do that.)
Aug
21
answered How to restrict sequence like ID type to C type like int64_t, uint64_t, int32_t?
Aug
18
answered Does a numeric field always use the same disk space?
Aug
18
comment Is there such thing as automatic ENUMs?
Nothing automatic for that on any SQL platform. MySQL has an enum, but it's not automatic. ("Automatic" would require altering the definition of the table at run time. That could be computationally very expensive.)
Aug
17
comment MySQL GROUP BY and ORDER BY giving inconsistent results
@HoshSadiq: There are dates in the data you say you want it to return. But you're selecting only an ID in your SQL.
Aug
17
comment Is there such thing as automatic ENUMs?
Where does your application code get the options it presents to the user?
Aug
15
answered Is there such thing as automatic ENUMs?
Aug
15
comment Being careful about additional records being added in a duplicate record merge?
You said, "The new record has alot of related child tables, the foreign keys of which, I need to switch to the original record to merge the two." How do you know that there won't be any conflicts between new "child" rows and old "child" rows?
Aug
14
answered How do I list all columns for a specified table
Aug
12
answered Writing to a DKNF database, but reading from a de-normalized table for performance?
Aug
8
answered is it possible to create a form in the intranet that can be stored in a database?
Jul
31
comment Is there a reason/benefit I am not aware of for this kind setup?
@FabricioAraujo: I'd expect to need a transaction wrapping all the statements that select the next value, insert into the target table, and update the next value. Docs for serializable transactions You can also lock tables in MySQL.