Consider cities and states. Each city exists within a state. Each state has a capital city. CREATE TABLE city ( city VARCHAR(32), state VARCHAR(32) NOT NULL, PRIMARY KEY (city), FOREIGN KEY (state) REFERENCES state (state) ); CREATE TABLE state ( state VARCHAR(32), capital_city VARCHAR(32), PRIMARY KEY (state), FOREIGN KEY (capital_city) REFERENCES city (city) ); First … Read more
When creating a migration you can specify foreign keys on your tables, i.e. public function up() { Schema::table(‘roles’, function(Blueprint $table) { $table->increments(‘id’); $table->integer(‘user_id’)->unsigned(); //rest of fields then… $table->foreign(‘user_id’)->references(‘id’)->on(‘users’); }); } This will create a foreign key on the user_id column on the roles table. The benefits of foreign keys is that when an update or … Read more
Voldemort’s support for adding nodes was just added recently (this month). So I would expect Cassandra’s to be more robust given the longer time to cook and a larger community testing. Both are fast (> 10k ops/s per machine). Because of their storage designs, I would expect Cassandra to be faster at writes, and Voldemort … Read more
RDBMSes aren’t flat. The R part sees to that. What you need is: Table Entity ———— ID Table EntityData —————- EntityID MatrixRow (1, 2, 3…) MatrixColumn (A, B, C, D…) Value Entity:EntityData is a one-to-many relationship; each cell in the matrix has an EntityData row. Now you have a schema that can be analyzed at … Read more
The place where this will fail is if you have to do what’s called “shard walking” – which is finding out all the data across a bunch of different users. That particular kind of “query” will have to be done programmatically, asking each of the SQLite databases in turn – and will very likely be … Read more
One of the use cases for RULES are updateable views (although that changes in 9.1 as that version introduces INSTEAD OF triggers for views) Another good explanation can be found in the manual: For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is … Read more
I’ve used PostgreSQL in an environment where we’re seeing 100K-2M new rows per day, most added to a single table. However, those rows tend to be reduced to samples and then deleted within a few days, so I can’t speak about long-term performance with more than ~100M rows. I’ve found that insert performance is quite … Read more
There are several reasons why JPA-oriented frameworks (Hibernate, for instance) do not fit into idiomatic Scala applications elegantly: there are no nested annotations as states the Scala 2.8 Preview — that means you cannot use annotations as mapping metadata for complex applications (even the simplest ones often use @JoinTable -> @JoinColumn); inconsistencies between Scala and … Read more
Databases that use MVCC to isolate transactions from each other need to periodically scan the tables to delete outdated copies of rows. In MVCC, when a row is updated or deleted, it cannot be immediately recycled because there might be active transactions that can still see the old version of the row. Instead of checking … Read more
ROWID is the physical location of a row. Consequently it is the fastest way of locating a row, faster even than a primary key lookup. So it can be useful in certain types of transaction where we select some rows, store their ROWIDs and then later on use the ROWIDs in where clauses for DML … Read more