http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Peter, Please read this part, it hit upon my thinking very well. http://www.evanhoff.com/archive/2007/06/05/19.aspx SQL has very poor support for abstraction, encapsulation, reuse, etc. http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment13http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment13Fri, 30 Nov 2007 21:40:07 GMTI almost wept when I saw a column called OrganizationUnit. We'd be lucky to get something as readable as ORG_UNT out of our DBAs. :-) And making column names match Java field names ... wouldn't that open the risk of using an ORM tool instead of writing millions of lines of code? Seriously, good post. I was exposed to the all metadata all the time horror only once and the others not at all, I think, but they are certainly out there. http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment12http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment12Fri, 30 Nov 2007 15:22:07 GMTA DB that has the following tables 1. Schemas 2. Tables 3. Columns 4. Rows 5. Values http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment11http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment11Fri, 30 Nov 2007 11:25:52 GMTPerter, Sometimes you get the easy way by mentoring, but you have to have some way to feel the pain. http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment10http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment10Fri, 30 Nov 2007 11:10:23 GMTJose, thanks. http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment9http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment9Fri, 30 Nov 2007 11:04:21 GMTSteve, You are correct in your approach, certainly. The different is that SQL offers a lot of rich tools for doing reports, while any other method means that I have to write it from scratch. The basic idea is that you can offload that to the end user, and tell them that it is a way they can write their own stuff easily http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment8http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment8Fri, 30 Nov 2007 11:02:50 GMT"I have made the 5-tables mistake before, I" What is that 5-table mistake? How can i know that i have or have not made it? http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment7http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment7Fri, 30 Nov 2007 10:55:06 GMT"""We probably want the usual auditing suspects, such as Id, CreatedAt, ModifiedAt, CreatedBy, ModifiedBy, Owner, OrganizationUnit. Maybe a few more, if this makes sense. Probably not too much, because that can get to be a problem.""" I strongly disagree here. Auditing info is IMO nothing that should be part of the entity itself, unless it's an integral part of the model's business needs. My solution here is different: I have separate changelog and changelogdetails tables. Any changes to entities that need to be monitored are logged to that tables by using the proper ActiveRecord/NHibernate hooks. This allows auditing entities by simply changing their base class instead of adding and maintaining a bunch of attributes on the entities themselves. And yes, even for account entities, auditing attributes are not an integral part of the business model, but the best example for why auditing changes is required in enterprise apps. http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment6http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment6Fri, 30 Nov 2007 08:44:47 GMT>No business logic in the DB! This is important, if you put business logic in the DB, you have to get to the DB in order to execute the business logic. In my experience almost every business logic (BL) changes some data so in majority of cases you must access database any way. If BL is in the middle tier you will probably round-trip to the database many times (many UPDATES, INSERTS and DELETES). If you put your BL in DB round-trip number will be lower, possibly just 1. And your business logic gets enforced even if you access DB directly and not through your middle tier. Don't forget that customer data will live a great longer than your application. Let's not forget that at least some part of BL is always in DB. Constraints. And those come in to flawors: declarative and procedural. Declarative constraints should obviously be put in DB. Procedural constraints, well you could probably put those in DB or in the middle tier. Don't get me wrong. I am not advocating that we should always put BL in DB (support for many DBMS is an example when we should minimize BL in DB). But to say that we should never do that seems equally wrong to me. I is an "it depends" decision I guess. >This kills scalablity, Seems to me that DBMS are more scalable then some custom middle tier or IIS. > hurts the ability to understand the solution, Why ? PL/SQL is harder to understand than C# ? http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment5http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment5Fri, 30 Nov 2007 08:34:59 GMTOren, There was a post of your's quite some time ago where you pointed out an object model for looking at objects that change over time (you had a .At(time) method on them or something like that). Would it be reasonable to use that rather than just the created / modified time / user? http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment4http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment4Fri, 30 Nov 2007 00:06:34 GMTOne thing we have in common is the ability to look at a database schema design, classify it into a familiar pattern, and identify its strengths and weaknesses. I have found that the best way to design a database schema is direct from the use cases. The use cases define how data is loaded and persisted. The more frequent use cases have the highest priority, but the database ultimately must satisfy all of the use cases in a simple manner. I have made the 5-tables mistake before, I have made the blob mistake before. Heck I have even attempted to push all of my business logic in to the stored procedures before (talk about refactoring nightmare!) I like to think that my skill has been earned simply from doing things the wrong way so many times before! My point is, your post is a "don't make stupid mistakes" post, but it comes from hard-earned knowledge... I sometimes wonder if everyone must make these stupid mistakes to appreciate the "best practices" http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment3http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment3Thu, 29 Nov 2007 22:13:41 GMTOren, I think your posts are very informative, its nice to see smart people writing about what should and should not be done when programming and also trying to find better ways to do things. http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment2http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment2Thu, 29 Nov 2007 19:00:39 GMTI think there is a pattern for reports that is broadly applicable, but hardly ever implemented. The pattern is to split the 5 aspects of reports, which are Options (parameters), Provider (runs the report), Data Adapter (fetches the data), Data (represents the data) and Presentation. The point I want to make is this: When you talk of SQL Views for reports, you are suggesting that the Data Adapter logic be encapsulated in the database - the very thing you just recommended against doing for other types of logic. Your report-security sidetrack became necessary because your data logic was in the database. If you move your data logic out of the database, then security is much easier to apply. Only in recent years has this become possible, because many reporting tools now allow reporting off of non-database representations of data (xml, datasets, etc). http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment1http://ayende.com/2978/a-vision-of-enterprise-platform-a-database-that-you-dont-hide-in-the-attic#comment1Thu, 29 Nov 2007 18:37:43 GMT