Question about Martin Fowler's db design

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Question about Martin Fowler's db design

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-02-23 : 10:30:07

hi guys,
am having an argument on a group project am working on.

Its modelling a database design after the object orientation of .net classes. ( WHICH IS THE MAIN OBJECTIVE )

Its a new project and The team leader and members are using a book called PATTERNS OF ENTERPRISE APPLICATION ARCHITECTURE. By Martin Fowler.

Now my disagrement is that you have a table of e.g. players/sports men and instead of having one big table of players and a related table of types of sports. They want to create a table for each player. e.g.

table - footballers
table - cricketers
table - bowlers

etc.

I feel this is bad, but am alone in my opinion and not the final decision maker. What do you guys think
Ehi

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-02-23 : 10:38:11

These are the other two recommendations

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-02-23 : 11:41:55

IMO (also) bad idea, unless there are sporting atrtributes particular to each sport than need recording for each distinct sport.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-23 : 12:33:44

quote:
Its modelling a database design after the object orientation of .net classes. ( WHICH IS THE MAIN OBJECTIVE )

The problem you're facing is that good database design is the EXACT opposite of this statement. As long as the object model takes precedence, the database design will suffer. No way around that.

I agree that either option is poor. Unless you can somehow get the developers to use views to present the player types (Footballer, Cricketer, Bowler) as views against the Players table. Additionally they'd have to look at mapping their object methods to SQL stored procedures to make that model work properly.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 17:53:26

I hesitate to even say this on sqlteam but:

If your developers only want to dump objects to a store somewhere and be able to perform simple queries then maybe what they should be considering is some sort of document persistence product.

i like mongoDB. It's really, really good for persistence. You can do some real funky things with mapReduce in it as well.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-03 : 09:49:26

Mmmmmmmmm...I want to bring EAV to the table but I know I shouldn't! Because "designwise" eav will solve your problems but it will bite you in the a$$ later :)

-> http://en.wikipedia.org/wiki/Entity-attribute-value_model

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-03 : 11:30:51

I'm afraid there's no right/wrong answer here. I'd vote for the single table. One advantage to that is if you have to insert a Player, you'll only have to write the Code once rather than for every Sport. And in the future you may wish to add more sports.

koto
Starting Member

6 Posts

Posted - 2011-03-06 : 17:13:21

In the 'Entity Framework 4.0 Recipes: A Problem-Solution Approach' there are some thoughts about this.
solution with many tables is called 'Table per type' while with one table 'Table per hierarchy'.
Here is a meaningful sentence:
"Table per type inheritance provides a lot of database flexibility because we can easily add tables as
new derived types find their way into our model as an application develops. However, each derived type
involves additional joins that can reduce performance. In real-world applications, we have seen
significant performance problems with TPT when many derived types are modeled.
Table per hierarchy, as you will see in Recipe 2-10, stores the entire hierarchy in a single table. This
eliminates the joins of TPT and thereby providing better performance but at the cost of some flexibility."

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-08 : 10:18:50

quote:
Originally posted by koto

In the 'Entity Framework 4.0 Recipes: A Problem-Solution Approach' there are some thoughts about this.
solution with many tables is called 'Table per type' while with one table 'Table per hierarchy'.
Here is a meaningful sentence:
"Table per type inheritance provides a lot of database flexibility because we can easily add tables as
new derived types find their way into our model as an application develops. However, each derived type
involves additional joins that can reduce performance. In real-world applications, we have seen
significant performance problems with TPT when many derived types are modeled.
Table per hierarchy, as you will see in Recipe 2-10, stores the entire hierarchy in a single table. This
eliminates the joins of TPT and thereby providing better performance but at the cost of some flexibility."

Shoot, that's a pretty good summary.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-04-05 : 16:35:32

Sorry guys, this is late. But we finished the project.

It was based on the new concept being pushed by microsoft, as koto rightly said. Called ENTITY FRAMEWORK MODEL.

Its meant to address design and performance issues with enterprise application, also with issues like LINQ.

Am not really a fan of it, but was quite interesting. See this http://msdn.microsoft.com/en-us/library/aa697427(v=vs.80).aspx and http://vimeo.com/4467595

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-04-06 : 14:25:14

Thankyou for the update. What parts did you like with ENTITY FRAMEWORK MODEL? dislikes?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-04-07 : 09:33:38

Its a bit fuzzy to me, and i would not criticize it now, because when i first moved from classic ASP to .net I hated .net but had no choice but to work with it, but years later, I wont touch classic ASP again, because i think its obsolete.

The entity framework in some areas are thought of being a bit controversial, but in my understanding, its

1. For the enterprise
2. To map to any data store ( and not just mssql )
3. To create a layer of abstraction for some tables ( and not all ) in the database, that contain high volume data
4. Reduce the overhead of complex queries and joins. And performance bottlenecks
5. Further abstract specific large db tables into smaller ones. E.g. where u have user/person in a sports table you can further abstract them to the above tables

Also there was a comparison to LINQ, which is for rapid development and maps 1:1, whereas EF has its on entitySQL which is for the enterprise and has 1:1, 1: Many and Many : 1 etc

My dislikes, we didnt use SP which over the years i have become accustomed to, but i understand you can map entitySQL to SP.

Personally i think its great, ( has its niche and not an everyday scenario ) but still confused about a few issues

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-04-07 : 09:35:35

... also i hear, oracle, DB2 and many popular IT companies are making thier own custom version. So its not just a Microsoft thing, but maybe a thing for the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources