The majority of work I do, as a programmer who wears a shirt and tie, involves databases. And I try to keep things nice and simple when working with databases. What I mean by simple is, extra tables in a database is fine, even triple the “minimum number” (whatever that is), if they all mostly follow some pattern. Constructing a database schema is then a simple application of the rules devised when designing the pattern, and means there’s not much to think about or have go wrong. With a judicious choice of pattern, ORM tools like Hibernate or other things like .NET Entity Framework can start working for you rather than against you. It’s even possible to write code to check over the schema and ensure that the pattern is being followed, and then write code to write code to do all the data access (yes, write code to write code…)
My boss, on the other hand, has a slightly different idea of simplicity. Namely, in his view, simplicity = minimise number of tables, and rely on stored procedures for everything. The arguments I want to win with him are not really what I want to blog about today.
Today I’d like to describe how I implemented strongly-typed lists of objects (think List<SomeType> in C# / list<AClass> in C++) adhering to my boss’s vision of minimising table count, using only three tables and a pile of T-SQL and custom constraints, instead of the “more complex” system of lots of join tables and foreign key constraints. I do not particularly recommend this mode of implementation. For a start, it has made use of .NET Entity Framework, shall we say, “interesting.”