Generally speaking a schema in oracle is the same as a user. Oracle Database automatically creates a schema when you create a user. A file with the DDL file extension is an SQL Data Definition Language file. Creating new user (using SQL Plus) Basic SQL Plus commands: – connect: connects to a database – disconnect: … Read more
If you’re going to be doing any syncing between databases with occasionally connected apps, then you should be using GUIDs for your primary keys. It is kind of a pain for debugging, so apart from that case I tend to stick to ints that autoincrement. Autoincrement ints should be your default, and not using them … Read more
You make sure one of the columns can only contain one value, and then make that the primary key (or apply a uniqueness constraint). CREATE TABLE T1( Lock char(1) not null, /* Other columns */, constraint PK_T1 PRIMARY KEY (Lock), constraint CK_T1_Locked CHECK (Lock=’X’) ) I have a number of these tables in various databases, … Read more
An age-old problem that has never been elegantly resolved. All the banking packages I’ve worked with store the balance with the account entity. Calculating it on the fly from movement history is unthinkable. The right way is: The movement table has an ‘opening balance’ transaction for each and every account. You’ll need this in a … Read more
I have a few fairly simple rules of thumb I follow when designing databases, which I think can be used to help make decisions like this…. Favor normalization. Denormalization is a form of optimization, with all the requisite tradeoffs, and as such it should be approached with a YAGNI attitude. Make sure that client code … Read more
Store your zipcodes as CHAR(5) instead of a numeric type, or have your application pad it with zeroes when you load it from the DB. A way to do it with PHP using sprintf(): echo sprintf(“%05d”, 205); // prints 00205 echo sprintf(“%05d”, 1492); // prints 01492 Or you could have MySQL pad it for you … Read more
Overall, I see nothing wrong with this. Except… I would store the day of week as an integer using whatever numbering system your native programming language uses (in its libraries). This will decrease the size of the database and remove string comparisons from your code. I would probably put the foreign key to the business … Read more
If the two columns have the same name in both tables (convention #2), you can use the USING syntax in SQL to save some typing and some boilerplate noise: SELECT name, address, amount FROM employees JOIN payroll USING (employee_id) Another argument in favor of convention #2 is that it’s the way the relational model was … Read more
I’m reasonably certain that it predates disk drives, and goes back to a considerably older technology: drum memory: Another possibility (or maybe the choice was based on both) is a still older technology: mercury tank memory: You may have seen the symbol oriented horizontally instead of vertically, but horizontal drums were common as well:
I like this to find missing indexes: SELECT relname AS TableName, to_char(seq_scan, ‘999,999,999,999’) AS TotalSeqScan, to_char(idx_scan, ‘999,999,999,999’) AS TotalIndexScan, to_char(n_live_tup, ‘999,999,999,999’) AS TableRows, pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize FROM pg_stat_all_tables WHERE schemaname=”public” AND 50 * seq_scan > idx_scan — more than 2% AND n_live_tup > 10000 AND pg_relation_size(relname :: regclass) > 5000000 ORDER BY relname … Read more