An ORM idea...

Question

Alright so after a bit of research I think I'm having some trouble coming up with a suitable ORM pattern.

Here's the situation. I have the following tables and relevant fields:

EVENTS
eventid
creatorid
imageid

INVITATION
invitationid
userid
eventid

IMAGES
imageid

USERS
userid
imageid

Aside from the more obvious relationships creatorid -> USERS.userid

I felt pretty early on that if I wanted to retrieve the relevant data for a specific object from the database the best way to do that would be in a single query rather than grabbing the IDs of subsequent objects and sending another query. In the example above, gathering the relevant information for a single invitation would require 7 queries!

So I started grabbing as much data as I could but as you can see, the field names will undoubtedly begin to collide since I'm grabbing multiple users and images. I could re-name each one of the fields but that seems like it would be extremely difficult to maintain and mean that adding a single field to any of these tables in the future would mean changing several if not dozens of stored procedures.

Today I think I came across a solution and I wondered what people here might think. I decided to return multiple results. IE:

DECLARE @invitationid int

SELECT * FROM invitations WHERE invitationid = @invitationid;
SELECT im.*, u.* FROM images im INNER JOIN users u ON u.imageid = im.imageid INNER JOIN invitations i ON i.userid = u.userid WHERE i.invitationid = @invitationid;
SELECT e.* FROM events e INNER JOIN invitations i ON i.eventid = e.eventid WHERE i.invitationid = @invitationid;

From there I could use a dataset to store all the results and use the class constructors to look to the proper result set for the necessary information.

It's not the prettiest solution but it's the only one I've managed to come up with so far. Does anyone see a better way?

Answer 1

I think you have created a classic ORM transition from database, but you are still using the guiding principles of a database ERD design to create your Entities.  During a database design you usually normalize the database primiarily for effective storage and denormalize if you want faster retrieval/query.

I think you have used your guideline for highly normalized design for effective storage, but I would say, ORM should be primarily about access and retrieval to data instead of storage (especially storage disk space comes cheap today)...

Flatten your design that helps faster easy retrieval and expressiveness of your SQL (Hopefully LinQ to SQL) rather than storage and you would be surprised how simple your entities are...

 

Hope that helps, if not ask again...

 

{ Gaja; }

http://www.gajakannan.com/netarch.aspx

Answer 2

Could you give me an example using the entities above?

Also isn't the complication with duplicate pieces of data that an update to one means you have to remember to update the other? How would you recommend taking care of this to make sure your data doesn't become inconsistent?