If you are looking for a one-size-fits-all, I’d suggest DECIMAL(19, 4) is a popular choice (a quick Google bears this out). I think this originates from the old VBA/Access/Jet Currency data type, being the first fixed point decimal type in the language; Decimal only came in ‘version 1.0’ style (i.e. not fully implemented) in VB6/VBA6/Jet … Read more
Just a few reasons for using surrogate keys: Stability: Changing a key because of a business or natural need will negatively affect related tables. Surrogate keys rarely, if ever, need to be changed because there is no meaning tied to the value. Convention: Allows you to have a standardized Primary Key column naming convention rather … Read more
Let’s say you have a FOO table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days. Then, I would create a FOO_HISTORY table. This has all the columns of the FOO table. The primary key is the same as FOO plus a RevisionNumber column. … Read more
Rows per a table won’t be an issue on it’s own. So roughly speaking 1 million rows a day for 90 days is 90 million rows. I see no reason Postgres can’t deal with that, without knowing all the details of what you are doing. Depending on your data distribution you can use a mixture … Read more
A PostgreSQL “schema” is roughly the same as a MySQL “database”. Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.
255 is used because it’s the largest number of characters that can be counted with an 8-bit number. It maximizes the use of the 8-bit count, without frivolously requiring another whole byte to count the characters above 255. When used this way, VarChar only uses the number of bytes + 1 to store your text, … Read more
The very first thing developers should know about databases is this: what are databases for? Not how do they work, nor how do you build one, nor even how do you write code to retrieve or update the data in a database. But what are they for? Unfortunately, the answer to this one is a … Read more
Without an index, maintaining an autoincrement column becomes too expensive, that’s why MySQL requires an autoincrement column to be a leftmost part of an index. You should remove the autoincrement property before dropping the key: ALTER TABLE user_customer_permission MODIFY id INT NOT NULL; ALTER TABLE user_customer_permission DROP PRIMARY KEY; Note that you have a composite … Read more
Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you’re making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn’t necessarily … Read more
HTTP specification does not limit length of headers at all. However web-servers do limit header size they accept, throwing 413 Entity Too Large if it exceeds. Depending on web-server and their settings these limits vary from 4KB to 64KB (total for all headers).