Any advice regarding my table structure and writing stored procedures to be used by my ADO.Net dataset?

Question

I'm writing a web application in ASP.Net (C#) with some content management functionality.

I have set up a database with a handful of tables, the most important of which I've called MediaObjects which has columns common to all my content files such as RelativeUri.  MediaObjects has primary key MediaObjectId which is shared by the Images, Audio, Video tables which extend it.

Images has columns like IsScaledCopy, IsThumbnail, PixelsHigh, PixelsWide.

MediaObjects
    MediaObjectId  PK
    AddedOn
    RelativeUri
    FileSizeBytes
    MediaType

Images
    MediaObjectId  PK FK
    IsScaledCopy
    IsThumbnail
    PixelsHigh
    PixelsWide

In my application I have an abstract MediaObject class and then subclasses such as ImageObject, AudioObject etc with fields that map to my database tables.

I've created a strongly typed dataset in Visual Web Developer and I'm in the process of setting up dataset methods using stored procedures.

I'm trying to decide whether to attempt to write SQL stored procedures that execute each other, i.e. having two stored procedures

sp_INSERT_MediaObject
sp_INSERT_ImageObject

and passing all the parameters needed for an ImageObject to sp_INSERT_ImageObject and then executing sp_INSERT_MediaObject from inside sp_INSERT_ImageObject,

or if it's better just to have a simple set of CRUD stored procedures for each table and let my business logic code in the application call multiple methods

void InsertImage(ImageObject ImObj){

    MediaObjectsAdapter.InsertObject(ImObj.params...);
    ImagesAdapter.InsertObject(ImObj.params...);

}

I suppose it might be something that could be done either way, I'd be grateful for some advice to help me decide which is going to work best.

Answer 1

First and foremost, don't prefix stored procedures with sp_, this causes a metadata lookup to the master database first since sp_ is used for all system stored procedures.  Prefixing object names isn't necessary in SQL Server and doesn't make sense today with object types separated in folders in SSMS, and type names easily available in sys.objects.


For the design side question, I think either would work for what you want, and I'm not sure that there is a actual best practice for this necessarily.  I am going to move your post from the Database Engine forum over to the Transact-SQL forum where you will get better answers to how you might design the tables and data access for this.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 2

BTW, for your point about naming you may check Don't name your SP with sp_ and Don't prefix tables with tbl_

---

Premature optimization is the root of all evil in programming.

Donald Knuth, repeating C. A. R. Hoare

My blog

Answer 3

As already mentioned by Jonathan and Naomi above, Best Pactices for naming T-SQL sprocs, current (2009) naming "standards":

InsertMediaObject
InsertImageObject

or

uspInsertMediaObject
uspInsertImageObject

or

sprocInsertMediaObject
sprocInsertImageObject

Stored procedures can be nested, however, you get easier transaction and error control if you go with single level sproc. For example:

uspInsertImageMediaObject

Input parameter can control if you need image insert, media insert or both.  When inserting both, envelope the INSERTs inside a transaction. Server-side transactions work very well in T-SQL, so you don't have to worry about it on the client-side (CRUD sprocs solution).

Related link: Managing Transactions in SQL Server Stored Procedures

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com