From: "Martijn Tonies"
> Ehm... it might be me - but what sense does it make to have a NULL
> in a PK?
> If you "need" this, then your primary key probably isn't a primary key.
> Care to explain why and how you're designing your database?
The table contains an variable number of integer parameters for accounts:
id INT(11) - accountID
name VARCHAR(32) - parameter name
value INT(11) - parameter value
Other tables contain string, datetime, etc. parameters.
Since most searches are made for a value (or range) of one or more
parameters, a usable primary key is:
name-value-id (on might argue that a part of the name would be sufficient,
but that is not the issue here).
Indeed almost all queries are very fast.
A NULL value indicates that it is a mandatory field that was not filled with
(a valid) value or that the value was reset, or...
Deleting the record is also not very nice, since it will prevent the use of
I have to think back why we dropped the left joins and started using NULL
values, but the first thing that came to mind was an increase in speed.
An extra challenge: the queries are generated because of the large number of
different queries that might be used. We can switch between left joins and
inner joins quickly though.