Building a Repository using ServiceStack.ORMLite

Question

I'm using servicestack and i'm planning to use ormlite for the data access layer. I've these tables (SQL Server 2005)

Table ITEM
ID PK
...

Table SUBITEM1
ID PK FK -> ITEM(ID)
...

Table SUBITEM2
ID PK FK -> ITEM(ID)
...

Table POST
ID PK
...

Table COMMENT
ID PK
...

Table DOWNLOAD
ID PK
...

Table POST_COMMENT
ID PK
POST_ID FK -> POST(ID)
COMMENT_ID FK -> COMMENT(ID)

Table DOWNLOAD_COMMENT
ID PK
DOWNLOAD_ID FK -> DOWNLOAD(ID)
COMMENT_ID FK -> COMMENT(ID)

I created a class for each table and mapped them using annotations (autoincrement, references, etc).

I decided to creare a repository for each "entity" (item, post, comment, download). Each repository contains the basic CRUD logic,

eg. 1 CommentRepository has a Save(Comment comment, Object relationship) that performs db.Insert(comment, relationship) where relationship is PostComment or DownloadComment.

eg. 2 PostRepository has a Save(Post p) that performs the insert into POST.

I'm not sure about this solution because the repository interface is different and I can't do polymorphic queries.

Can you provide any advice to improve my DAL?

Thank you for your attention.

Answer 1

I'm not a fan of forced artificial abstraction so I don't like starting with a repository for every entity as it will just lead to un-necessary code-bloat. I like to start with only 1 repository for all entities encapsulating all data access and refactor naturally out when it gets too big.

I don't know enough of your domain to know what the best RDBMS layout is but I also like to avoid creating un-necessary tables where possible and will look to blob non-aggregate root data, e.g. if SubItem can only applies and is not meaningful outside the context of its parent Item, then I would just blob it saving a couple of tables, e.g:

class Item {
   int Id; //PK, AutoIncr
   List<SubItem> SubItem;
}

Rather than the separate Many : Many tables, I would just maintain it on the single Comment table, e.g:

class Comment {
    int Id; //PK, AutoIncr
    string CommentType; //i.e. Post or Download
    int RefId;
    string Comment;
}

So my repository would mimic the data access patterns required for fulfilling the Web Request, something like:

class BlogRepository {
    void AddCommentToPost(int postId, Comment comment);
    void AddCommentToDownload(int downloadId, Comment comment);
}