Member Avatar for towerrounder

Hi all,

I'd appreciate some advice concerning the following issue.

I have a situation with two very different ways to implement. Both are easy, but Im not sure of the pros and cons of each.

Basically I am allowing users to message each other on a website, and so I have the following table in my database:

Message Table:
MessageUID (PK), SenderUID, ReceiverUID, MesasgeText, DateMessageSent

I also have another table thats stores images on the message. I allow users to paste pictures into the message and to position them. In this table I basically just store the picture name, the messageUID and the X and Y location of the picture.

Message Picture Table:
MessagePictureUID, MessageUID (FK), PictureName, XPos, YPos.

This works fine. But my question is this. Would you scrap the second table and store that information as xml in the first table?

IE
New Message Table:
MessageUID (PK), SenderUID, ReceiverUID, MesasgeText, DateMessageSent, *PictureInfo*

At the moment, I save the message, get the MessageUID, and the with the MessageUID I iterate through I list of my picture objects and save each of them to the Message Picture table. As you can see it might be quite a few db hits instead of just the one.

Im currently running a Sql Express database (and might be for a while), so Id like to keep my db as fast and efficient as possible, and so I'm tempted to go for the later solution as apposed to the first.

If I go with the later solution, I will no doubt loose the maintainability and readability of the message pictures, but will the performance gain be worth it?

I hope that make sense.

Thank you.

  • 2 Contributors
  • 2 Replies
  • 205 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by nick.crane
Member Avatar for towerrounder

I forgot to mention that with the later solution, I would serialize the list of picture objects into an xml string

Member Avatar for nick.crane

You should be able to use the FK constraint to retrieve the images for a specific message without needing to iterating through the whole image table.
Use DataRow.GetChildRows to do this.
Adding suitable indexes may also improve your performance.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.