Hacker News Reader

Having done this for a while, I can solidly say that the relationships and structure of data has been consistently better when designed as a set of schema tables than as a set of classes because they are especially constrained. The constraints forced people to actually think about the relationships between facets of their data, and the resulting classes were much cleaner and more maintainable. Should we ever decide to move aspects of the classes into a database, the transition requires little code refactoring; this comes up fairly often for us, since we are trying to give users more customization powers.

People should be doing that when they design classes, but the flexibility tends to work against them. It could just be a mindset that makes it work well, and this might be a more appropriate approach for larger projects than for smaller types.

In my experience, relational modelling works fine for domains containing simple logic.

As soon as you start having constraints such as "from date must be before to date" and "the quote must have at least one line, and these lines should sum to > 0 and < 1000000" then relational modelling fails. Hard.

I like to use a Quote with QuoteLines as an interview question. Relational modellers make two tables, both having unique identifiers. Domain modellers create two classes, sometimes exposing only one to the outside world. Only one of those classes (Quote) has an identity.

Domain/class models tend, from what I've seen in the wild, to fail faster under load and under change. Ironically, experienced relational modellers tend to build cleaner class models. Although that might be more a function of experience than anything else..

As soon as you start having constraints such as "from date must be before to date" and "the quote must have at least one line, and these lines should sum to > 0 and < 1000000" then relational modelling fails. Hard.

Why?

Ignorance is often the reason why they find such things "difficult". Many NoSQLers just aren't aware of the existence of the check constraints and triggers offered by basically all relational databases.

"from date must be before to date"

In Postgres:

    CREATE TABLE foo (
        from_date datetime,
        to_date datetime,
        CHECK (from_date < to_date)
    );

(This fails in MySQL. MySQL parses check constraints without error, but ignores them.)

I don't understand the quote problem, so I won't attempt to model it.

> As soon as you start having constraints such as "from date must be before to date" and "the quote must have at least one line, and these lines should sum to > 0 and < 1000000" then relational modelling fails. Hard.

Really? Both those problems are insanely trivial to solve with one or two SQL statements. Your second and third criterium are perfect candidates for relational modelling; the first is so trivial it beggars belief.

Enforcing that a quote has at least one quote_line is pretty tough, at least in Postgres, and involves triggers and locks.

How do you enforce that two transactions don't delete the last quote_line at the same time?

And how would you enforce that the sum of the quote_lines is correct with multiple writers?

Not saying it can't be done easily, but I'd love to hear how.

I wonder if SSI (Serializable Snapshot Isolation) would be of use here. http://wiki.postgresql.org/wiki/SSI

"Enforcing that a quote has at least one quote_line is pretty tough, at least in Postgres, and involves triggers and locks."

The reason it seems simple when using a few classes is because there is an implicit assumption that concurrency is a non-issue and there's only one application involved. But those aren't good assumptions, so the approach using classes will start to look more complex (and involve locks, etc.).

In Postgres, the trick is to use SERIALIZABLE transactions everywhere (should be the default, eventually), which avoids the need for explicit locking. Then, add a trigger that is fired before changes to either table, and it would simply check that the condition holds for the quote that was modified.

Here's some code, since you asked:

  create table quote(
    quote_id int8 primary key,
    customer_id int8,
    valid daterange
  );
  
  create table quote_line(
    quote_id int8 references quote(quote_id),
    amount numeric(10,2)
  );
  
  create or replace function check_quote() returns trigger language plpgsql as $$
  declare
    line_count int;
    line_sum numeric;
  begin
    select into line_count, line_sum
      count(*), sum(amount) from quote_line where quote_id=NEW.quote_id;
    if (line_count < 1 OR line_sum < 0 OR line_sum > 1000000)
    then raise exception 'invalid quote';
    else return NEW;
    end if;
  end;
  $$;
  
  create constraint trigger quote_check_trig after insert or update or delete on quote deferrable initially deferred for each row execute procedure check_quote();
  create constraint trigger quote_line_check_trig after insert or update or delete on quote_line deferrable initially deferred for each row execute procedure check_quote();

Note that I did not need to add the CHECK constraint, because it's much better to use the appropriate data type -- DATERANGE -- instead of hacking it together from parts.

I figured that serializable transactions would be necessary. That's going to be the default?

If you forget to use serializable transactions every time you update these tables, is it possible to store incorrect data?

I think that SERIALIZABLE should be the default eventually, but others may disagree and I won't make a prediction.

However, it isn't necessary to solve the problem. It would be relatively easy to use a row lock in this case to solve the problem, as well, but I like to avoid those unless there's a reason.

If you want to have a mix of SERIALIZABLE and other transactions, or you are worried about making a mistake (or some malicious user), then you need to use the row lock. Eventually there should be a way to force users into serializable transactions.

EDIT: actually, in the trigger, you could explicitly check if the transaction isolation mode is serializable. That would be the best approach:

  if current_setting('transaction_isolation') <> 'serializable' then
    raise exception 'serializable mode required';
  end if;

apart from all the other comments here, you seem to be confusing natural and surrogate keys. the "identity" of the quote (the quote number) is a natural key. the "identity" of the quotelines table is a surrogate key - the equivalent in the class model would be the address of an instance (the thing to which the pointer in the quote points).

there's nothing significant in the class model only having the one identity; it's just a natural consequence of what's implicit and explicit in the two technologies.

> As soon as you start having constraints such as "from date must be before to date" and "the quote must have at least one line, and these lines should sum to > 0 and < 1000000" then relational modelling fails. Hard.

Those types of requirements rarely have a big impact on the design. Sure, you might have to write some procedural code in a trigger, but that's just a handful of lines of code. (I don't know why you think the "from_date < to_date" is a difficult requirement though -- as someone else pointed out, that's just a CHECK constraint).

Relational modelling is often a very clean, concise, and readable way to represent many kinds of businesses. You can pile a few extra requirements on top, and a good DBMS will make it easy to do so.

So I don't see a "hard failure" of anything here.

If you think that you're not constrained by relational requirements, when your data is backed by a relational database, then you're living in a fantasy. The constraints exist, the only question is whether you recognize that fact.

When you're answering an interview question on a whiteboard, "right-click refactor" usually fails hard.

The whole point of the exercise is to test someone's design skills, and to find out what interactively designing with them would be like. We didn't care that they came up with a good design - we wanted to see the thought process and the interaction. Because in that organization, on that team, that was actually how we did design - laying out the database schema on the white board. Therefore we were testing design sensibility and a concrete skill that people needed to have.

Not all of us use a language that has classes or where it's considered good taste to use classes to structure everything.

Using classes early on is just a technique to structure the data of your application. Even in a language without classes, you still have a way of representing your data that you could use. In C you would use structs, in a functional language you'd use a Type or whatever.

You're mostly looking at a way to say "this entity belongs to this entity" and "this entity has many of this entity". That kind of thing. Classes or not, you still have a programming-level way of representing that.

Even for languages that don't idiomatically have you use classes for everything, this is still where you would use them. If your language has classes, you should probably be using them for your entities and domain models. The place where you would use discretion by picking between classes or loose functions (like in python, php) is not the area you'd be sketching out in place of a database schema.

> If your language has classes, you should probably be using them for your entities and domain models.

Two years ago, I would have agreed with you. Now after some heavy, realistic usage of Clojure, I don't think I'll ever go back to modeling my domains with classes.

Maps are just so much more flexible! Granted, you frequently have a "type" like key. In the ClojureScript compiler, for example, AST nodes look like {:op :if :test ... :then ...} You can say that the :op :if is a "type", but in reality, the type of that object is a Map.

I'm working on a system now where there wasn't an obvious discriminated union or hierarchy of types. I fought the urge to introduce a type-like key in my map; the result has been quite pleasant.

The Clojure type of the object is a map, but for the AST-manipulating part of the compiler, isn't it in fact more accurate to say that the type of the object (the logical type, you might say, rather than the host type) is, in fact, `:if`?

After some reasonable, realistic usage of Clojure, I'm quite glad of protocols and multimethods, which I have found make several complex things much easier to work through.

Paraphrasing: "Isn't the logical type, in fact, `:if`?"

Yes.

Clojure's types are, generally, of the solution domains. The primary solution domain being: computation. That's the domain that all of Clojure built in types belong to.

The nature of Clojure and its community discourages the use of platform types for modeling the problem domain. Sometimes using platform types is desirable for optimizations like protocol dispatch and well-known structured fields. However, even when you're doing that, you're still operating in the solution domain. You're making an explicit decision about representation and evaluation: data structures and algorithms.

However, any substantial application is going to need some custom code for inspecting and debugging values. You'll wind up designing some schema and writing some custom validation. There can be tools to help you with this: consider XML's (very ugly) XSD schema system. Or consider W3C validators for HTML and CSS. For an example in the OOP world, look at AstValidator.java in the Google Closure code base. You simply can't escape it. A rich, strong type system can give you a leg up and get you 70% of the way there, but it will actively fight you when you want to go the last mile.

When and if you need it, you can basically make your own type system, tailored to your application.

> When and if you need it, you can basically make your own type system, tailored to your application.

When you do decide you need to think about and enforce types though, it helps a lot to have a clean, well-thought-out formally-specified framework to do this in. I hear there's some work on an optional type system for Clojure which might help with this.

Brings to mind the flip-side of that old chestnut about any sufficiently complicated C program containing an ad-hoc, informally-specified, bug-ridden, slow implementation of half of Common Lisp.

Any sufficiently complicated Lisp program contains an ad hoc, informally-specified, bug-ridden, slow implementation of a type system...

"When and if you need it, you can basically make your own type system, tailored to your application."

Only given an exiguous understanding of what a type system is. Maybe if Typed Clojure gets to the state of Typed Racket, sure, you could do that. But at the moment lots of things that would be statically discoverable in a language like Haskell---where, too, the types are of the solution domains---will pop up, to your woe, at runtime, in Clojure. This can be mitigated with some minor macrology and some major discipline, but the Typed Clojure route is major, major macrology (and not just macrology, obviously).

I'm a Clojure user like snprbob86.

A C struct is just data, a map is just data. You're using the term classes when you mean data or data structures.

Classes are the the creation of complexity by unnecessarily fusing code with data. If you don't mean classes specifically, then use the universal terms "data structure" or "schema".

> If your language has classes, you should probably be using them for your entities and domain models.

Not necessarily at all. It takes a profound lack of imagination to believe that's the only way to handle business logic ++ persistence.

Furthermore, freeing yourself from the constraints of the persistence side (presumed to be a SQL DB in this conversation) in the initial sketch/mock stage is foolishness beyond measurement.

> Classes are the the creation of complexity by unnecessarily fusing code with data.

No, classes discourage unnecessary complexity by fusing code with data -- leading to a better separation of concerns.

In the end, the question is about how to split up a software into components. Should "data" and "business logic" be separate modules or shouldn't it rather be (for example) "accounting" and "hr"?

In other words, classes are a namespacing mechanism.

> Not necessarily at all. It takes a profound lack of imagination to believe that's the only way to handle business logic ++ persistence.

It is a stretch to say that there is an idiomatic way to avoid classes as domain objects in most languages. Imagine in $classBasedLanguage you used static functions and key/value arrays..do you think that's a good idea? That's all I was saying.

> Furthermore, freeing yourself from the constraints of the persistence side..

If you're making an ERD, a lot of what you're doing is deciding entity relationships and just naming your data. None of that is impossible with classes.

"There's no difference between this and designing the classes first."

Databases are designed to be shared structures accessible from many applications, so it's not a 100% direct comparison. When focusing on a single process of a single application, of course that leads to some simplifications (no IPC, all datatypes match up perfectly, no concurrency problems).

"you're not constrained by relational requirements"

I don't see relational as more constrained or less constrained than OO. It's different. For instance, in OOP I often feel like it's forcing a hierarchical structure upon the design. Inheritance feels very constraining to me in comparison to the free joining of one table to another based on the values inside (not necessarily based on explicit connections in the design).

It's always about the data, never about the code.