wulczer

I'd say that the URL for Apple iPod is:

  http://google.com/search?q=apple%20ipod

and for Sony Walkman:

  http://google.com/search?q=sony20walkman

Really, I see people going to their GMail by typing "gmail" in Google. And ask 100 strangers on the street how important the tidyness of the text in their browser's address bar is to them.

That's what ON UPDATE CASCADE is for.

If you have a distributed system, generating unique identifiers and keeping referential consistency across the distributed parts is already a difficult problem. But that's a digression. If I ever get so many users I'll have to partition the user table across databases, I'll have much more trouble that just their IDs (and I'll be rich :) )

I think I see the fundamental point where we disagree. I try to keep as much business logic in the database as possible. That's more of a DBA perspective, I guess: don't trust these pesky app developers, always assume they'll try to put broken data in your DB.

It's often not possible to model all business rules in SQL, but the more you manage to cram into the database, the less probable it is you'll end up with inconsistent data.

Often there are dozens of applications (or independent modules) using the database. Keeping the business rules (like "every user has to have a first name" or "no two customers can rent the same car at the same time") in a centralised place helps a lot with keeping everything in order. It's often not possible to limit the interaction with the database to a one-and-only system (imagine different teams writing different parts of the system, app changes, outsourced integrations, etc)

So, if a label can't have spaces in it, that's easy - slap on a check constraint that enforces it. Then you know that even if the new hire writes a "change my label" funcionality and forgets to enforce the no spaces rule, she won't screw up your data.

If a user can have at most three labels, things get more interesting. Offhand I'm not sure how I'd model that and it actually is a difficult problem, best solved with triggers IMHO, as you have better control over locking and can guard yourself against concurrency issues.

Anyway, the discussion on "all logic in DB" vs "all logic in the app" is part of the neverending struggle between DBAs and app programmers...

Yeah, these might all be valid reasons to use surrogate keys. I'm not saying there are none, just that always using one often leads to design mistakes.

Incidentally, part III of the keyvil blog posts talks about reasons to use surrogates :)

As always, nothing's black or white, sometimes surrogates are OK, sometimes they're not. But I see schemas abusing surrogates much more often than schemas abusing natural keys...

There are strong opinions on it, but I firmly believe that "always have an autoincremental id" is a very wrong approach.

Read http://it.toolbox.com/blogs/database-soup/primary-keyvil-par... (an often-quoted series of blog posts on why surrogate numeric keys are evil).

In general, if you have an entity with a well-defined natural key (users and their logins, shipments and their tracking codes), why would you use an autogenerated key that's meaningless?

Case in point, my current schema, designed from scratch. Users can have multiple dashboards, each dashboard has a label. A user cannot have multiple dashboards with the same label, but the two users can of course use the same label for their dashboards (like "sales" or "mydashboard"). The dashboard's table primary key is (username, label) - that pair uniquely identifies a dashboard, so it's a perfect PK candidate.

Also, as already noted in one response, many-to-many relationships are typically modelled by an intermediate table that has foreign keys to both sides of the many-to-many, and its foreign key is the sum of these foreign keys.

EDIT: added a concrete example

SQLAlchemy supports multiple-column primary keys, Django ORM does not. What's more to think about?

Oh, I see what you're referring to.

Yes, storing the local time and the timezone is not exactly what's enough, all that time I was thinking about the PostgreSQL timestamp with time zone type, which is preferable to use vs storing the Unix timestamp.

I guess we've been talking past each other, with me being the main culpable :)

Use pytz.

  import pytz
  from datetime import datetime

  tz = pytz.timezone('Asia/Shanghai')
  tz.normalize(pytz.utc.localize(datetime.utcnow()).astimezone(tz))

You store the local time and timezone. You show the value in whatever timezone the user configured the application.

If you store only Unix time, you don't have enough information. If you store both the Unix timestamp, the local time and the timezone, you have too much information. You can derive Unix time from local time and the timezone.

Ergo: store time with time zone.

But you frequently need to store the original timezone, to be able to do date calculations after storing it.

For instance, a user added an appointment and her timezone is America/Los_Angeles. Then she moved to China and changed her timezone in your app to Asia/Shanghai. Then you run analytics to see how many users had had appointments on weekdays.

When storing date information in persistent stores (like databases), it's always advisable to store the time and the timezone.

Storing time as Unix timestamps is often not possible. Often you need to be able to answer questions like "did this moment fall on a weekday?", "what day was it 3 hours after that moment" and so on.

My rule is to either work with integer Unix timestamps or timezone-aware datetime objects. A naive datetime object is, well, naive. If it's the number of seconds since the Epoch, represent it as a number, not an object.

Oh, good point. I clearly need more caffeine in my system.

Looks useful, but why make smoke.confirm work differently from plain confirm? It uglifies the API for no gain, IMHO.

My first reflex was to write a comment mentioning that essay and then I found this. Wish I could upvote you more...

See http://www.madore.org/~david/computers/quine.html and http://www.nyx.net/~gthompso/quine.htm

A quick search reveals this post:

http://jan.kneschke.de/2008/9/9/async-mysql-queries-with-c-a...

with a little more in-depth exploration of the MySQL async API. The problem seems to be that that (undocumented) API does not handle EAGAIN and that there's no way to connect asynchronously (mysql_connnect always blocks).

Compare with PostgreSQL, which has had an async API for a long time:

http://developer.postgresql.org/pgdocs/postgres/libpq-async....

and for which I wrote a Twisted library that does fully asynchronous connection building and query execution: https://github.com/wulczer/txpostgres.

Since the asynchronous API is exposed in psycopg2, the Python connector library, it should be trivial to hook it up to Tornado as well.

Oh, missed that one. It's a great day for dashboards and the more the merrier!

Ah, our quest for a logo was truly epic. One day we'll write a blog post about 99designs...

Anyway, I hope that this exact conversation will make you remember it's duckSboard.com ;)

It's definitely going to be included in all plans.

To expand on that, we do real real-time. For services that provide real-time updates, like Twitter, you get notification in the exact same moment someone retweeted you or followed you. For custom widgets where you provide your data, it's the same. Your system pushes data to the dashboard and your dashboard shows it instantly. For services you need to repeatedly poll, we're doing as many API requests as their limits allow us.

Yeah, we found that after we already made the page public and didn't want to touch it right after launch.

We have a fix that we'll apply in a few minutes. Thanks!

Thanks, we've taken note. Definitely something to think about.

On a tangential note, our main objective is not differentiating from Geckoboard, it's creating the best online dashboard out there.

Yes, both are dashboards for online metrics, so they will necessarily be similar.

We believe that our product puts the accents in different places. Two things we put much emphasis on are user interface and true realtime updates. We spent a lot of effort polishing the UI and making sure it's consistent and informative, we're using websockets to deliver updates as soon as our backend gets them.

Check out the demos for both of them and pick the one that pleases you more.

A very good question.

First, we caught bugs (not even regressions) just by writing tests. More importantly, many time when we left code without tests, we found a bug in it later. We're not 100% sure it's been a good investment, but we're 90% sure.

Second, because it wasn't that hard. With the tools available, writing tests that cover all of your code is not too painful.

Third, in private beta we're sure we'll have to change things as we face scaling problems and get failure reports. We wanted to have these tests before we started monkeying around with code.

Short answer: we're ashamed (if you're not ashamed of what you launched, you launched it late) :)

Long answer: we're inviting close friends first, expanding to people that signed up back when we launched the first teaser and then the rest. As soon as we see how well does it scale, we'll start sprinkling invite codes on HN.

Thanks, we'll try to get you an invite as soon as possible but we're still in the "too embarassed to show it to anyone else than close friends" stage :)

We're two technical people and one biz.

Offtopic: am I the only one struck by "about 2 hours of WTF'ing"? For a truly elusive bug, one that you stuff and hang over your fireplace after hunting it down, 2 weeks seem more appropriate.

There are cases where a full table scan is faster than an index scan, and there are cases where the index scan is faster. There are even cases where the only option is a full table scan!

I still don't see what a setting that gives errors when a particular access method has been chosen would be good for.

You don't care about the plan, you care about how fast the query runs.

You'll always have to do reactive development when trying to get good performance. There is only so much simulating and testing you can do and real data volume and distribution changes all the time.

That would be nearly useless in my opinion.

If you have a table with four rows in it, would you like the database to give you errors when you do a full scan on it?

What you want is an option to throw errors when a query takes too long to execute and most products already have this in the form of a statement timeout parameter.

On a separate note, the balance between "late" optimization and "premature" optimization is thin indeed... These test systems need to run with the same data and on the same hardware as your main database to give meaningful results, so it's generally unavoidable to do some kind of performance tweaking on the already deployed code. And of course as the data distribution and its amount changes, you need to keep on optimizing...