ora600

You can require this, but its tricky to enforce. Especially on DBAs.

In any database comes a time where a developer or administrator will have to make manual changes to the data through an ad-hoc script. It can be straight sql or perl/php/ruby thing.

The easier you'll make it on him not to cause horrible corruptions in the data by forgetting to update that additional table that depends on whatever he is updating - the better life will be.

Triggers, foreign keys and constraints are all excellent ways to do just that.

Can you link to documentation of ALTER SCHEMA for Oracle that does transactional DDL?

I don't think it exists.

But the reverse is true. If you bought music from iTunes you can't play it elsewhere.

Since iTunes are a convenient way to purchase music, some of us ended up with large quantities of music tied to Apple's devices.

Of course it isn't! I was just riffing off the idea in the parent comment.

You may have sampling bias - you tend to hear more the ones that completely dominate conversations. This doesn't immediately mean they are really the majority.

Personally, I wish my CS program taught me to speak more at a conversation setting (as opposed to "raise your hand to speak" classroom). I spent the first 3 years of my career unable to get a word in at meetings.

I learned today that I may be losing my H1B soon. I can stay in the US as I'm married to another H1B holder, but I will not be able to work.

Since I dislike being idle, my first thought is to pack up and move back to my home country to look for a job. But this article represents an interesting path that I may follow myself.

Thanks for inspiring me to view lack of work permit as an opportunity and not a major life crisis.

I liked the idea of "unprecedented level of access to the algorithms and data that Blekko uses to determine relevancy".

This is a great differentiator from Google. Google can copy features like slashtags easier, but I can't imagine a large company known for its secrecy suddenly exposing their algorithms.

Except that sometimes differences only exist on a slightly more expensive scale.

If you can't tell the difference between 6$ Cab and 6$ Merlot, it is likely because at this price range there is very little difference. Better wines are better at expressing the differences between the grapes. Cheap wines are all fruity and alcoholic.

I'm totally not following here...

How can multiple insert statements be considered duplication if they are inserting different things to different tables? My apps have multiple insert statements. They also have multiple calls to "print". Do you count that as a duplication as well?

We can continue the discussion by email if you prefer. It is interesting to me, but we may be getting off topic.

I have a problem with designing a data model with the "ease of displaying it in a pretty way" as the main consideration.

Nested tables may well be the bee's knees, but probably not for this reason alone...

It is more work than ORM, but if efficient access to data is an important part of your application, it seems to be extra work that brings benefits. At least that has been my experience.

I'm not clear on why this layer has to be repetitive and full of duplication.

I completely agree with every word you said.

But why do you want columns that contain tables recursively?

Aggregate and non-aggregate mixture is supported in the standard and Oracle with the analytical expressions (if I got your meaning correctly).

Even in its own domain, SQL sacrificed parsing for "user friendly" English-like appearance.

SQL error messages are very unhelpful in every DB. You can't easily debug misbehaving queries and there are no tools to help. There is even a lack of indentation standard.

Fixing very long queries that fetch the wrong results is something I've been doing daily for the past 10 years of my life. It is not easy, and I believe the language is to blame for a large part of the difficulty. In some aspects of doing unexpected things with no warning - it is worse than C.

As you state in your article, the ability to easily phrase ad-hoc questions and have them answered with almost no programming is a very useful tool.

Why not use two tools where two are needed?

Many databases run a mix of OLTP (which requires fast cache and no ad-hoc queries) and ad-hoc business intelligence queries.

If you little all your code with CRUD, your code is horrible indeed.

If I hard code filenames all over my code, I also have horrible code. This doesn't mean that files and file names are a problem or that they caused duplication.

The normal (I hope) practice is to this in terms of transactions or activities and create functions that wrap those activities. SQL should only live within those functions that provide a logical interface to the DB. Its called TAPI or XAPI (transaction API). Think of it as your own domain specific ORM.

"Twitter's basically just using Cassandra in place of memcached"

Not sure about that. They also said they are using it for analytics. Actually, since Cassandra is persistent to disks, using it as a memcached doesn't sound right.

So my customer has a critical production database that was hit by excessive locking issue on a table that slowed everything to a halt. It looks like an abuse of existing feature, so I worked on collecting enough information to help the developers find the broken feature and abusing users.

This also seems to be impacting parts of the app that shouldn't be touching that table, and I'm trying to figure out why that is happening.

Life is exciting at operations :)

Thanks for pointing out specific frameworks. I got the feeling that I should be doing more programming than just unix scripts.

Thats for the detailed reply. My email is cshapi@gmail.com, feel free to pick my brain :) I joined Hackers and Founders meetup - just meeting people who are doing interesting work sounds like great fun and will probably make me feel more "silicon valley" than working at a bank does.

That sounds reasonable. I think my CV is a problem - 15 years of Oracle probably sounds like "very expensive specialist" to a startup. I should probably rewrite it to say "Will do any type of work very cheaply for a startup", only without sounding desperate ;)

The article is too fluffy for my tastes, but it states correctly that multi-tenancy is a difficult problem where you have too choose between several non-perfect solutions.

From the DB perspective, the major design decision seems to be: Does each customer get his own schema, or do customers share a schema?

Problems with giving each customer his own schema:

* No DB vendor will ever admit to a limit on the number of schemas you can put on one database. However, more schemas mean more objects, and every RDBMS has a table with a list of all objects in the database. A list that is sometimes scanned by routine maintenance/monitoring procedures. As the database grows you will need to avoid "show databases" or "select count() from user_tables" like the plague.

You will become an expert writing code that goes over hundreds of identical schemas to aggregate something. This is not fun, and normally does horrible things to the location in shared memory where the database keeps cursors (compiled queries). Identical queries on different schemas always compile and cache separately.

Problems with shared schemas:

* Lots of code handling permissions and identity and who can see what * If you decide to do something to just a subset of the customers (migrate to a different server or upgrade version), you will need code to handle this. There is nothing ready made to support you. * Schemas grow very large, and performance of smaller customers can be impacted by their larger peers. * You don't get diskspace back when customers leave. * Sharding is not as trivial as it would be with separate servers.

Generally speaking, I prefer shared schemas. However, the most realistic scenario is that you will have both - large customers will get their own schemas and small ones will share. I've seen this happen 5 different times - you start with one architecture (no matter which) and add the other and write code to migrate customers.

The best solution I've came up with is to design for multiple shared schemas and build the product to manage this. This will allow you to stay with one schema as long as you are small, add more as you grow, and if you are lucky enough to have large customers - creating another shared schema that happens to host just one customer is almost no overhead. The important part here is to build a really good sharding and migration tool.

I wish I had one like that to sell you :)